在mysql中同步执行存储过程 [英] Synchronized stored procedure execution in mysql

问题描述

我在 mysql 中有一个存储过程，可以执行需要同步的任务，这样，如果两个应用程序调用该存储过程，则只有一个程序可以访问一段代码来执行该任务，保持另一个被阻塞，直到第一个完成任务为止.

I have a stored procedure in mysql thats to perform a task that needs to be synchronized such that if two application calls the stored procedure, only one can access a section of code to perform the task, keeping the other one to get blocked until the first one finishes 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 simultaneously, the task has to be synchronized.

a.但是开始交易和 COMMIT 没有同步执行.

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

b.并且LOCK TABLES tableA 也不能用于存储过程中以确保同步.

b. And LOCK TABLES tableA can not be used in stored procedure to ensure the synchronization too.

c.我试图在应用程序级别同步存储过程调用.我用

c. I tried to synchronize the stored procedure call in application level. I used

boost_interprocess scoped_lock lock();

boost_interprocess scoped_lock lock();

在Boost 1.41中效果很好

It worked perfectly fine in boost 1.41

但是boost 1.34库中不支持进程间锁定互斥锁，在我的情况下可用.

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

是否有一种方法可以同步代码的存储过程部分，以便当同时进行两个调用时，一个在执行另一个调用之前被阻塞?

Is there a way to synchronize the stored procedure section of code such that when two calls are made simultaneously, one gets blocked before the other gets executed?

(添加以下内容) 编辑后的代码:提供一个想法，让我了解在存储过程的同步块中要执行的操作.

(added the following) edited code: to give an idea what I am trying to perform in the synchronized block of the stored procedure.

它获取最后分配的ID，并将其递增1，并检查它是否不用于其他名称"记录. 找到有效的ID后，更新最后分配的ID记录表，然后将其与给定的名称"相关联.

It gets the last assigned id, and increment it by one and check whether it is not used for someother 'name' record. When a valid id is found, update the last assigned id record table and then associate that with the 'name' given.

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 ;

推荐答案

使用START TRANSACTION启动事务实际上并不会启动它. START TRANSACTION之后的第一个表访问.打开事务也不是并发控制的一种手段.如果您只需要这样做，则可以依靠MySQL通过GET_LOCK()，RELEASE_LOCK()和其他一些相关功能提供的咨询锁系统.

Starting a transaction with START TRANSACTION does not actually start it. The first table access following START TRANSACTION does. Opening a transaction isn't also a mean for concurrency control. If you need just that, you can rely on the advisory locks system MySQL provides through GET_LOCK(), RELEASE_LOCK(), and a few other related functions.

这次通过事务实现并发控制的另一种方法是依靠互斥行锁.因为SELECT语句在InnoDB中是非锁定的，所以发出这样的查询会启动一个事务，但是它既不设置任何锁，也不尊重任何现有的锁.如果在相同的信息(行)上有更早的事务，要使SELECT语句实际阻塞，则必须使用FOR UPDATE子句.例如:

An alternative way to implement concurrency control, through transactions this time, would be by relying on exclusive row locks. Since SELECT statements are non-locking in InnoDB, issuing such query starts a transaction, however it neither sets any locks nor respects any pre-existing ones. To have a SELECT statement actually block if there is an earlier transaction operating on the same information (row), you have to use FOR UPDATE clause. For example:

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

通过这种构造，在明确要求执行锁定读取的SELECT语句之后，将不会有两个并发事务在同一'NAME_ID'上进行操作.

With this construction there will never be two concurrent transactions operating on the same 'NAME_ID' past the SELECT statement that was explicitly told to perform a locking read.

这篇关于在mysql中同步执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！