Learn, Share, Build

230

September 20, 2017, at 1:35 PM

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

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 MyISAM
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.

POPULAR ONLINE

MySQL