在mysql函数中启动事物和行锁/悲观锁实现并发条件下获得唯一流水号

  我有一个业务需求：我有一个报卡表report里面有一个登记号字段fcardno、地区代码faddrno和发病年份fyear，登记号由**“4位地区代码”+“00”+“发病年份”+“5位流水号”**组成，我要在每次插入一张报卡（每一行数据）的时候给每一张报卡生成唯一的登记号。
  登记号其他都好处理，主要是我们要怎么获取唯一的流水号，特别是并发情况。

  在不考虑并发的条件下，我可以随便写，我简单粗暴直接：

select count(*) +1 
from report 
where faddrno = {待插入行的faddrno} and fyear = {待插入行的fyear}

上面的查询结果就是我的流水号，然后前面填充0补齐5位就可以了。

  一开始由于经验不足，我写了一个函数f_getreportid_testJob并创建了一个表专门记录流水号的表ib_tbs_ib_tbs_tumorcardno_test。

表里的数据大致是：

f_getreportid_testJob函数里做的是就是查流水号表如果表里该地区和年份有记录则将该行的fno+1作为当前报卡的流水号，然后更新流水号表该行的fno = fno +1；如果改表中没有该地区和年份的记录那么，就插一条fno=1的记录并且该报卡的流水号就为1。那么就可以初步实现为每个地区和年份记录连续的流水号了。f_getreportid_testJob代码如下：

CREATE DEFINER=`root`@`%` FUNCTION `f_getreportid_testJob`(`as_fyearno` VARCHAR(20),`as_faddrno` VARCHAR(20)) RETURNS varchar(200) CHARSET utf8mb4
    DETERMINISTIC
    COMMENT '获取上报卡编号函数'
BEGIN
		#市编码+00+年份+五位数字
  	DECLARE ls_fnum int DEFAULT 0;
		DECLARE ls_fcount int DEFAULT 0;#用来判断ib_tbs_ib_tbs_tumorcardno_test表中是否有数据
		DECLARE reportid varchar(255) DEFAULT ''; 
		
		if ifnull(as_fyearno,'') = '' or ifnull(as_faddrno,'') = '' then
				RETURN null;
		end if;
		
		select count(*) into ls_fcount
		from ib_tbs_ib_tbs_tumorcardno_test
		where fcityno = substr(as_faddrno,1,4)
		and fyearno = as_fyearno;
		
		if ls_fcount <> 0 then
				select fno + 1 into ls_fnum
				from ib_tbs_ib_tbs_tumorcardno_test
				where fcityno = substr(as_faddrno,1,4)
				and fyearno = as_fyearno;-- 获取流水号
				
				update ib_tbs_ib_tbs_tumorcardno_test set fno = fno + 1
				where fcityno = substr(as_faddrno,1,4)
				and fyearno = as_fyearno;-- 更新流水号
		else
				insert into ib_tbs_ib_tbs_tumorcardno_test(fcityno,fyearno,fno/*流水号*/)
				VALUES(substr(as_faddrno,1,4),as_fyearno,1);
				set ls_fnum = 1;
		end if;
		
		SET reportid = CONCAT(substr(as_faddrno,1,4),'00',as_fyearno,lpad(ls_fnum,5,'0'));
	RETURN reportid;
  END

问题暴露：
  在测试阶段没有发现问题，系统上线一段时间有发现有一些重复的登记号，简单说就是流水号重复了。查看每张报卡的插入时间是相同的：

  上面的函数中我们获取流水号的时候是先查(select)然后再将新的流水号更新(update)到流水号表，这两步操作在并发的情况下，两步操作之间就可能穿插其他其他用户的操作，比如其他用户的程序也在调用这个函数并且也刚好执行到更新同一行的fno操作，此时后者拿到的流水号与前者是相同的，那么就重复了。
  所以在并发情况下，我们还要拿到唯一的流水号就需要保证我查(select)当前流水号以及我取到一个新的流水号并更新(update)流水号fno的这两步操作是原子操作，即两步操作之间不能被其他程序查到同一行数据并更新同一行数据。

  再简化一下我们的目的：我们希望获取流水号和更新流水号之间，该行数据不被其他程序查询和更新流水号就能实现我整个报卡表的流水号都是唯一的。
  既然我们只需要关注流水号某一行的数据，那么我们自然可以想到加锁！因为我们对某一行先查然后更新，并且希望我在更新操作完成之后，其他程序才能查这一行的数据，这样就能保证我的流水号是唯一的了。
  在mysql中默认以自动提交模式运行，也就是说我在存储过程中操作数据库在存储过程执行完毕后不需要我们显示使用commit提交事物，mysql会自动替我们提交事物；你也可以使用START TRANSACTION;手动开启一个事物，在事务结束的时候使用COMMIT提交事务，或使用ROLLBACK回滚事务。
  我们在原来的函数里面开启事物，并且使用行锁(或者叫悲观锁、排它锁。我在这里直接叫行锁是因为我查询和更新的时候都是可以通过fcityno和fyearno可以定位到唯一一行的，这两个字段是联合主键能确定唯一一行，所以在InnoDB引擎下它就是一个行级锁)，注意FOR UPDATE必须在事务的上下文中使用。如果没有开启事务，那么锁将没有意义，因为锁定会在每个单独的查询后自动释放。
  所以我们只要将上面的函数修改为存储过程并加一个OUT 参数用来记录生成的登记号，开启事物后，在获取流水号的sql语句结尾加上FOR UPDATE;就可以了，代码可参考：

CREATE DEFINER=`root`@`%` PROCEDURE `p_getreportid_testJob`(`as_fyearno` VARCHAR(20),`as_faddrno` VARCHAR(20),OUT `as_rslt` varchar(500))
BEGIN
	#市编码+00+年份+五位数字
  	DECLARE ls_fnum int DEFAULT 0;
		DECLARE ls_fcount int DEFAULT 0;#用来判断ib_tbs_ib_tbs_tumorcardno_test表中是否有数据
		DECLARE reportid varchar(255) DEFAULT ''; 
		
		if ifnull(as_fyearno,'') = '' or ifnull(as_faddrno,'') = '' then
				set as_faddrno = null;
		
		else
		-- 开始事务  
			START TRANSACTION;
			
			select count(*) into ls_fcount
			from ib_tbs_ib_tbs_tumorcardno_test
			where fcityno = substr(as_faddrno,1,4)
			and fyearno = as_fyearno;
			
			if ls_fcount <> 0 then
					select fno + 1 into ls_fnum
					from ib_tbs_ib_tbs_tumorcardno_test
					where fcityno = substr(as_faddrno,1,4)
					and fyearno = as_fyearno
					FOR UPDATE; -- 使用FOR UPDATE锁定记录行
					
					update ib_tbs_ib_tbs_tumorcardno_test set fno = fno + 1
					where fcityno = substr(as_faddrno,1,4)
					and fyearno = as_fyearno;
			else
					insert into ib_tbs_ib_tbs_tumorcardno_test(fcityno,fyearno,fno/*流水号*/)
					VALUES(substr(as_faddrno,1,4),as_fyearno,1);
					set ls_fnum = 1;
			end if;
			
			COMMIT;-- 提交事务自动释放锁
			
			SET reportid = CONCAT(substr(as_faddrno,1,4),'00',as_fyearno,lpad(ls_fnum,5,'0'));
			
			set as_rslt = reportid;
		
		end if;
END

  其实前面已经能实现我们的需求了，但是由于实际限制获取登记号的函数f_getreportid_testJob函数我不能改为存储过程，我希望它还是一个函数，并且我并发的情况下一般都是使用存储过程调用这个函数，那我只需要在存储过程中调用这个获取登记号的函数前开启事物就可以了，然后f_getreportid_testJob几乎不需要怎么修改:

CREATE DEFINER=`root`@`%` FUNCTION `f_getreportid_testJob`(`as_fyearno` VARCHAR(20),`as_faddrno` VARCHAR(20)) RETURNS varchar(200) CHARSET utf8mb4
    DETERMINISTIC
    COMMENT '获取上报卡编号函数'
BEGIN
		#市编码+00+年份+五位数字
  	DECLARE ls_fnum int DEFAULT 0;
		DECLARE ls_fcount int DEFAULT 0;#用来判断ib_tbs_ib_tbs_tumorcardno_test表中是否有数据
		DECLARE reportid varchar(255) DEFAULT ''; 
		
		if ifnull(as_fyearno,'') = '' or ifnull(as_faddrno,'') = '' then
				RETURN null;
		end if;
		
		
		select count(*) into ls_fcount
		from ib_tbs_ib_tbs_tumorcardno_test
		where fcityno = substr(as_faddrno,1,4)
		and fyearno = as_fyearno;
		
		if ls_fcount <> 0 then
				select fno + 1 into ls_fnum
				from ib_tbs_ib_tbs_tumorcardno_test
				where fcityno = substr(as_faddrno,1,4)
				and fyearno = as_fyearno
				FOR UPDATE; -- 使用FOR UPDATE锁定记录行,只加这一个
				
				update ib_tbs_ib_tbs_tumorcardno_test set fno = fno + 1
				where fcityno = substr(as_faddrno,1,4)
				and fyearno = as_fyearno;
		else
				insert into ib_tbs_ib_tbs_tumorcardno_test(fcityno,fyearno,fno/*流水号*/)
				VALUES(substr(as_faddrno,1,4),as_fyearno,1);
				set ls_fnum = 1;
		end if;
		
		SET reportid = CONCAT(substr(as_faddrno,1,4),'00',as_fyearno,lpad(ls_fnum,5,'0'));
	RETURN reportid;
  END

  在存储过程中就按下面的代码进行调用：

CREATE DEFINER=`root`@`%` PROCEDURE `p_call_getreportid_testJob`()
BEGIN
	# 模拟一个存储过程调用f_getreportid_testJob获取函数
	# 将获取的流水号插入到q3表
	
	START TRANSACTION;-- 开始事务 
	
	insert into q3(c1)
	select f_getreportid_testJob('2023','4404');-- 获取流水号
	
	COMMIT;-- 提交事务自动释放锁
	
END

执行完后q3.c1就有值:440400202316430

  需要注意的是，如果外层的存储过程的处理逻辑比较多，甚至你还开启了循环，所以你最好尽量在调用f_getreportid_testJob函数的附近（最好就是前一行）开启事物，因为START TRANSACTION; 和下一个COMMIT;之间的所有代码都被视为原子操作。

  我用Navicat开了五个窗口，分别调用p_call_getreportid_testJob存储过程，并传参p1、p2、p3、p4、p5，然后这个存储过程每次执行会调用登记号函数10000次，模拟并发场景。q3最终会插入5万条数据，如果q3表的c1字段没有重复的话就表示并发没有问题，我们用q3模拟业务中的登记号。

CREATE DEFINER=`root`@`%` PROCEDURE `p_call_getreportid_testJob`(`as_p` VARCHAR(20))
BEGIN
	# 模拟一个存储过程调用f_getreportid_testJob获取函数
	# 将获取的流水号插入到q3表
	DECLARE done INT DEFAULT 0;
	set done = 0;
	
	WHILE done < 10000 DO
	
			START TRANSACTION;-- 开始事务 
	
			insert into q3(c1,c2)
			select f_getreportid_testJob('2023','4404'),as_p;-- 获取流水号
			
			COMMIT;-- 提交事务自动释放锁
			
			set done = done + 1;
	END WHILE;

END

  然后在五个Navicat调用这个存储过程：

call p_call_getreportid_testJob('p5'); -- 参数每个窗口改一下

不加FOR UPDATE行锁：（把f_getreportid_testJob函数中的FOR UPDATE注释起来）
  执行测试前清空q3表，并查询一下流水号表（流水号表一般都不会有问题update操作mysql会自己判断锁，我们主要看q3表有没有重复的登记号，q3是我们业务中要获得的登记号）

执行测试后：

查一下q3看看有没有重复的登记号：

  可以看到不加锁的情况下5万条数据有8条是重复的（感觉也还好）。
加FOR UPDATE行锁：
  执行测试前清空q3并且重置流水号为1，重新开5个窗口进行测试后：

查一下q3看看有没有重复的登记号：

执行测试前后查了流水号表发现fno最后是增加了50000，并且q3.c1也没有重复，最后一个登记号是440400202350001，说明并发下是没有问题的。
  如果对于FOR UPDATE锁还是不明白或者不知道锁的是行还是整个表的话，你可以用navicate开两个查询窗口自己测一下，注意测试前一定要开启事物。

START TRANSACTION;-- 开始事务 （这个可以不执行，因为如果你不写的话一个窗口就是一个事物）

select fno
from ib_tbs_ib_tbs_tumorcardno_test 
where fcityno = '4404' and fyearno = '2023'
for update;-- 测试的时候你可以选中当前行及前面的所有代码执行执行
-- 我的ib_tbs_ib_tbs_tumorcardno_test是fcityno,fyearno为联合主键，所以当前是行锁，你随便换一个参数值，只要其他窗口没有查该行那么就能直接获得结果，否则就要阻塞等待

COMMIT;-- 提交事务自动释放锁，执行这一行后其他阻塞着的窗口才能获得上面sql的结果

  再多提一句FOR UPDATE是个排它锁，所以再对锁住的行加一个排它锁另一个加锁的查询就会阻塞等待，但是如果您另一个窗口查同一个sql语句的时候没有使用FOR UPDATE那么这个sql不会被阻塞。