mysql locking_【MySQL】InnoDB Locking（Innodb锁之Shared and Exclusive Locks）

共享锁和排它锁

看我翻译过来的文字，就可以很好区分。共享锁可以共同持有，排它锁只有一个事务可以占有。

对行数据获取到共享锁的事务，可以对事务进行读操作，其他事务也可以同时获取此行数据的共享锁，并且进行读操作。如果其它事务要获取排它锁，不会立即获得，必须等待共享锁释放后才可以

对行数据获取到排它锁的事务，可以对事务进行变更和读取操作，其他事务不可以获取此行数据的任何锁(共享锁和排它锁)，必须等待次锁释放后，才可以获取

验证一

transcation1：

(root@localhost)xt> begin;

Query OK, 0 rows affected (0.00 sec)

(root@localhost)xt> select * from test_lock where id = 2 lock in share mode;+----+------+----------+

| id | name | phone |

+----+------+----------+

| 2 | Ava | 12345678 |

+----+------+----------+

1 row in set (0.00 sec)

transcation2：

(root@localhost)xt> begin;

Query OK, 0 rows affected (0.00 sec)

(root@localhost)xt> select * from test_lock where id = 2 lock in share mode;+----+------+----------+

| id | name | phone |

+----+------+----------+

| 2 | Ava | 12345678 |

+----+------+----------+

1 row in set (0.00 sec)

另外一个窗口查看：

(root@localhost)performance_schema> select * from data_locks;+--------+-------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |

+--------+-------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

| INNODB | 421125896362744:1400 | 421125896362744 | 801 | 19 | xt | test_lock | NULL | NULL | NULL | 139650794563608 | TABLE | IS | GRANTED | NULL |

| INNODB | 421125896362744:343:4:3 | 421125896362744 | 801 | 19 | xt | test_lock | NULL | NULL | PRIMARY | 139650794560568 | RECORD | S | GRANTED | 2 |

| INNODB | 421125896364584:1400 | 421125896364584 | 802 | 17 | xt | test_lock | NULL | NULL | NULL | 139650794575544 | TABLE | IS | GRANTED | NULL |

| INNODB | 421125896364584:343:4:3 | 421125896364584 | 802 | 17 | xt | test_lock | NULL | NULL | PRIMARY | 139650794572664 | RECORD | S | GRANTED | 2 |

+--------+-------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

4 rows in set (0.00 sec)

结论：获取共享锁的事务，其他事务可以理解获取共享锁。多个事务可以共同持有共享锁。

验证二

transcation1：

(root@localhost)xt> commit

transcation2：

保持不变

另外一个窗口查看：

(root@localhost)performance_schema> select * from data_locks;+--------+-------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |

+--------+-------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

| INNODB | 421125896364584:1400 | 421125896364584 | 802 | 17 | xt | test_lock | NULL | NULL | NULL | 139650794575544 | TABLE | IS | GRANTED | NULL |

| INNODB | 421125896364584:343:4:3 | 421125896364584 | 802 | 17 | xt | test_lock | NULL | NULL | PRIMARY | 139650794572664 | RECORD | S | GRANTED | 2 |

+--------+-------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

2 rows in set (0.00 sec)

transcation1：

(root@localhost)xt> begin;

Query OK, 0 rows affected (0.01 sec)

(root@localhost)xt> update test_lock set name='Andy' where id = 2 ;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

另外一个窗口查看：

(root@localhost)performance_schema> select * from data_locks;+--------+-------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |

+--------+-------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

| INNODB | 76101:1400 | 76101 | 801 | 30 | xt | test_lock | NULL | NULL | NULL | 139650794563608 | TABLE | IX | GRANTED | NULL |

| INNODB | 421125896364584:1400 | 421125896364584 | 802 | 17 | xt | test_lock | NULL | NULL | NULL | 139650794575544 | TABLE | IS | GRANTED | NULL |

| INNODB | 421125896364584:343:4:3 | 421125896364584 | 802 | 17 | xt | test_lock | NULL | NULL | PRIMARY | 139650794572664 | RECORD | S | GRANTED | 2 |

+--------+-------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

结论：获取共享锁的事务，如果这个时候有排它锁请求的话，必须释放共享锁才可以获取。

验证三

transcation1 和 transcation2 均为新开事务，所有的锁均释放完毕

另外一个窗口查看：(检查所是否释放完毕)

(root@localhost)performance_schema> select * from data_locks;

Empty set (0.00 sec)

transcation1：

(root@localhost)xt> begin;

Query OK, 0 rows affected (0.00 sec)

(root@localhost)xt> update test_lock set name='Andy' where id = 2 ;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

transcation2：

(root@localhost)xt> begin;

Query OK, 0 rows affected (0.00 sec)

(root@localhost)xt> select * from test_lock where id = 2 lock in share mode;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

(root@localhost)xt> update test_lock set name='Andy' where id = 2 ;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

另外一个窗口查看：

(root@localhost)performance_schema> select * from data_locks;+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |

+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

| INNODB | 76174:1400 | 76174 | 802 | 25 | xt | test_lock | NULL | NULL | NULL | 139650794575544 | TABLE | IS | GRANTED | NULL |

| INNODB | 76174:1400 | 76174 | 802 | 26 | xt | test_lock | NULL | NULL | NULL | 139650794575632 | TABLE | IX | GRANTED | NULL |

| INNODB | 76156:1400 | 76156 | 801 | 37 | xt | test_lock | NULL | NULL | NULL | 139650794563608 | TABLE | IX | GRANTED | NULL |

| INNODB | 76156:343:4:4 | 76156 | 801 | 37 | xt | test_lock | NULL | NULL | PRIMARY | 139650794560568 | RECORD | X | GRANTED | 2 |

+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

结论：获取到排它锁的事务，未释放之前，其他事务都不能获取到共享锁和排它锁，只能获取到IS和IX锁。。这个后面会进行对应的解释。