mysql中的同步存储过程执行

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

分隔符 $$创建过程 SP_GEN_ID(IN NAME VARCHAR(20))开始DECLARE maxLen int 默认 0；开始交易；#需要同步的代码段犯罪结尾;$$分隔符；

因此，如果两个应用程序同时调用存储过程，则任务必须同步.

一个.但是 Start TRANSACTION 和 COMMIT 没有同步执行.

b.并且LOCK TABLES tableA也不能用在存储过程中来保证同步.

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

<块引用>

boost_interprocess scoped_lock lock();

它在 boost 1.41 中运行良好

但是 boost 1.34 库不支持进程间锁定互斥锁，这在我的案例中是可用的.

有没有办法同步代码的存储过程部分，这样当同时进行两个调用时，一个在另一个被执行之前被阻塞?

(添加以下内容)编辑后的代码:给出一个想法，我想在存储过程的同步块中执行什么.

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

分隔符 $$创建过程 SP_GEN_ID(IN NAME VARCHAR(20))开始DECLARE maxLen int 默认 0；开始交易；#需要同步的代码段选择 lastid 到 lastgenerated FROM DB_last_id WHERE key = 'NAME_ID';findid_loop:环形设置 lastid = lastid + 1;#这是为了检查它之前是否被分配了其他名称.IF not EXISTS (SELECT 1 FROM user_name_id WHERE name_id = lastgenerated) 然后设置 nameid = lastgenerated;设置找到=真；离开 findid_loop;万一;#for循环限制检查IF (计数器 < loopLimit) 然后设置计数器 = 计数器 + 1；迭代 findid_loop；别的#达到限制但未找到.离开 findid_loop;万一;结束循环 findid_loop;#如果一个有效的id，更新最后一个id并分配给name.如果(找到)然后#更新身份证.更新 DB_last_id 设置 lastid = nameid where key = 'NAME_ID';插入 user_name_id 值(nameid ,name)；别的#return 一个空字符串，让应用程序对失败采取措施.设置 nameid = '';万一;#在这里结束交易.犯罪结尾;$$分隔符；

解决方案

使用 START TRANSACTION 启动事务实际上并没有启动它.START TRANSACTION 之后的第一个表访问确实如此.打开事务也不是并发控制的手段.如果你只需要这个，你可以依赖 MySQL 通过 GET_LOCK()、RELEASE_LOCK() 和其他一些相关函数提供的建议锁系统.

这次通过事务实现并发控制的另一种方法是依靠排他行锁.由于 SELECT 语句在 InnoDB 中是非锁定的，因此发出这样的查询会启动一个事务，但是它既不设置任何锁也不尊重任何预先存在的锁.如果在同一信息(行)上存在较早的事务操作，要使 SELECT 语句实际阻塞，您必须使用 FOR UPDATE 子句.例如:

开始交易；选择 lastid 到 lastgenerated FROM DB_last_id WHERE key = 'NAME_ID' FOR UPDATE;...

使用这种结构，在明确告知执行锁定读取的 SELECT 语句之后，永远不会有两个并发事务在同一个 'NAME_ID' 上运行.p>

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. But Start TRANSACTION and COMMIT did NOT synchronize the execution.

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

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

boost_interprocess scoped_lock lock();

It worked perfectly fine in boost 1.41

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.

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 ;

解决方案

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.

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;
...

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.