How to simulate lost update in MySQL?

Question

I am using MySQL 8.0 version. I am trying to simulate the lost update transaction concurrency problem. Here is my initial table.

+----+--------------+----------------+
| Id | Product_Name | Items_In_Stock |
+----+--------------+----------------+
|  1 | iPhone 16    |             10 |
|  2 | Dell Laptop  |             20 |
+----+--------------+----------------+

I am running two transactions in separate sessions/ MySQL server instance. First I run transaction 1 and then transaction 2 at the same time. Session 1:

set session transaction isolation level read committed;
start transaction;
set @itemsInStock = (select items_in_stock from inventory where id = 1);
-- Transaction takes 10 seconds.
select sleep(10);
set @itemsInStock = @itemsInStock-1;
update inventory set items_in_stock = @itemsInStock where id = 1;
select @itemsInStock;
commit;

Session 2:

set session transaction isolation level read committed;
start transaction;
set @itemsInStock = (select items_in_stock from inventory where id = 1);
-- Transaction takes 1 second.
select sleep(1);
set @itemsInStock = @itemsInStock-2;
update inventory set items_in_stock = @itemsInStock where id = 1;
select @itemsInStock;
commit;

I expected transaction 2 to finish first because its timer expires before transaction 1. But in practice, I see that transaction 2 is getting blocked until transaction 1 finishes. And then I see that transaction 2 successfully updated the table 'inventory' but transaction 1 gives an error:

Error Code: 1213. Deadlock found when trying to get lock;

I don't understand how the rows are getting locked in transaction 1 before the update. Can anyone who has worked on databases and transactions help me?

Answer 1

This is a locking read:

set @itemsInStock = (select items_in_stock from inventory where id = 1);

Any read query in MySQL that sets data as a side-effect is a locking read.

This includes setting user variables from the result of a SELECT.

Locking reads set shared locks, so both transactions can do their SELECT, and now both hold shared locks.

Then one transaction tries to UPDATE, which requires an exclusive lock. It can't get an exclusive lock because the other transaction holds a shared lock. So it waits.

Then the other transaction also tries to UPDATE. The first transaction holds a shared lock and is waiting for its exclusive lock. So the second transaction also waits.

This triggers the deadlock detection. Both transactions are waiting for the other, which means they cannot proceed. So one transaction is terminated, and its locks are released.