Locking in MySQL stored procedure for deduplication
Will this work as intended?


Not really.

You've indented the code as if you expect that the statements betweeb DO GET_LOCK... and DO RELEASE_LOCK... would only execute if the lock is obtained.  

That is not the case.

You shouldn't use GET_LOCK() in a void context (with DO) unless you don't actually care whether you actually got the lock.  What you have written will wait up to 10 seconds for the lock, and if it isn't available, continues anyway with the next statement.  That does not sound like what you want.

A better solution for MySQL 5.5 and later looks like this:

IF GET_LOCK('my_lock',10) IS NOT TRUE THEN
  SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = 'timed out waiting for named semaphore lock';
END IF;


That will throw an exception and terminate the procedure after the timeout, if the lock has not been obtained.  (This is a useful tactic with the event scheduler, when you need to ensure that concurrent invocations of events don't pile up.)

Note that the logical operator IS NOT TRUE, used above, matches both 0 (FALSE) and NULL, either of which can indicate that you didn't get the lock.

The same is true of RELEASE_LOCK(), which returns TRUE only if you actually held the lock you tried to release.  If you try to release a lock you don't own, that does not throw an error.  You have to check return values.


  In particular, I'm worried that the commit won't happen until after the lock is released.


No, the lock is released after the commit, here.  The get and release operations are only functions.  They execute in sequence like everything else.

Also, it's usually best to leave the caller in charge of the transaction, because if this procedure is called while already in a transaction, START TRANSACTION silently commits the previous transaction and starts a new one, leaving the caller unable to roll back any work done before the procedure was called.  That may not be applicable, here, but it's good practice to handle it consistently across your code base.