Running a synchronized stored procedure in mysql

Question

I have a stored procedure in mysql that performs a task that needs to be synchronized, so if two applications call the stored procedure, only one can access a section of code to complete the task, leaving the other lock until the first completes the task.

DELIMITER $$ CREATE PROCEDURE SP_GEN_ID(IN NAME VARCHAR(20)) BEGIN DECLARE maxLen int default 0; START TRANSACTION; #the section of code that needs to be synchronized COMMIT END; $$ DELIMITER ; 

So, if two applications call the stored procedure at the same time, the task must be synchronized.

but. But Start TRANSACTION and COMMIT did NOT synchronize the execution.

b. And LOCK TABLES tableA cannot be used in a stored procedure to provide synchronization.

from. I tried to synchronize the stored procedure call at the application level. I used

boost_interprocess scoped_lock lock ();

It worked great in boost 1.41

But the interprocess lock mutex is not supported in boost 1.34 library, which is available in my case.

Is there a way to synchronize a section of a stored code procedure so that when two calls are made simultaneously, one of them will be blocked before the other is executed?

(added the following) edited code: to give an idea of ​​what I'm trying to execute in a synchronized block of a stored procedure.

It receives the last assigned identifier and increments it by one and checks to see if it is being used for any other name entry. When the correct identifier is found, update the last assigned table of identifier entries, and then match this with the specified name.

 DELIMITER $$ CREATE PROCEDURE SP_GEN_ID(IN NAME VARCHAR(20)) BEGIN DECLARE maxLen int default 0; START TRANSACTION; #the section of code that needs to be synchronized SELECT lastid into lastgenerated FROM DB_last_id WHERE key = 'NAME_ID'; findid_loop: LOOP set lastid = lastid + 1; #this is to check whether it was assigned with someother name before. IF not EXISTS (SELECT 1 FROM user_name_id WHERE name_id = lastgenerated) then set nameid = lastgenerated; set found = true; LEAVE findid_loop; END IF; #for loop limit check IF (counter < loopLimit) then set counter = counter + 1; ITERATE findid_loop; ELSE #reached the limit and not found. LEAVE findid_loop; END IF; END LOOP findid_loop; #if a valid id, update last id and assign to name. IF (found) THEN #update the id. update DB_last_id set lastid = nameid where key = 'NAME_ID'; insert into user_name_id values (nameid ,name); ELSE #return an empty string for the application to take action on the failure. set nameid = ''; END IF; #end transaction here. COMMIT END; $$ DELIMITER ; 

Answer 1

Starting a transaction using START TRANSACTION does not actually start it. First access to the table after START TRANSACTION. Opening a transaction is also not a concurrency control tool. If you need it, you can rely on the MySQL advisory lock system through GET_LOCK() , RELEASE_LOCK() and several other related functions.

An alternative way to implement concurrency control through transactions this time will consist of exclusive row locks. Since SELECT not blocked in InnoDB, issuing such a query triggers a transaction, however it does not set any locks or preserve any existing ones. In order for the SELECT actually block if the previous transaction is working with the same information (row), you should use the FOR UPDATE clause. For example:

 START TRANSACTION; SELECT lastid into lastgenerated FROM DB_last_id WHERE key = 'NAME_ID' FOR UPDATE; ... 

With this construct, there will never be two concurrent transactions running on the same 'NAME_ID' , after the SELECT , which was explicitly used to perform a read with a lock.

Answer 2

As mentioned in my comments above, you should find that transactions are sufficient for most needs; however, if you need to explicitly wait for another call to complete, use GET_LOCK(str,timeout) :

Attempts to obtain a lock with the name given by str using the timeout timeout seconds. Returns 1 if the lock was successfully received, 0 if a timeout attempt (for example, because another client had previously blocked the name), or NULL if an error occurred (for example, out of memory or the thread was killed using mysqladmin kill ). If you have a lock obtained with GET_LOCK() , it is issued when RELEASE_LOCK() is executed, execute a new GET_LOCK() , or your connection is terminated (normal or abnormal). Locks received with GET_LOCK() do not interact with transactions. That is, the transaction does not release such locks received during the transaction.

This function can be used to implement application locks or to simulate record locks. Names are blocked on the server as a whole. If a name is blocked by one client, GET_LOCK() blocks any request from another client to block with the same name. This allows customers who agree with the given lock name to use the name to perform joint advisory locks. But keep in mind that it also allows a client that is not among the interacting clients to block the name, unintentionally or intentionally, and thus prevent any of the collaborating clients from blocking the name. One way to reduce the likelihood of this is to use lock names specific to a particular database or application. For example, use lock names of the form db_name.str or app_name.str .

  mysql> SELECT GET_LOCK ('lock1', 10);
         -> 1
 mysql> SELECT IS_FREE_LOCK ('lock2');
         -> 1
 mysql> SELECT GET_LOCK ('lock2', 10);
         -> 1
 mysql> SELECT RELEASE_LOCK ('lock2');
         -> 1
 mysql> SELECT RELEASE_LOCK ('lock1');
         -> NULL

The second call to RELEASE_LOCK() returns NULL because the lock 'lock1' was automatically released by the second GET_LOCK() .

If several clients are waiting for the block, the order of their acquisition will be undefined and depends on factors such as the thread library used. In particular, applications should not assume that clients will receive a lock in the same order in which they issued lock requests.

Note
Before MySQL 5.5.3, if a client tries to get a lock that is already held by another client, it locks according to the timeout argument. If a blocked client exits, its thread does not die until the request for blocking expires.

This feature is not safe for instruction-based replication. Starting with MySQL 5.5.1, a warning is logged if you use this function when binlog_format set to STATEMENT . (Error No. 47995)