MySQL如何实现互斥访问,锁表?

MySql 码拜 9年前 (2015-09-27) 1901次浏览
 

由于在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上运行

MySQL如何实现互斥访问,锁表?

#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如何实现互斥访问,锁表?
喜欢 (0)
[1034331897@qq.com]
分享 (0)