CREATE TABLE `t_user_message` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '发信方id', `object_id` int unsigned NOT NULL DEFAULT '0' COMMENT '收信方id', `relation_id` int unsigned NOT NULL DEFAULT '0' COMMENT '关联id', `is_read` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否已读(0未读,1已读)', `sid` int unsigned NOT NULL DEFAULT '0' COMMENT '消息条数', `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '状态(0无效 1有效)', `content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '消息内容', `type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '类型(0文本,1语音,2图片,3视频,4表情,5分享链接)', `ext_json` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '扩展字段', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`) USING BTREE COMMENT '发信方id索引', KEY `idx_object_id` (`object_id`) USING BTREE COMMENT '收信方id索引', KEY `idx_relation_id` (`relation_id`) USING BTREE COMMENT '关联id索引' ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin COMMENT='用户消息表';按照锁的粒度来区分,可以分为以下两种:
select * from t_user_message where user_id=1001 for update;表锁
select * from t_user_message for update;排它锁和共享锁的区别
select * from t_user_message for update;共享锁
select * from t_user_message;这条sql在innodb中,默认是不会锁表,也不会锁行记录。如果你希望加上一把共享锁,那么可以尝试以下的这种写法:
select * from t_user_message lock in share mode;lock in share mode 和 for update使用起来有什么区别?
[查询2中发生错误] Lock wait timeout exceeded; try restarting transaction再来看看for update锁住的数据,对于其他会话的写操作有何影响。
|
lock in share mode 锁 |
for update 锁 |
多session读 |
不会堵塞,多个session可以读共同锁住的记录。 |
会堵塞,只能有一个session读取到锁住的记录,其他session的访问得等待。 |
多session写 |
会堵塞,任何写相关的操作都不行 |
会堵塞,任何写相关的操作都不行 |
CREATE TABLE `t_account` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `user_id` int DEFAULT NULL, `coin` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;然后在业务操作上,我们的账户扣款和增款逻辑上的设计如下:
//开启一个事务操作 set autocommit=0; begin; //如果账户存在,才进行update,如果账户不存在,就得先insert select * from t_account where user_id=111 lock in share mode; //这里我们假设账户是存在的,那么就直接选择打款入账 update t_account set coin=coin+100 where user_id=111; //记录到账户流水表中 INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`) VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034'); commit;这里为了保证将账户流水记录和打款两个操作保证一致性,得加入一个本地事务去修饰。但是这段代码中使用了一个lock in share mode的关键字,这个关键字是为了避免在并发的情况下,对账户记录进行读的过程中,有其他地方对账户的coin值进行写的修改。
//开启一个事务操作 set autocommit=0; //如果账户存在,才进行update,如果账户不存在,就得先insert select * from t_account where user_id=111; //这里我们假设账户是存在的,那么就直接选择打款入账 update t_account set coin=coin+100,version=version+1 where user_id=111; //记录到账户流水表中 INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`) VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034'); commit;2.去掉使用lock in share mode,使用乐观锁。
//堆代码 duidaima.com //开启一个事务操作 set autocommit=0; //如果账户存在,才进行update,如果账户不存在,就得先insert select * from t_account where user_id=111 and version=#{version}; //这里我们假设账户是存在的,那么就直接选择打款入账 update t_account set coin=coin+100,version=version+1 where user_id=111 and version=#{version}; //记录到账户流水表中 INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`) VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034'); commit;这里要注意,当同时两个会话针对同一行数据执行上述更新操作的时候,可能会导致同一行的记录被锁,所以我们在进行update的时候,可以用一个version字段去管理。但是这种设计,可能会导致一次更新失败,需要进行重试,因此并发量高的情况下,容易对MySQL造成较大的压力。
3. Next-Key Lock(临键锁):它是记录锁和间隙锁的结合,锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。next-key锁是InnoDB默认的锁,该锁也只会在隔离级别是RR或者以上的级别内存在。
CREATE TABLE `t_user_message` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '发信方id', `object_id` int unsigned NOT NULL DEFAULT '0' COMMENT '收信方id', `relation_id` int unsigned NOT NULL DEFAULT '0' COMMENT '关联id', `is_read` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否已读(0未读,1已读)', `sid` int unsigned NOT NULL DEFAULT '0' COMMENT '消息条数', `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '状态(0未审核 1审核失败 2审核通过)', `content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '消息内容', `type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '类型(0文本,1语音,2图片,3视频,4表情,5分享链接)', `ext_json` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '扩展字段', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`) USING BTREE COMMENT '发信方id索引', KEY `idx_object_id` (`object_id`) USING BTREE COMMENT '收信方id索引', KEY `idx_relation_id` (`relation_id`) USING BTREE COMMENT '关联id索引' ) ENGINE=InnoDB AUTO_INCREMENT=100015 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin COMMENT='用户消息表';假设我们的会话A执行了以下事务操作:
START TRANSACTION; //更新用户的消息状态,从未读变为已读 update t_user_message set is_read=1 where user_id=1003 and object_id=1004; //...中间有些别的业务操作 update t_user_message set is_read=1 where user_id=1001 and object_id=1002; commit;而此时我们的会话B在执行一个异步的消息是否合法的检测工作,具体操作如下:
set autocommit=0; START TRANSACTION; //定时任务更新用户的消息审核状态,从未审核变为审核通过 update t_user_message set status=2 where user_id=1001 and object_id=1002; //...中间有些别的业务操作 update t_user_message set status=2 where user_id=1003 and object_id=1004; commit;这两个事务如果并发执行,并发度高的情况下,可能会出现死锁情况,死锁产生的步骤如下图所示:
set SESSION transaction ISOLATION LEVEL REPEATABLE READ;如果你想确认当前的会话的事务隔离级别,那么可以使用以下命令去查询:
SELECT @@transaction_isolation; (mysql8.0语法) SELECT @@tx_isolation; (mysql5.7语法)还是针对我们的消息表t_user_message,在某些高并发场景下,如果使用可重复读的话,尤其是事务场景中,出现死锁的概率会加大。例如下边这个场景:
select * from information_schema.innodb_locks;查看等待锁的代码
select * from information_schema.innodb_lock_waits但是要注意,在mysql 8.0中查看死锁代码变了,如果继续用5.7的代码会提示报错
Unknown table ‘INNODB_LOCKS’ in information_schema所以在8.0使用以下代码
select * from performance_schema.data_locks;查看死锁等待时间
select * from performance_schema.data_lock_waits;