2

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?

2

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.)

| improve this answer | |
1

The MySQL 5.1 Reference Manual includes a chapter on transactions and atomic operations (which is quoted below).

The original author expresses a desire to avoid lock contention which can be achieved by using "functions to update records in a single operation".

In this context, UPDATE … LIMIT is a single atomic function that therefore avoids the use of locks.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.