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

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='流水号生成表';

<select id="generateSerialNumber" parameterType="java.util.HashMap" statementType="CALLABLE">
  <![CDATA[
       {
       call generate_serial_number (
      #{param_key,mode=IN,jdbcType=VARCHAR},
      #{param_org_id,mode=IN,jdbcType=BIGINT},
      #{result,mode=OUT,jdbcType=BIGINT}
      )
       }
     ]]>
  </select>

@Override
  public Map<String, Object> generateSerialNumber(Map<String, Object> param) {
    sysSerialNumberMapper.generateSerialNumber(param);
    return param;
  }

final Map<String, Object> param = new HashMap<String, Object>();
    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<String, Object> 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<String, Object> 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<String, Object> map = serialNumberProvider.generateSerialNumber(param);
          System.out.println("thread-3：" + map.get("result"));
        }
      }
    }).start();
 
    byte[] b = new byte[0];
    synchronized(b) {
      b.wait();
    }

### SQL: 
{      
call generate_serial_number_by_date (       
?,       ?,       ?,       ?,       ?      
)      
}
### Cause: java.sql.SQLException: Parameter number 4 is not an OUT parameter
; SQL []; Parameter number 4 is not an OUT parameter; nested exception is java.sql.SQLException: Parameter number 4 is not an OUT parameter