论高并发下悲观锁导致的响应超时问题

描述：高并发的情况下，大量用户领取某个批次卡券导致上层依赖接口响应超时。

问题复现:

       1.创建卡券表sql大致如下：

      CREATE TABLE `coupon_group_a` (
            `id` bigint(20) NOT NULL,
           `batch_id` int(11) NOT NULL,
           `name` varchar(32) DEFAULT '',
           `status` varchar(32) DEFAULT '' COMMENT '券码状态:券码状态：INIT（初始），USED（被使用），OCCUPY（被占    用）,CANCLE（取消）',
           PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      2.创建一个存储过程：

      CREATE DEFINER=`root`@`%` PROCEDURE `insert_coupon_group_a`(IN max_num INT(10))
      BEGIN  
      DECLARE i INT DEFAULT 0;   
           #set autocommit =0 把autocommit设置成0  
           SET autocommit = 1;    
           REPEAT  
                 SET i = i + 1;  
                 INSERT INTO coupon_group_a(id, batch_id , name,status) VALUES (1+i,1001,CONCAT('coupon',i),'INIT');  
           UNTIL i = max_num  
           END REPEAT;  
           COMMIT;  
      END

     3.调用存储过程生成100万调数据模拟大量数据：call insert_coupon_group_a(1000000)；

     4.模拟客户端A连接主要操作：

     BEGIN;
     update coupon_group_b set `status`='OCCUPY' where `status`='INIT' and batch_id=1001 limit 1;
     select sleep(10);
     COMMIT;

     

    5.模拟客户端B连接主要操作：

    BEGIN;
    update coupon_group_b set `status`='OCCUPY' where `status`='INIT' and batch_id=1001 limit 1;
    COMMIT;

结论：由于sql：update coupon_group_b set `status`='OCCUPY' where `status`='INIT' and batch_id=1001 limit 1，采用悲观锁的形式去抢占卡券，导致数据库慢的原因主要如下：

        (1).单表数据量巨大，有100万，where后面条件查询相对较慢；

        (2).由于业务中开启了事物，mysql会把更新语句关联的sql记录全部上锁，知道事物释放才会解锁；即：对于同一批次的卡券，在同一个事务中可以认为是串行执行，这样的话大大减弱了数据库的并行能力。

优化：针对（2）可以考虑使用乐观锁的机制去做优化，这样的话一个事物只会锁住单条记录，大大提高了mysql的并发能力。但是会带来一个问题，即在资源争用紧张的情况下，数据跟新失败导致的同一个请求事物反复建立以及回滚的问题。