構造測試環境：
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時也不會出現鎖阻塞。

MySQL入門學習之——innodb lock

相關文章