Why use SELECT FOR UPDATE?
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.
Related videos on Youtube
Comments
-
Rinto George over 1 year
I have 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 this rows until my update finished..
Thanks!
-
Marc B over 9 yearsWhy not read the manual? dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
-
a_horse_with_no_name over 9 years"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
withoutfor update
)
-
-
J.J. Beam over 4 yearsDoesn'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) ?
-
CandyCrusher over 4 yearsIn my opinion, SELECT ... FOR UPDATE is usually applied under "read committed" isolation level.
-
HelloWorld about 4 years@J.J.Beam transactions guarantee locks on an
UPDATE
, but not aSELECT
(read). so if you do aSELECT ...
withoutFOR 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 aSELECT ... FOR UPDATE
within a transaction if you need to use the queried rows later. Read: dev.mysql.com/doc/refman/8.0/en/…