Code Bye

MySQL如何实现互斥访问，锁表？

码拜
 

由于在MySQL中没有类似Oracle的序列的概念，因此决定通过一个表和两个存储过程来模拟这个功能。

我希望来自不同服务器的连接调用时，会有同步功能。

Create Table tbSequence(Id Int Unsigned Not Null Auto_Increment Primary Key, SequenceName Varchar(50) Not Null, SequenceValue Bigint Not Null);
Delimiter ;
Drop Function If Exists `fnNextSequence`;
Delimiter ;;
Create Function `fnNextSequence`(
	p_SequenceName Varchar(50)
) Returns Bigint 
Begin 
	
	Declare v_Result Bigint Default Null;
	
	-- Lock Tables tbSequence Write;
	-- Set Autocommit = 0;
	-- Start Transaction;
	Select SequenceValue Into v_Result From tbSequence Where SequenceName = p_SequenceName;
	If v_Result Is Not Null Then 
		Set v_Result = v_Result + 1;
		Update tbSequence Set SequenceValue = v_Result Where SequenceName = p_SequenceName;
	Else 
		Set v_Result = 1;
		Insert Into tbSequence(SequenceName, SequenceValue) Values(p_SequenceName, v_Result);
	End If;
	
	-- Unlock Tables tbSequence;
	-- Commit;
	
	Return v_Result;
End;;
Delimiter ;
Drop Function If Exists `fnCurrentSequence`;
Delimiter ;;
Create Function `fnCurrentSequence` (
	p_SequenceName Varchar(50)
) Returns Bigint 
Begin 
	Declare v_Result Bigint Default Null;
	
	-- Lock Tables tbSequence Write;
	-- Set AutoCommit = 0;
	-- Start Transaction;
	Select SequenceValue Into v_Result From tbSequence Where SequenceName = p_SequenceName;
	
	-- Unlock Tables tbSequence;
	-- Commit;
	
	Return v_Result;
End;;

但是MySQL提示在存储过程中不允许锁表，也不允许启动事务。

该如何解决啊？
#1

10分
不是有自增id吗
#2

10分
没有序列号。也没有互斥。

可以考虑用锁。 或者创建一个表，主键为自动增长的序列号，然后每次插入一条记录，得到last_insert_id
#3

10分
SP中可以用事务，你的代码贴出来
#4

回复2楼:

我可能有多个序列号，这样不是每个序列要创建一个表？
#5

回复3楼:

代码在第一楼里就帖了啊

启用事务时，说在过程里不允许有事务

锁表的时候，也说在过程里不允许锁表

两种办法的语句都在里面被注释过了的
#6

10分
自定义函数改成SPS

SP也可以返回参数
#7

10分

回复4楼:

是的，这是一种最简单的方法了。利用MYSQL本身的 auto_increment

类似的，在ORACLE中不也同样要创建很多 sequence 吗？
#8

回复6楼:

什么是SPS？

过程可以返回值，但是我要求加锁，当多个线程请求获得新序列号时，不会得到同一个值
#9

10分
就是SP，多线程访问　，必须锁表，替换后再释放，进行再一次替换
#10

回复9楼:

那到底要怎么做呢？
#11

10分
假设AA1保存最后1个ID

在SP中

BEGIN WORK;

SELECT id FROM aa1 WHERE id=(SELECT MAX(id) FROM aa1) FOR UPDATE

update aa1 set id=max(id)+1;

COMMIT WORK;
#12

回复11楼:

delimiter ;
drop function if exists `fnNextSequence1`;
delimiter ;;
create function `fnNextSequence1` (
	p_name varchar(50)
) returns bigint 
begin 
	begin work;
		declare v_result bigint;
		if exists(select * from tbSequence where SequenceName = p_name) then 
			select SequenceValue +1 into v_result from tbSequence where SequenceName = p_name;
			update tbSequence set SequenceValue = SequenceValue + 1 where SequenceName = p_name;
		else 
			set v_result = 1;
			insert into tbSequence(SequenceName, SequenceValue) Values(p_name, 1);
		end if;
		return v_result;
	end work;
end;;

报错：

[Err] 1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near “”;

declare v_result bigint;

if exists(select * from tbSequence where Sequen”” at line 5
#13

10分
declare v_result bigint default  0;

select 1 into v_result bigint from tbSequence where SequenceName = p_name

        if v_result bigint>1 then …..
#14

回复13楼:

我想知道的是：怎么实现互斥，哥哥
#15

10分
你没有仔细看看别给的代码吗？

假设AA1保存最后1个ID

 在SP中

 BEGIN WORK;

 SELECT id FROM aa1 WHERE id=(SELECT MAX(id) FROM aa1) FOR UPDATE

 update aa1 set id=max(id)+1;

 COMMIT WORK;

锁表，替换　，释放　
#16

回复15楼:

在过程中似乎不允许出现 begin work; 语句，否则报语法错误，以下是简单的测试代码

delimiter ;

drop procedure if exists `spNextSequence3`;

delimiter ;;

create procedure `spNextSequence3`(

p_name varchar(50), 

out p_result bigint 

) 

begin 

begin work;

update tbSequence set SequenceValue = SequenceValue + 1 Where SequenceName = p_name;

commit work;

end ;;

—————

[Err] 1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near “”;

update tbSequence set SequenceValue = SequenceValue + 1 Where SequenceName “” at line 6
#17

10分

 

DELIMITER ;

 DROP PROCEDURE IF EXISTS `spNextSequence3`;

DELIMITER ;;

 CREATE PROCEDURE `spNextSequence3`(p_name VARCHAR(50),OUT p_result BIGINT) 

 BEGIN 

 START TRANSACTION ;

 SELECT SequenceValue FROM tbSequence WHERE SequenceName = p_name FOR UPDATE;

 UPDATE tbSequence SET SequenceValue=SequenceValue+1 WHERE SequenceName = p_name;

 COMMIT ;

 

 END;;

DELIMITER ;

还少了1句

SELECT SequenceValue FROM tbSequence WHERE SequenceName = p_name FOR UPDATE;
#18

回复17楼:

不是多了条语句还是少了条语句的问题了

问题是，加了 begin work, begin transaction, start work, start transaction 之类的语句之后，mysql 说你有语法错误了
#19
在SP中没有问题上述代码在MYSQL5。.中测试通过，加入

 SELECT SequenceValue FROM tbSequence WHERE SequenceName = p_name FOR UPDATE;是锁

表
#20
我的版本是 mysql5.6.25 社区版，在CentOS 7上运行

#21
别人的回复不看看吗？

是CREATE PROCEDURE

不是FUNCTION
#22

回复21楼:

Function和Procedure 都试过了

只要里面出现 begin work, begin transaction 就是编译不过

算了，其实办法已经找到，就不纠结这个问题了，通过调用 mysql 的函数 Get_Lock 和 RELEASE_LOCK 即可：

Delimiter ;
Drop Function If Exists `fnNextSequence`;
Delimiter ;;
Create Function `fnNextSequence`(
	p_SequenceName Varchar(50)
) Returns Bigint
Begin 
	
	Declare v_Result Bigint Default Null;
	Declare v_MaxValue Bigint Default Null;
	Declare v_Lock Int Unsigned;
	
	Select Get_Lock(p_SequenceName, 1) Into v_Lock;
	
	Select `SequenceValue`, `MaxValue` Into v_Result, v_MaxValue From tbSequence Where SequenceName = p_SequenceName;
	
	If v_Result Is Not Null Then 
		Set v_Result = v_Result + 1;
		if (v_MaxValue Is Not Null) And (v_Result > v_MaxValue) THEN
			 Set v_Result = 1;
		END IF;
		Update tbSequence Set SequenceValue = v_Result Where SequenceName = p_SequenceName;
	Else 
		Set v_Result = 1;
		Insert Into tbSequence(SequenceName, SequenceValue) Values(p_SequenceName, v_Result);
	End If;
	
	Select RELEASE_LOCK(p_SequenceName) Into v_Lock;
	
	Return v_Result;
End;;
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明MySQL如何实现互斥访问，锁表？！