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

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.

Answer 2

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 3

Either

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

-- and suffer some deadlocks and a bit of performance loss
-- not as bad as it looks, but depends on your needs
-- REPEATABLE READ is the default level for InnoDB

OR

SELECT FOR UPDATE

-- well, u wrote the code, u know which Selects need to lock others out
-- this assumes you Isolation level is READ_COMMITTED

more on isolation levels can be found in MySql documentation (short and clear this time) https://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-isolation-levels.html

Answer 4

Use SELECT FOR UPDATE to prevent lost update on MySQL. *On MySQL, only SERIALIZABLE can prevent lost update without SELECT FOR UPDATE but deadlock occurs in SERIALIZABLE so we should use SELECT FOR UPDATE even for SERIALIZABLE.

For example, there is product table with id, name and stock as shown below. *The stocks of the products decrease when customers buy the products.

product table:

id	name	stock
1	Apple	10
2	Orange	20

First, these steps below without using SELECT FOR UPDATE shows lost update on MySQL:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	SELECT stock FROM product WHERE id = 2; 20		T1 reads 20 which is updated later to 13 because a customer buys 7 oranges.
Step 4		SELECT stock FROM product WHERE id = 2; 20	T2 reads 20 which is updated later to 16 because a customer buys 4 oranges.
Step 5	UPDATE product SET stock = '13' WHERE id = 2;		T1 updates 20 to 13.
Step 6	COMMIT;		T1 commits.
Step 7		UPDATE product SET stock = '16' WHERE id = 2;	T2 updates 13 to 16 after T1 commits.
Step 8		COMMIT;	T2 commits. *Lost update occurs.

Second, these steps below shows how to prevent lost update with SELECT FOR UPDATE on MySQL:

Flow	Transaction 1 (T1)	Transaction 2 (T2)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	SELECT stock FROM product WHERE id = 2 FOR UPDATE; 20		With "SELECT FOR UPDATE", T1 reads 20 which is updated later to 13 because a customer buys 7 oranges.
Step 4		SELECT stock FROM product WHERE id = 2 FOR UPDATE;	T2 needs to wait for T1 to commit to read stock with "SELECT FOR UPDATE".
Step 5	UPDATE product SET stock = '13' WHERE id = 2;	Waiting...	T1 updates 20 to 13.
Step 6	COMMIT;	Waiting...	T1 commits.
Step 7		SELECT stock FROM product WHERE id = 2 FOR UPDATE; 13	Now with "SELECT FOR UPDATE", T2 reads 13 which is updated later to 9 because a customer buys 4 oranges.
Step 8		UPDATE product SET stock = '9' WHERE id = 2;	T2 updates 13 to 9 after T1 commits.
Step 9		COMMIT;	T2 commits. *Lost update doesn't occur.