函数存储过程并发控制-案例
031-典型-存储过程并发控制
需求说明：为几个重要的生产材料生产编号。每日每个材料表产生不同的顺序编号。
多个生产材料，例如：CPU，内存，硬盘分别为C,M,H开头的编号+日期8位+顺序编号至少5位。要求每一个唯一，不能重复编号。
编号生成表：
use test;
create table pro_no(id bigint not null auto_increment primary key,type_name varchar(300),date_no varchar(300),max_no bigint);
alter table pro_no add unique index uniq_type_name_date_no(type_name,date_no);
生成编号函数如下：
delimiter &&
drop function if exists test.sf_pro_no &&
create function sf_pro_no(typeName varchar(300),dateNo varchar(8),noLen int)
returns varchar(255)
begin
declare error_code varchar(30) default '-1';
declare error_msg varchar(300) default '';
set @old_no = 0;
set @max_no = '-1';
if (typeName is null or typeName='') or (dateNo is null or dateNo='') or (noLen is null or noLen = '') then
set error_code = '-1';
set error_msg = '参数不能为空.';
set @max_no = concat('{',error_code,':',error_msg,'}');
elseif typeName not in ("CPU","MEMERY","HARD") then
set error_code = '-2';
set error_msg = 'typeName 必须是"CPU","MEMERY","HARD"之一.';
set @max_no = concat('{',error_code,':',error_msg,'}');
elseif length(dateNo) <> 8 then
set error_code = '-3';
set error_msg = '日期错误.';
set @max_no = concat('{',error_code,':',error_msg,'}');
elseif noLen <14 or noLen>100 then
set error_code = '-4';
set error_msg = '编号至少14位,最多100位.';
set @max_no = concat('{',error_code,':',error_msg,'}');
else
select ifnull(max(max_no),0)+1 into @old_no from pro_no where type_name = typeName and date_no = dateNo;
if @old_no = 1 then
insert into pro_no(type_name,date_no,max_no) values(typeName,dateNo,@old_no);
else
update pro_no set max_no = @old_no where type_name = typeName and date_no = dateNo;
end if;
set @max_no = concat(left(typeName,1),dateNo);
set @max_no = concat(@max_no,lpad(@old_no,noLen-length(@max_no),'0'));
set @max_no = concat('{0:',@max_no,'}');
#set @max_no = @old_no;
return @max_no;
end if;
end &&
delimiter ;
使用方法：
select sf_pro_no('CPU',date_format(now(),"%Y%m%d"),14);
注意的点：
1、ifnull在返回空记录的时候，不起作用。只能对值为空进行处理，因此需要使用max，使用min等也是可以的。
select ifnull(max(max_no),0)+1 into @old_no from pro_no where type_name = typeName and date_no = dateNo;
2、函数中无法使用事务，执行中出错，会导致数据不一致。（如上案例，只有一个更新，则不会，如果多次更新操作则会导致数据不一致）。
3、如上函数在并发请求的情况下，会返回重复的编号。存储过程同样。
存储过程，事务控制。 如下：
delimiter &&
drop procedure if exists test.sf_pro_no &&
create procedure sf_pro_no(in typeName varchar(300),in dateNo varchar(8),in noLen int,out maxNo varchar(300))
begin
declare error_code varchar(30) default '-1';
declare error_msg varchar(300) default '';
set @old_no = 0;
set @max_no = '-1';
if (typeName is null or typeName='') or (dateNo is null or dateNo='') or (noLen is null or noLen = '') then
set error_code = '-1';
set error_msg = '参数不能为空.';
set @max_no = concat('{',error_code,':',error_msg,'}');
elseif typeName not in ("CPU","MEMERY","HARD") then
set error_code = '-2';
set error_msg = 'typeName 必须是"CPU","MEMERY","HARD"之一.';
set @max_no = concat('{',error_code,':',error_msg,'}');
elseif length(dateNo) <> 8 then
set error_code = '-3';
set error_msg = '日期错误.';
set @max_no = concat('{',error_code,':',error_msg,'}');
elseif noLen <14 or noLen>100 then
set error_code = '-4';
set error_msg = '编号至少14位,最多100位.';
set @max_no = concat('{',error_code,':',error_msg,'}');
else
start transaction;
select ifnull(max(max_no),0)+1 into @old_no from pro_no where type_name = typeName and date_no = dateNo;
if @old_no = 1 then
insert into pro_no(type_name,date_no,max_no) values(typeName,dateNo,@old_no);
else
update pro_no set max_no = @old_no where type_name = typeName and date_no = dateNo;
end if;
commit;
set @max_no = concat(left(typeName,1),dateNo);
set @max_no = concat(@max_no,lpad(@old_no,noLen-length(@max_no),'0'));
set @max_no = concat('{0:',@max_no,'}');
select @max_no;
end if;
end &&
delimiter ;
使用方法：
call sf_pro_no('CPU',date_format(now(),"%Y%m%d"),14,@maxNo);
select @maxNo;
测试代码：
#encoding:utf-8
#Author:lzj
#Date:2024-07-12
#Description:简单并行SQL执行。
import concurrent.futures
import pymysql
def connect():
config = {
'host': '127.0.0.1',
'port': 3306,
'user': 'root',
'passwd': 'mysql',
'charset': 'utf8mb4',
'db':'test'
#'cursorclass': pymysql.cursors.DictCursor
}
conn = pymysql.connect(**config)
cursor = conn.cursor()
return conn,cursor
def call_max_no(index):
conn,cur = connect()
#conn.autocommit(1)
#sql = "select sf_pro_no('CPU',date_format(now(),'%Y%m%d'),14); "
sql = "call sf_pro_no('CPU',date_format(now(),'%Y%m%d'),14,@maxNo);"
cur.execute(sql)
data = cur.fetchall()
print(data)
cur.close()
conn.close()
def main():
#多进程
#with concurrent.futures.ProcessPoolExecutor(max_workers=10) as exector:
#多线程
with concurrent.futures.ThreadPoolExecutor(max_workers = 10) as exector:
futures = [exector.submit(call_max_no,i) for i in range(150)]
#concurrent.futures.wait(futures)
if __name__ == '__main__':
main()
执行100次，最大并发10个线程或进程，都会产生重复编号。
============== 那如何解决函数，存储过程的并发问题呢 ================
总结：
1、函数
由于无法使用事务控制语句。也无法设置autocommit=1，因此需要在代码会话设置（很多框架默认autocommit=0），数据库也可以设置，但不建议，会影响其他事务提交。
在第一条select开始,,添加for update添加ix意向排它锁，不能添加lock in share mode的is共享锁，不会阻塞其他读。
select ifnull(max(max_no),0)+1 into @old_no from pro_no where type_name = typeName and date_no = dateNo for update；
2、存储过程
显式开启事务，start transcation; commit; 很多框架默认autocommit=0。也可以在代码会话设置autocommit=1，不显式开启事务。
在第一条select开始,,添加for update添加ix意向排它锁，不能添加lock in share mode的is共享锁，不会阻塞其他读。
select ifnull(max(max_no),0)+1 into @old_no from pro_no where type_name = typeName and date_no = dateNo for update；
则都能安全的更新，但都会导致锁等待，相当于串行执行。
特别提醒，pro_no表，添加索引或唯一索引，最好是唯一索引。 如果多行则更新多行，产生更多的执行开销，锁等待。
alter table pro_no add unique index uniq_type_name_date_no(type_name,date_no);
有索引能保证update时行锁，否则则会表锁。
测试代码，在执行次数，并发小的情况下，不添加for update，IX锁，满足条件1，则可能不会产生重复编号，因为案例只有一个select insert/update，有索引执行非常快。 执行量，并发量增加则更容易验证出来是否有重复编号。
