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

业务场景

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

解决方案1

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

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

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

解决方案2

一开始由于经验不足，我写了一个函数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的这两步操作是原子操作，即两步操作之间不能被其他程序查到同一行数据并更新同一行数据。

解决方案3-加锁

再简化一下我们的目的：我们希望获取流水号和更新流水号之间，该行数据不被其他程序查询和更新流水号就能实现我整个报卡表的流水号都是唯一的。
既然我们只需要关注流水号某一行的数据，那么我们自然可以想到加锁！因为我们对某一行先查然后更新，并且希望我在更新操作完成之后，其他程序才能查这一行的数据，这样就能保证我的流水号是唯一的了。
在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;之间的所有代码都被视为原子操作。