Why use SELECT FOR UPDATE? (MySQL)

Question

I have a question regarding what purpose we are using SELECT FOR UDPATE? What does it do exactly?

I have 2 tables, from that I need to select rows from table and update the same rows.

For example:

Select Query

SELECT * from  t1 WHERE city_id=2 for update

Update Query

UPDATE t1 SET final_balance = final_balance - 100 WHERE city_id ='2'

My question - Does this really lock the read operation till my update is done, or what does it exactly deal with?

My idea is nobody can read/update from/to these rows until my update finished.

Morello

Why not read the manual? dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html – Ymir

"My idea is nobody can read/update from this rows" - this is wrong. A row lock does not prevent read access (e.g. a normal select without for update) – Poet

Answer 1

SELECT ... FOR UPDATE will lock the record with a write (exclusive) lock until the transaction is completed (committed or rolled back).

To select a record and ensure that it's not modified until you update it, you can start a transaction, select the record using SELECT ... FOR UPDATE, do some quick processing, update the record, then commit (or roll back) the transaction.

If you use SELECT ... FOR UPDATE outside of a transaction (autocommit ON), then the lock will still be immediately released, so be sure to use a transaction to retain the lock.

For performance, do not keep transactions open for very long, so the update should be done immediately.

Hyperaemia

Doesn't the transaction guarantees the lock? In other words does SELECT ... FOR UPDATE makes sense only if transaction has a low isolation level ( like read_uncommit) ? – Manuel

In my opinion, SELECT ... FOR UPDATE is usually applied under "read committed" isolation level. – Unpopular

@J.J.Beam transactions guarantee locks on an UPDATE, but not a SELECT (read). so if you do a SELECT ... without FOR UPDATE at the beginning of your transaction then use the selected row(s) information later in your transaction for an update, it's possible that another transaction updated the row(s) that you queried earlier in your transaction. Hence it is necessary to use a SELECT ... FOR UPDATE within a transaction if you need to use the queried rows later. Read: dev.mysql.com/doc/refman/8.0/en/… – Legitimist

Answer 2

In MySQL, SELECT FOR UPDATE is used to prevent lost update and write skew (see more about these problems my answer to 'Lost update' vs 'Write skew'). Note: in MySQL, only SERIALIZABLE transaction isolation level prevents those problems without SELECT FOR UPDATE but deadlock error can occur even in that case, so we should use SELECT FOR UPDATE anyway.

Below I show lost update and write skew and how to prevent them with SELECT FOR UPDATE with the REPEATABLE READ isolation level.

First, this is the example of lost update below. There is product table with id, name and stock as shown below.

product table:

id	name	stock
1	Apple	10
2	Orange	20

These steps below shows lost update without SELECT FOR UPDATE in MySQL. *The stocks of the products decrease when customers buy the products:

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.

Next, these steps below shows how to prevent lost update with SELECT FOR UPDATE on MySQL. *The stocks of the products decrease when customers buy the products:

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.

Second, this is the example of write skew. There is event table with name and user as shown below.

event table:

name	user
Make Sushi	John
Make Sushi	Tom

These steps below shows write skew without SELECT FOR UPDATE in MySQL. *Only 3 users can join the event "Make Sushi":

Flow	Transaction (JT)	Transaction (LT)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	SELECT count(*) FROM event WHERE name = 'Make Sushi'; 2		T1 reads 2 so only one user can join it.
Step 4		SELECT count(*) FROM event WHERE name = 'Make Sushi'; 2	T2 reads 2 so only one user can join it.
Step 5	INSERT INTO event values ('Make Sushi', 'Lisa');		T1 inserts Lisa to event table.
Step 6	COMMIT;		T1 commits.
Step 7		INSERT INTO event values ('Make Sushi', 'Kai');	T2 inserts Kai to event table.
Step 8		COMMIT;	T2 commits. 4 users join it. *Write skew occurs.

Next, these steps below shows how to prevent write update on MySQL with SELECT FOR UPDATE. *Only 3 users can join the event "Make Sushi":

Flow	Transaction (JT)	Transaction (LT)	Explanation
Step 1	BEGIN;		T1 starts.
Step 2		BEGIN;	T2 starts.
Step 3	SELECT count(*) FROM event WHERE name = 'Make Sushi' FOR UPDATE; 2		With "SELECT FOR UPDATE", T1 reads 2 so only one user can join it.
Step 4		SELECT count(*) FROM event WHERE name = 'Make Sushi' FOR UPDATE;	T2 needs to wait for T1 to commit to read event table with "SELECT FOR UPDATE".
Step 5	INSERT INTO event values ('Make Sushi', 'Lisa');	Waiting...	T1 inserts Lisa to event table.
Step 6	COMMIT;	Waiting...	T1 commits.
Step 7		SELECT count(*) FROM event WHERE name = 'Make Sushi' FOR UPDATE; 3	Now with "SELECT FOR UPDATE", T2 reads 3 so no users can join it.
Step 8		COMMIT;	T2 commits. 3 users join it. *Write skew doesn't occurs.

Monodic