SQL/MySQL/Procedure Function/Lock

Get lock in a procedure

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()

   -> BEGIN
   ->     DECLARE lock_result INT;
   ->     IF get_lock("sp_critical_section_lock",60) THEN
   ->        /* This block can only be run by one user at a time*/
   ->        SELECT "got lock";
   ->        /* Critical code here */
   ->       SET lock_result=release_lock("sp_critical_section_lock");
   ->     ELSE
   ->        SELECT "failed to acquire lock";
   ->        /* Error handling here */
   ->     END IF;
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> call myProc(); +----------+ | got lock | +----------+ | got lock | +----------+ 1 row in set (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql>

       </source>