Lock wait timeout exceeded； try restarting transaction问题解决和复现

如果开启了事务，请检查以下问题

1.字段没有建索引，用它进行where查询(for update ) 或者 update，没有对应的索引树（B+树），就会进行全表扫描，锁表。

2.字段建立索引，但是索引失效（比如%号放左边），这时候也会全表扫描，锁表。

3.InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。
链接第七点

– 查询 正在执行的事务：
SELECT * FROM information_schema.INNODB_TRX;

– 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

– 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

for update的锁表（跟update同理，表示我要修改这条数据了）

参考:链接

InnoDB默认是行级别的锁，当有明确指定的主键时候，是行级锁。否则是表级别。

例子: 假设表student ，存在有id跟s_no、name、age等字段，id是主键，s_no有索引。

例1: (明确指定主键，并且有此记录，行级锁)

SELECT * FROM student WHERE id=1 FOR UPDATE;

SELECT * FROM student WHERE id=1 and name=‘张三’ FOR UPDATE;

SELECT * FROM student WHERE s_no=‘1001’ FOR UPDATE;

例2: (明确指定主键/索引，若查无此记录，无锁)

SELECT * FROM student WHERE id=-1 FOR UPDATE;

例3: (无主键/索引，表级锁)

SELECT * FROM student WHERE name=‘张三’ FOR UPDATE;

例4: (主键/索引不明确，表级锁)

SELECT * FROM student WHERE id<>‘3’ FOR UPDATE;
SELECT * FROM student WHERE id LIKE ‘3’ FOR UPDATE;

准备工作

1.创建测试数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(50) NOT NULL AUTO_INCREMENT COMMENT '自动递增id',
  `s_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '学员id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(20) NULL DEFAULT NULL COMMENT '年龄',
  `error` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '错误次数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 337 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (313, '1001', '张三', 18, NULL);
INSERT INTO `student` VALUES (314, '1002', '李四', 18, NULL);
INSERT INTO `student` VALUES (315, '1003', '王麻子', 18, NULL);
INSERT INTO `student` VALUES (316, '1004', '赵公子', 18, NULL);
INSERT INTO `student` VALUES (317, '1005', '赵公子1005', 18, NULL);
INSERT INTO `student` VALUES (318, '1006', '赵公子1006', 18, NULL);
INSERT INTO `student` VALUES (319, '1007', '赵公子1007', 18, NULL);
INSERT INTO `student` VALUES (320, '1008', '赵公子1008', 18, NULL);
INSERT INTO `student` VALUES (321, '1009', '赵公子1009', 18, NULL);
INSERT INTO `student` VALUES (322, '1010', '赵公子1010', 18, NULL);
INSERT INTO `student` VALUES (323, '1011', '赵公子1011', 18, NULL);
INSERT INTO `student` VALUES (324, '1012', '赵公子1012', 18, NULL);
INSERT INTO `student` VALUES (325, '1013', '赵公子1013', 18, NULL);
INSERT INTO `student` VALUES (326, '1014', '赵公子1014', 18, NULL);
INSERT INTO `student` VALUES (327, '1015', '赵公子1015', 18, NULL);
INSERT INTO `student` VALUES (328, '1016', '赵公子1016', 18, NULL);
INSERT INTO `student` VALUES (329, '1017', '赵公子1017', 18, NULL);
INSERT INTO `student` VALUES (330, '1018', '赵公子1018', 18, NULL);
INSERT INTO `student` VALUES (331, '1019', '赵公子1019', 18, NULL);
INSERT INTO `student` VALUES (332, '1020', '赵公子1020', 18, NULL);
INSERT INTO `student` VALUES (333, '1021', '赵公子1021', 18, NULL);
INSERT INTO `student` VALUES (334, '1022', '赵公子1022', 18, NULL);
INSERT INTO `student` VALUES (335, '1023', '赵公子1023', 18, NULL);
INSERT INTO `student` VALUES (336, '1024', '赵公子1024', 18, NULL);

SET FOREIGN_KEY_CHECKS = 1;

2.检查自动提交 每开一个新窗口都需要检查 注意！！！

-- 查看是否自动提交
select @@autocommit; 
-- 如果是1，那么运行命令： 设置为不开启自动提交 
set autocommit = 0;

开始复现

开启两个查询窗口，这边以navicat举例

窗口1

-- 查看详细状态  show engine innodb status/G;
show engine innodb status;

-- 查询 正在执行的事务：
SELECT * FROM information_schema.INNODB_TRX;

-- 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;


-- 开启事务
START TRANSACTION; -- 第一步 1
-- 关闭自动提交
set autocommit = 0;

-- update student set age=99 where id='4310';

select * from student where s_no='1001' for update; -- 第一步 1

-- commit; 

-- 添加学员字段索引
ALTER TABLE `xcall`.`student` ADD UNIQUE INDEX `no_index`(`s_no`);   -- 第四步4  执行完后重复前三步 会发现不会锁表了

-- 删除索引
ALTER TABLE `xcall`.`student` DROP INDEX `no_index`;

窗口2

-- 开启事务
START TRANSACTION; -- 第二步 2

-- update student set age=99 where id='4360'; -- update 和 for update  都是行级锁 

select * from student where s_no='1004' for update;-- 第二步 2   此时没有索引会锁整张表，正常情况下这段sql会出现等锁超时


-- select * from student where id='1001' for update; 


commit; -- 第三步3   提交事务，切换窗口1进行添加索引

总结

1.where条件后面最好跟带有索引的字段
2.where条件能用主键就用主键，因为主键自带 UNIQUE 索引

索引扩容

normal：表示普通索引，一般数据类型够用

unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique

full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

查看索引使用情况

show status like ‘Handler_read%’;

handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
handler_read_rnd_next:这个值越高，说明查询低效