开启锁监控功能有两种方式:
一.创建innodb_lock_monitor表
二.设置全局属性innodb_status_output=1&innodb_status_output_locks=1
第一种方式:
开启:CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB;
关闭:DROP TABLE innodb_lock_monitor;
注意:在未拥有设置全局属性权限下也可以开启该功能。
第二种方式:
开启:set GLOBAL innodb_status_output=ON;set GLOBAL innodb_status_output_locks=ON;
关闭:set GLOBAL innodb_status_output_locks=OFF;
开启锁监控功能可以看到更多锁信息,例如一个更新sql加了哪些锁
测试一下:
1.创建表:在这里插入代码片
CREATE TABLE `test_user` (
`user_id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` char(10) DEFAULT NULL,
PRIMARY KEY (`user_id`),
KEY `index_user` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
2.插入数据:
INSERT INTO `test_user` VALUES ('1', 'a');
3.开启事务:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_user set name='a' where name='a';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
4.查看show engine innodb status\G;输出信息如下(体现了加锁顺序):
事务id:118184187,表级锁,锁模式:意向排他锁
TABLE LOCK table `test`.`test_user` trx id 118184187 lock mode IX
事务id:118184187,索引:辅助索引,锁模式:排他锁, 锁:行锁
RECORD LOCKS space id 6636 page no 4 n bits 72 index `index_user` of table `test`.`test_user` trx id 118184187 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 10; hex 61202020202020202020; asc a ;;
1: len 8; hex 8000000000000001; asc ;;
事务id:118184187,索引:主键,锁模式:排他锁,锁:不包含间隙的行锁
RECORD LOCKS space id 6636 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_user` trx id 118184187 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 0000070b213e; asc !>;;
2: len 7; hex 13000002710110; asc q ;;
3: len 10; hex 61202020202020202020; asc a ;;
事务id:118184187,索引:辅助索引,锁模式:排他锁,锁:行锁之前的间隙锁+行锁
RECORD LOCKS space id 6636 page no 4 n bits 72 index `index_user` of table `test`.`test_user` trx id 118184187 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 10; hex 63202020202020202020; asc c ;;
1: len 8; hex 8000000000000003; asc ;;
总结:
开启锁监控功能,可以看到一个sql,关于锁的具体信息以及加锁顺序,方便分析死锁。