mysql+mybatis实现存储过程+事务 + 多并发流水号获取_Mysql_

DROP PROCEDURE IF EXISTS `generate_serial_number_by_date`; CREATE PROCEDURE `generate_serial_number_by_date`( IN param_key varchar(100), IN param_org_id bigint, IN param_period_date_format varchar(20), OUT result bigint, OUT current_datestr varchar(20)) begin declare old_datestr varchar(20); START TRANSACTION; if param_period_date_format='infinite' then set current_datestr = '00000000'; else set current_datestr = DATE_FORMAT(NOW(), param_period_date_format); end if; select number, datestr from sys_serial_number where table_key = param_key and org_id = param_org_id and period_date_format = param_period_date_format into result, old_datestr for update; IF result is null then set result = 1; insert into sys_serial_number(table_key, org_id, period_date_format, datestr, number, description) values(param_key, param_org_id, param_period_date_format, current_datestr, 1, 'add by procedure'); elseif old_datestr != current_datestr then set result = 1; update sys_serial_number set number = 1, datestr = current_datestr where table_key = param_key and org_id = param_org_id and period_date_format = param_period_date_format; end if; update sys_serial_number set number = number + 1 where table_key = param_key and org_id = param_org_id and period_date_format = param_period_date_format; commit; end

 DROP TABLE IF EXISTS `sys_serial_number`; CREATE TABLE `sys_serial_number` ( `table_key` varchar(100) NOT NULL COMMENT '主键（建议用表名）', `org_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '分公司ID', `number` bigint(20) NOT NULL DEFAULT '1' COMMENT '流水号（存储过程控制递增，获取完后+1）', `period_date_format` varchar(20) NOT NULL COMMENT '流水号生成周期日期格式', `datestr` varchar(20) DEFAULT NULL COMMENT '流水号日期值', `description` varchar(100) DEFAULT NULL COMMENT '描述', PRIMARY KEY (`table_key`,`org_id`,`period_date_format`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='流水号生成表';

 @Override public Map generateSerialNumber(Map param) { sysSerialNumberMapper.generateSerialNumber(param); return param; }

 final Map param = new HashMap(); param.put("param_key","contract"); param.put("param_orgId", 84); new Thread(new Runnable() { @Override public void run() { for(int i =0; i<100; i++) { Map map = serialNumberProvider.generateSerialNumber(param); System.out.println("thread-1：" + map.get("result")); } } }).start(); new Thread(new Runnable() { @Override public void run() { for(int i =0; i<100; i++) { Map map = serialNumberProvider.generateSerialNumber(param); System.out.println("thread-2：" + map.get("result")); } } }).start(); new Thread(new Runnable() { @Override public void run() { for(int i =0; i<100; i++) { Map map = serialNumberProvider.generateSerialNumber(param); System.out.println("thread-3：" + map.get("result")); } } }).start(); byte[] b = new byte[0]; synchronized(b) { b.wait(); }