构造测试环境：
mysql> create table t(id int,name varchar(10));
Query OK, 0 rows affected (0.34 sec)

mysql> insert into t values(10,'mongodb'),(20,'oracle'),(30,'mysql');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t;
+------+---------+
| id | name |
+------+---------+
| 10 | mongodb |
| 20 | oracle |
| 30 | mysql |
+------+---------+
3 rows in set (0.00 sec)

场景1：READ-COMMITTED&无索引&等值查询条件
session 1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=10 lock in share mode;
+------+---------+
| id | name |
+------+---------+
| 10 | mongodb |
+------+---------+
1 row in set (0.13 sec)

session 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set name='redis' where id=20;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.07 sec)

session 1:
mysql> select * from t;
+------+---------+
| id | name |
+------+---------+
| 10 | mongodb |
| 20 | redis |
| 30 | mysql |
+------+---------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

场景2：REPEATABLE-READ&无索引&等值查询条件
session 1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=10 lock in share mode;
+----+---------+
| id | name |
+----+---------+
| 10 | mongodb |
+----+---------+
1 row in set (0.00 sec)

session 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set name='redis' where id=20;
...
...
note:操作被阻塞

场景3：REPEATABLE-READ&有索引&等值查询条件
session 1:
mysql> alter table t add primary key pk_id(id);
Query OK, 0 rows affected (1.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=10 lock in share mode;
+----+---------+
| id | name |
+----+---------+
| 10 | mongodb |
+----+---------+
1 row in set (0.00 sec)

session 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set name='redis' where id=20;
Query OK, 0 rows affected (0.05 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

session 1:
mysql> select * from t;
+----+---------+
| id | name |
+----+---------+
| 10 | mongodb |
| 20 | redis |
| 30 | mysql |
+----+---------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

总结：在无索引的情况下，事物隔离级别为REPEATABLE-READ,mysql在对表中某一行操作时,其他会话对其他行加了不兼容的锁时仍然会被阻塞，
因为加锁的粒度由于无索引导致整个表都被锁了。而事务隔离级别设置为READ-COMMITTED，或者在事务隔离级别为REPEATABLE-READ的前
提下给表中添加索引也可以避免该情况发生。

场景4：REPEATABLE-READ&有索引&范围查询条件
session 1:
mysql> insert into t values(40,'hadoop'),(50,'nginx'),(101,'keepalive'),(200,'balance');
Query OK, 4 rows affected (0.11 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from t;
+-----+-----------+
| id | name |
+-----+-----------+
| 10 | mongodb |
| 20 | redis |
| 30 | mysql |
| 40 | hadoop |
| 50 | nginx |
| 101 | keepalive |
| 200 | balance |
+-----+-----------+
7 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id >15 and id <35 lock in share mode;
+----+-------+
| id | name |
+----+-------+
| 20 | redis |
| 30 | mysql |
+----+-------+
2 rows in set (0.00 sec)

session 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set name='hadoop2' where id=40;
...
...
note:操作被阻塞

场景5：READ-COMMITTED&有索引&范围查询条件
session 1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id >15 and id <35 lock in share mode;
+----+-------+
| id | name |
+----+-------+
| 20 | redis |
| 30 | mysql |
+----+-------+
2 rows in set (0.00 sec)

session 2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set name='hadoop2' where id=40;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> insert into t values(37,'hive');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t values(34,'memcache');
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

总结：在有索引的情况下，对于范围查询条件的sql来说，事物隔离级别为REPEATABLE-READ的前提下会出现next-key锁阻塞，
这个现象在READ-COMMITTED时不存在；即使是在无索引的前提下，事物隔离级别为READ-COMMITTED时也不会出现锁阻塞。

ITPub博客

MySQL入门学习之——innodb lock

最新文章