28

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.

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
Duke
  • 35,420
  • 13
  • 53
  • 70
  • 2
    Why not read the manual? http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html – Marc B Jan 09 '15 at 17:30
  • 3
    "*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`) –  Jan 09 '15 at 20:53

2 Answers2

48

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.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
  • 1
    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) ? – J.J. Beam Nov 20 '19 at 20:49
  • In my opinion, SELECT ... FOR UPDATE is usually applied under "read committed" isolation level. – CandyCrusher Nov 24 '19 at 03:55
  • 10
    @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: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html – HelloWorld Mar 26 '20 at 16:11
12

In MySQL, SELECT FOR UPDATE is used to prevent lost update and write skew. *In MySQL, only SERIALIZABLE can prevent lost update and write skew without SELECT FOR UPDATE but deadlock error occurs so we should use SELECT FOR UPDATE even for SERIALIZABLE. You can know more about lost update and write skew in my answer of 'Lost update' vs 'Write skew'.

Now in MySQL, I'll show you lost update and write skew and how to prevent them with SELECT FOR UPDATE in REPEATABLE READ.

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.
Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129