InnoDB引擎的行锁

InnoDB引擎特点简述

• 支持事务
  
• 支持外键
• 必须要有主键，聚焦索引
• 不支持全文检索
• 使用行锁

行锁特点

　　支持事务，加锁开销大，加锁慢，会出现死锁，锁的粒度小，并发下等待锁的概率较低，所以支持高并发。

手动事务语法

手动测试行锁需要首先关闭自动提交，每个会话都需要关闭自动提交

mysql> SHOW VARIABLES LIKE 'autocommit';    #查看
mysql> SET autocommit = 0;

 

中间执行SQL

最后执行完了需要进行提交

mysql> COMMIT;

最佳实践

步骤	Session1	Session2
1	可以查询innodb_lock表数据	可以查询innodb_lock表数据
2	可以增删改innodb_lock表数据	可以查询旧数据，增删改innodb_lock表数据阻塞
3	commit	commit同时阻塞的增删改成功
4	可以增删改innodb_lock表数据	增删改innodb_lock表与Session1不同行数据也阻塞
5	commit	commit同时阻塞的增删改成功
6	没能获取session2更新的数据	commit
7	可以查询innodb_lock表更新后数据

 

 

 

 

 

 

 

 

这里有几个问题：

• 这两个session，谁先进行的增删改，谁就先拿到锁。但是在查询的时候，如果session1进行了一次SELECT操作，而session2提交之后，session1再一次的SELECT是无法获取session2更新的值的，必须session1进行提交后才可以更新数据。
• 在第3步，session2会将session1更新的数据覆盖掉
• 在第4步，session2修改的与session1不同的行也会阻塞，在这里其实session1的锁是表锁，只有用上索引之后才能变为行锁。

针对给表加索引，让表锁变行锁的实践

表名：innodb_lock

 

步骤	Session1	Session2
1	CREATE INDEX idx_id ON innodb_lock(id);
2	COMMIT;
3	UPDATE innodb_lock SET color = 'pink' WHERE id = 1;	UPDATE innodb_lock SET color = 'yellow' WHERE id = 2;
4	COMMIT;	COMMIT;

 

 

 

 

 

在这里两个session可以同时更新数据，实现行锁。我们可以看出这个索引落在WHERE语句参数的ID上，假如没有WHERE 条件并且没有索引是否也可成功？

步骤	Session1	Session2
1	DROP INDEX idx_id ON innodb_lock;
2	COMMIT;
3	UPDATE innodb_lock SET color = 'red' WHERE id = 1;	UPDATE innodb_lock SET color = 'black';
4	COMMIT;	COMMIT;

 

 

 

 

 

在第3行session2发生阻塞，因为没有索引则会发生阻塞。

 手动锁定记录

这种锁方式也是MySQL实现悲观锁的方式

mysql> BEGIN;
mysql> SELECT ... FOR UPDATE;

在中间进行数据更新

mysql> COMMIT;

最后进行提交。

最佳实践

1、还是innodb_lock这张表和数据，自动提交开启的情况和没有索引的实践手动锁定记录。

步骤	Session1	Session2
1	BEGIN;
2	SELECT * FROM innodb_lock WHERE id = 1 FOR UPDATE;	UPDATE innodb_lock SET color = 'orange' WHERE id = 1; #阻塞
3	UPDATE innodb_lock SET color = 'pink' WHERE id = 1;
4	COMMIT;	#阻塞释放，执行更新，数据被覆盖为orange

 

 

 

 

 

2、上一个测试两个session修改的是同一条数据，如果修改不一样的数据，是否还会发生阻塞

步骤	Session1	Session2
1	BEGIN;
2	SELECT * FROM innodb_lock WHERE id = 1 FOR UPDATE;	UPDATE innodb_lock SET color = 'yellow' WHERE id = 2; #阻塞
3	UPDATE innodb_lock SET color = 'red' WHERE id = 1;
4	COMMIT;	#阻塞释放，执行更新

 

 

 

 

 

3、这里可以看到session1对数据表进行了锁表，那我们给id加上索引是否就不会阻塞

步骤	Session1	Session2
1	CREATE INDEX idx_id ON innodb_lock(id);
2	BEGIN;
3	SELECT * FROM innodb_lock WHERE id = 1 FOR UPDATE;	UPDATE innodb_lock SET color = 'blue' WHERE id = 2; #未阻塞
4	UPDATE innodb_lock SET color = 'pink' WHERE id = 1;
5	COMMIT;

 

 

 

 

 

 

4、session2未发生阻塞，那么假如锁定的行与更新的行不是一个行那session2能更新哪行呢

步骤	Session1	Session2
1	BEGIN;
2	SELECT * FROM innodb_lock WHERE id = 1 FOR UPDATE;
3	UPDATE innodb_lock SET color = 'yellow' WHERE id = 2;	UPDATE innodb_lock SET color = 'red' WHERE id = 1;　　#阻塞
4		UPDATE innodb_lock SET color = 'orange' WHERE id = 2;　　#阻塞
5		UPDATE innodb_lock SET color = 'grey' WHERE id = 3;　　#未阻塞
6	COMMIT;

 

 

 

 

 

 

 

5、我们发现id为1和2的两行都被锁住了，如果这样的话是不是说明只要是锁定或更新的行都会被锁

步骤	Session1	Session2
1	INSERT INTO innodb_lock VALUES(4, 'red'),(5, 'orange');
1	BEGIN;
2	SELECT * FROM innodb_lock WHERE id < 3 FOR UPDATE;
3	UPDATE innodb_lock SET color = 'black' WHERE id > 3;	UPDATE innodb_lock SET color = 'black' WHERE id = 1;　　#阻塞
4		UPDATE innodb_lock SET color = 'black' WHERE id = 2;　　#阻塞
5		UPDATE innodb_lock SET color = 'black' WHERE id = 3;　　#阻塞
6		UPDATE innodb_lock SET color = 'black' WHERE id = 4;　　#阻塞
7		UPDATE innodb_lock SET color = 'black' WHERE id = 5;　　#阻塞
8	COMMIT;

 

 

 

 

 

 

 

 

 

 

6、不会吧，全部阻塞了，id为3的数据明明没在锁住和更新的数据里，这是为什么呢？我们再试一组数据。

步骤	Session1	Session2
1	INSERT INTO innodb_lock VALUES(6, 'purple'),(7, 'white');
2	BEGIN;
3	SELECT * FROM innodb_lock WHERE id < 3 FOR UPDATE;
4	UPDATE innodb_lock SET color = 'black' WHERE id > 5;	UPDATE innodb_lock SET color = 'black' WHERE id = 2;　　#阻塞
5		UPDATE innodb_lock SET color = 'black' WHERE id = 3;　　#阻塞
6		UPDATE innodb_lock SET color = 'black' WHERE id = 4;　　#未阻塞
7		UPDATE innodb_lock SET color = 'black' WHERE id = 5;　　#未阻塞
8		UPDATE innodb_lock SET color = 'black' WHERE id = 6;　　#阻塞
9		INSERT INTO innodb_lock VALUES(8, 'test');　　#阻塞
10	COMMIT;

 

 

 

 

 

 

 

 

 

 

我再添加两条数据，我知道的颜色单词就这些了，多一个也想不起来了。

我嘞个去，结果让人匪夷所思，小于号是后妈养的吗？为什么3就阻塞而5就不阻塞。

好吧，算你厉害，大家还是记住这个结果吧。

这里这种范围的锁定就是传说中的间隙锁，他有一种特点就是只要在范围之内的数据全部被锁住，不管当前是否存在。

7、如果有两张innodb的表，两个session锁住各一张表，然后再去更新对方的那张表会怎样呢？

步骤	Session1	Session2
1	BEGIN;	BEGIN;
2	SELECT * FROM innodb_lock WHERE id = 1 FOR UPDATE;	SELECT * FROM innodb_test WHERE id = 1 FOR UPDATE;
3	UPDATE innodb_test SET color = 'pink' WHERE id = 1;　　#阻塞	UPDATE innodb_lock SET color = 'pink' WHERE id = 1;　　#阻塞
4	COMMIT;

 

 

 

 

 

在第3行都发生了阻塞，而在Session2的第3行出现以下错误

这里出现了传说中的死锁，就是两个session吃着自己碗里的还看着对方碗里的，双方还都是倔脾气，就在这里杠上了，谁也不服谁。

查看行锁的信息

• Innodb_row_lock_current_waits：当前正在等待的数量
• Innodb_row_lock_time：从启动到现在锁定的总时长，单位ms
• Innodb_row_lock_time_avg：锁等待的平均时长，单位ms
• Innodb_row_lock_time_max：等待锁时间最长的一个时间，单位ms
• Innodb_row_lock_waits：总共的等待次数

---

本文索引关键字：

间隙锁：http://www.cnblogs.com/huanStephen/p/8076172.html#c_lock

悲观锁：http://www.cnblogs.com/huanStephen/p/8076172.html#p_lock

死锁：http://www.cnblogs.com/huanStephen/p/8076172.html#d_lock

欢迎大家索引！

转载于:https://www.cnblogs.com/huanStephen/p/8076172.html