How to use transactions in MySQL to avoid "lost updates"?

Question

A lost update occurs when two different transactions are trying to update the same column on the same row within a database at the same time. Typically, one transaction updates a particular column in a particular row, while another that began very shortly afterward did not see this update before updating the same value itself. The result of the first transaction is then "lost", as it is simply overwritten by the second transaction. --https://morpheusdata.com/blog/2015-02-21-lost-update-db

Answer 1

You have two possibilities

1. In case of pessimistic locking, if you intend to update the data you just read, do select for update. Then only one can read the record until the current transaction is finished, the others trying to select for update, must wait.

2. In case of optimistic locking you formulate your update-statement so that in case of a change between select and update, no update occurs. In your case you can do that using:

   UPDATE product set quantity = 10 
          where id = 1 and quantity = <original quantity -- 7>
   

   if not the expected number of records, usually 1, has been updated, because an update of the quantity has been done meanwhile by another process, then you have to repeat the select before the update. How do you find out, how many records have been updated? that depends on the technology you use to do your db-requests, but in my experience every Sql-Dbms returns that information to its client.

Answer 2

This is also known as a "race condition". You already have your answer in your question: You "use a transaction", do you work, then COMMIT the transaction in each thread. Now the nitty gritty:

• Your tables MUST BE if type InnoDB
• By default, MySQL connections work with 1 transaction per command, basically autocommitting the data after each write. You need to START TRANSACTION or disable autocommit: $mysqli->autocommit(FALSE); in PHP for example
• You need to pay attention to result of your operations and ROLLBACK on errors and stop what you are doing
• You really have to remember to COMMIT your changes when fully done, otherwise, the system will think there was an error and ROLLBACK for you.