Mysql – create a semaphore with locked transactions in MySQL

MySQL

I have a service that requires maintaining a pool of external resources. Therefore I have a table to track the pool. Each row can be used by one user once. My thought was that I can start a transaction, use a lock, try to update row, and see if it errors from having a lock. However it seems my attempt during a lock just makes MySQL wait for the lock to end before making the update happen.

# process 1
START TRANSACTION;
SELECT * FROM pool WHERE id=1 FOR UPDATE;
UPDATE pool SET claimed=1 WHERE id=1;
# (run process 2 below before this commits)
COMMIT;

# process 2
START TRANSACTION;
SELECT * FROM pool WHERE id=1 FOR UPDATE;
# this hangs until above commit
UPDATE pool SET claimed=1 WHERE id=1;
COMMIT;

I know I can lower the lock timeout but that would possibly impact other queries negatively. Is there a way to accomplish what I'm going for via MySQL?

I should note that in reality I will be doing a SELECT on the table for things where claimed=0 ORDER BY RAND() LIMIT 1. If the update throws an exception I plan to do another SELECT to find another row, while the successful path would DELETE the row. Doing the example with id=1 is for simplicity in testing 🙂

Best Answer

Plan A -- If the task takes only a few seconds,

    START TRANSACTION;
    do all the task (possibly including FOR UPDATE)
    COMMIT;

Plan B -- If the task takes longer than a few seconds. (Note: The UPDATEs can probably be done with a single statement, hence no need for a transaction.)

    (with autocommit=ON)
    UPDATE ... to grab an item and announce who has the item
    do the task
    UPDATE ... to release the item

Plan C -- Read about GET_LOCK() and RELEASE_LOCK(). Note that there is a timeout on the GET.

lock_wait_timeout (which applies to Plan A) defaults to 50 seconds. But I think you should not have transactions that last anywhere near that long. Hence, Plan B.

Yes, Plan A might include

SELECT .. WHERE claimed=0 ORDER BY RAND() LIMIT 1  FOR UPDATE;

But notice the FOR UPDATE. And there should be no case for failure. You have limited the choices to claimed=0. Caution: if this is a huge table, the random selection will take time.

Related Question