Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why use SELECT FOR UPDATE?

Tags:

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!

like image 875
Rinto George Avatar asked Jan 09 '15 17:01

Rinto George


People also ask

Can we use SELECT in update statement?

The subquery defines an internal query that can be used inside a SELECT, INSERT, UPDATE and DELETE statement. It is a straightforward method to update the existing table data from other tables. The above query uses a SELECT statement in the SET clause of the UPDATE statement.

Why do we use SELECT?

A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used data manipulation language (DML) command. As SQL is a declarative programming language, SELECT queries specify a result set, but do not specify how to calculate it.

What is SELECT for update in MySQL?

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

What is SELECT for update in Oracle?

SELECT FOR UPDATE lets you base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them. You can also use SELECT FOR UPDATE to lock rows that you do not want to update, as in Example 9-6.


1 Answers

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.

like image 187
Marcus Adams Avatar answered Oct 26 '22 23:10

Marcus Adams