Can I create a semaphore with locked transactions in 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 :)
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.