我的存储程序载于mysql,执行需要同步的任务,这样,如果两项申请要求所储存的程序,只有一人才能进入法典的一个部分来执行任务,使另一人受阻,直到任务完成后。

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 ;

因此,如果两项申请同时采用所储存的程序,任务必须同步进行。

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

b) 而LOCK TABLES tableA不能用于储存程序以确保同步。

c) 我试图使所储存的程序达到应用水平。 I 旧

提升——内部处理范围:24小时(a);

该公司在提高1.41英寸方面做得很好。

但是,在1.34个图书馆中,锁定双向图书馆没有得到支持,而这正是我的案例。

是否有办法使所储存的法典程序部分同步,这样,当同时发出两条呼吁时,有人在对方被执行之前被阻断?

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