When to use FOR UPDATE in SQL Queries?

Question

Can anyone please tell the difference between the following sql,

SELECT * FROM TABLE

VS

SELECT * FROM TABLE FOR UPDATE

When and at what situation i need to use For Update

It is taking more time for getting the data if i use update and getting stopped. What is happening on Update to increase the time. Please tell me the entire process happening on this.

http://stackoverflow.com/questions/8849518/mysql-select-for-update-behaviour — vasanth, Jun 05 '13 at 12:52

score 8 · Accepted Answer · answered Jun 05 '13 at 12:49

8

I think the sentence from the MariaDB / MySQL manual is self-explanatory:

If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction.

answered Jun 05 '13 at 12:49

DejanLekic

18,787
4
46
77

Why it takes more time for that For Update? Can you please tell me the exact thing happening? – Vinoth Babu Jun 05 '13 at 12:51
2

@VinothBabu when a transaction happens it puts everything in a temporary state/storage before, during, and after - so it is doing much more I/O and also more processing. Transaction logs can get HUGE (especially on big tables) – Zach Leighton Jun 05 '13 at 12:52
It takes time and resources to obtain that lock, Vinoth. – DejanLekic Jun 05 '13 at 12:53
Thanks frds for your comments and answers.. :) – Vinoth Babu Jun 05 '13 at 12:55

score 0 · Answer 2 · answered Jun 05 '13 at 13:02

SELECT ... FOR UPDATE acquires write locks on objects (rows, pages, depending on engine). This involves some overhead. Additionally, the database (the InnoDB engine in most cases) needs to do additional book keeping for multi-version concurrency control (MVCC).

See the MySQL documentation on SELECT ... FOR UPDATE for in-depth information and examples.

When to use FOR UPDATE in SQL Queries?

2 Answers2