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，有索引執行非常快。 執行量，併發量增加則更容易驗證出來是否有重複編號。