Mysql – the difference between UPDATE … LIMIT and SELECT … FOR UPDATE

innodbMySQL

I have read an article, there is one sentence about MySQL:

As one might expect, lock contention is an issue and we use several
strategies to mitigate it including a separate table for each priority
level , use of UPDATE … LIMIT instead of SELECT FOR UPDATE for the
dequeue selection query, and carefully tuned schemas and secondary
indexes to fit this type of workload.

So, what is the difference between UPDATE ... LIMIT and SELECT ... FOR UPDATE when considering lock?

Best Answer

Pattern 1:

BEGIN;
SELECT stuff ... FOR UPDATE;
    fetches `stuff` and prevents (via a lock) other connections from messing with row(s).
work with `stuff`.
UPDATE the rows SELECTed (or a subset of them)
COMMIT;

Pattern 2:

(autocommit=1)
UPDATE Queue... WHERE ... LIMIT...  -- Grab row for self
SELECT ... WHERE ...           -- Make sure I got it, and fetch id
if got it     -- Perhaps someone else got it
then
    BEGIN;
    work on application things
    COMMIT;
endif
DELETE FROM Queue WHERE ...   -- remove from queue

Pattern 1 locks the row(s) longer, but allows more flexibility in what is done.

Pattern 2 has two quick locks. But, because of other threads potentially messing with the same row(s), you have to check that you really got the row.

Since you mentioned queuing, think of it this way... You have two structures: (1) the queue, and (2) your application data. In general it is better to handle them separately. The way to do that is to avoid having both in the same transaction.

Having both in the same transaction causes locks to last longer for both. And it forces rollback of both, even if that is inappropriate.

Pattern 1 is better when the SELECT and the UPDATE are both working on application stuff.

Something like Pattern 2 is useful with you need to hang onto something "too long". (That is, more than a few seconds. Or from one html page to another, where you have lost the connection.)

Related Question