描述:高并发的情况下,大量用户领取某个批次卡券导致上层依赖接口响应超时。
问题复现:
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的并发能力。但是会带来一个问题,即在资源争用紧张的情况下,数据跟新失败导致的同一个请求事物反复建立以及回滚的问题。