共享锁和排它锁
看我翻译过来的文字,就可以很好区分。共享锁可以共同持有,排它锁只有一个事务可以占有。
对行数据获取到共享锁的事务,可以对事务进行读操作,其他事务也可以同时获取此行数据的共享锁,并且进行读操作。如果其它事务要获取排它锁,不会立即获得,必须等待共享锁释放后才可以
对行数据获取到排它锁的事务,可以对事务进行变更和读取操作,其他事务不可以获取此行数据的任何锁(共享锁和排它锁),必须等待次锁释放后,才可以获取
验证一
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锁。。这个后面会进行对应的解释。