文档课题：MySQL锁的相关测试.
数据库：MySQL 8.0.27
1、知识概述
锁是计算机协调多个进程或线程并发访问某一资源的机制，在数据库中除系统资源(CPU、RAM、I/O)的争用以外，数据也是一种供许多用户共享的资源.如何保证数据并发访问的一致性、有效性是所有数据库必须解决的问题，锁冲突也是影响数据库并发访问性能的一个重要因素.锁对于数据库而言显得尤其重要，也更加复杂.MySQL按照锁的粒度分为以下三类：
全局锁：锁定数据库中的所有表
表级锁：每次操作锁住整张表
行级锁：每次操作锁住对应的行数据
2、全局锁
2.1、相关概念
全局锁是对整个数据库实例加锁，加锁后整个实例处于只读状态，后续的DML、DDL语句以及更新操作的事务提交语句都将被阻塞.其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性.
2.2、案例演示
--客户端1
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
--客户端2
mysql> use fruitsDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| o2 | 103 | coconut | 9.20 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
14 rows in set (0.00 sec)
mysql> update fruits set f_price=5.3 where f_id=a1;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
[mysql@mysql-leo-master ~]$ mysqldump -uroot -pmysql_4U fruitsDB > /tmp/fruitsDB.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
--逻辑备份完成后，解开全局锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> update fruits set f_price=5.3 where f_id='a1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
2.3、全局锁特点
数据库中加全局锁，是影响比较大的操作，主要存在以下问题：
1)、若在主库上备份，则在备份期间都不能执行更新，业务基本停摆；
2)、若在从库上备份，则在备份期间从库不能执行主库同步过来的二进制日志(binlog),会导致主从延迟.
说明：InnoDB引擎中可以在备份时加参数--single-transaction来完成不加锁的一致性数据备份.
3、表级锁
3.1、理论知识
每次操作锁住整张表，锁定力度大，发生锁冲突的概率最高，并发度最低.应用在MyISAM、InnoDB、BDB等存储引擎中.表级锁主要分为以下三类：
1）、表锁
2）、元数据锁MDL(meta data lock)
3）、意向锁
表锁又分为两类：
a、 表共享读锁(read lock)
b、表独占写锁(write lock)
语法：
1）、加锁：lock tables 表名… read/write;
2）、释放锁：unlock tables 或者 客户端断开连接
3.2、表锁
3.2.1、表共享读锁演示
--客户端1
mysql> lock tables fruits read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.30 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| o2 | 103 | coconut | 9.20 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
14 rows in set (0.00 sec)
mysql> update fruits set f_price=6 where f_id='a1';
ERROR 1099 (HY000): Table 'fruits' was locked with a READ lock and can't be updated
--客户端2
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 5.30 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| o2 | 103 | coconut | 9.20 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
14 rows in set (0.00 sec)
mysql> update fruits set f_price=6 where f_id='a1';
(hang住)
说明：此处客户端2直接hang住.
--查具体被锁表的信息
mysql> show processlist;
+----+------+-----------------------+----------+------------------+-------+---------------------------------------------------------------+---------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------------------+----------+------------------+-------+---------------------------------------------------------------+---------------------------------------------+-----------+---------------+
| 2 | repl | 192.168.133.112:54256 | NULL | Binlog Dump GTID | 10558 | Master has sent all binlog to slave; waiting for more updates | NULL | 0 | 0 |
| 3 | root | localhost | fruitsDB | Sleep | 147 | | NULL | 0 | 0 |
| 6 | root | localhost | fruitsDB | Query | 74 | Waiting for table metadata lock | update fruits set f_price=6 where f_id='a1' | 0 | 0 |
| 7 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |
+----+------+-----------------------+----------+------------------+-------+---------------------------------------------------------------+---------------------------------------------+-----------+---------------+
4 rows in set (0.00 sec)
mysql> show open tables;
+--------------------+-------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+-------------------+--------+-------------+
| booksDB | tb | 0 | 0 |
| booksDB | test03 | 0 | 0 |
| booksDB | article | 0 | 0 |
| booksDB | demo_table | 0 | 0 |
| booksDB | tbl_user | 0 | 0 |
| fruitsDB | fruitsbak | 0 | 0 |
| fruitsDB | fruits | 1 | 0 |
| booksDB | book | 0 | 0 |
| booksDB | phone | 0 | 0 |
| performance_schema | session_variables | 0 | 0 |
| booksDB | class | 0 | 0 |
| booksDB | staffs | 0 | 0 |
+--------------------+-------------------+--------+-------------+
12 rows in set (0.00 sec)
mysql> show open tables where in_use>0;
+----------+--------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+--------+--------+-------------+
| fruitsDB | fruits | 1 | 0 |
+----------+--------+--------+-------------+
1 row in set (0.00 sec)
mysql> show full processlist;
+----+------+-----------------------+----------+------------------+-------+---------------------------------------------------------------+---------------------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------------------+----------+------------------+-------+---------------------------------------------------------------+---------------------------------------------+-----------+---------------+
| 2 | repl | 192.168.133.112:54256 | NULL | Binlog Dump GTID | 11021 | Master has sent all binlog to slave; waiting for more updates | NULL | 0 | 0 |
| 3 | root | localhost | fruitsDB | Sleep | 610 | | NULL | 0 | 0 |
| 6 | root | localhost | fruitsDB | Query | 537 | Waiting for table metadata lock | update fruits set f_price=6 where f_id='a1' | 0 | 0 |
| 7 | root | localhost | NULL | Query | 0 | starting | show full processlist | 0 | 0 |
+----+------+-----------------------+----------+------------------+-------+---------------------------------------------------------------+---------------------------------------------+-----------+---------------+
4 rows in set (0.00 sec)
说明：此时可以看到表fruits被锁住.
--客户端1解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
--客户端2成功update
mysql> update fruits set f_price=6 where f_id='a1';
Query OK, 1 row affected (12 min 48.56 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3.2.2、表独占写锁演示
--客户端1
mysql> lock tables fruits write;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 6.00 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| o2 | 103 | coconut | 9.20 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
14 rows in set (0.00 sec)
mysql> update fruits set f_price=6.5 where f_id='a1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--客户端2
mysql> use fruitsDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from fruits;
(hang住)
说明：此时客户端2执行查询语句hang住.
mysql> update fruits set f_price=6.2 where f_id='a1';
(hang住)
说明：客户端2执行更新操作也被hang住.
查锁信息
mysql> show processlist;
+----+------+-----------------------+----------+------------------+-------+---------------------------------------------------------------+----------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------------------+----------+------------------+-------+---------------------------------------------------------------+----------------------+-----------+---------------+
| 2 | repl | 192.168.133.112:54256 | NULL | Binlog Dump GTID | 11951 | Master has sent all binlog to slave; waiting for more updates | NULL | 0 | 0 |
| 3 | root | localhost | fruitsDB | Query | 0 | starting | show processlist | 0 | 0 |
| 7 | root | localhost | NULL | Sleep | 863 | | NULL | 0 | 0 |
| 9 | root | localhost | fruitsDB | Query | 64 | Waiting for table metadata lock | select * from fruits | 0 | 0 |
+----+------+-----------------------+----------+------------------+-------+---------------------------------------------------------------+----------------------+-----------+---------------+
4 rows in set (0.00 sec)
mysql> show open tables where in_use>0;
+----------+--------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+--------+--------+-------------+
| fruitsDB | fruits | 1 | 0 |
+----------+--------+--------+-------------+
1 row in set (0.00 sec)
--释放锁：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
说明：表独占写锁会使select语句hang住.
3.3、元数据锁
3.3.1、理论知识
元数据锁MDL(meta data lock)加锁过程是系统自动控制的.在访问表时会自动添加，MDL锁主要作用是维护表元数据一致性，在表上有事务时，不可以对元数据进行写入操作，主要作用是为避免DML与DDL冲突，保证读写正确性.MySQL从5.5引入MDL概念，当对表进行增删改查时，加MDL读锁(共享)；当对表结构进行变更时，加MDL写锁(排他).
3.3.2、share_read与share_write兼容演示
--客户端1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 6.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| o2 | 103 | coconut | 9.20 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
14 rows in set (0.00 sec)
说明：客户端1产生share_read类型锁.
--客户端2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 6.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3服务器托管网.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| o2 | 103 | coconut | 9.20 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
14 rows in set (0.00 sec)
mysql> update fruits set f_price=6.5 where f_id='a1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
说明：客户端2产生share_write类型锁.share_read与share_write类型锁兼容，因此客户端2成功执行update语句.
--客户端1解锁
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端2解锁
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
3.3.3、share_read与exclusive互斥演示
--客户端1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name | f_price |
+------+------+------------+---------+
| a1 | 101 | apple | 6.50 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| o2 | 103 | coconut | 9.20 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+------------+---------+
14 rows in set (0.00 sec)
--客户端2
mysql> desc fruits;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| f_id | char(10) | NO | PRI | NULL | |
| s_id | int(11) | NO | | NULL | |
| f_name | char(255) | NO | | NULL | |
| f_price | decimal(8,2) | NO | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table fruits add column f_attribute varchar(100);
(hang住)
说明：客户端2 hang住.因为客户端1执行select * from fruits后会自动为fruits表加上shared_read锁.当客户端2执行alter table fruits add column时自动生成排他锁，而排他锁与其它MDL都互斥，因此就会出现阻塞现象，直到客户端1提交事务.
--客户端1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端2在客户端1提交后成功添加列.
mysql> alter table fruits add column f_attribute varchar(100);
Query OK, 0 rows affected (7 min 42.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
--查元数据锁
(root@Master) [(none)] 00:12:16> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+-----------------+---------------------+---------------+
| object_type | object_schema | object_name | lock_type | lock_duration |
+-------------+--------------------+-----------------+---------------------+---------------+
| TABLE | fruitsDB | fruits | SHARED_READ | TRANSACTION |
| GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | STATEMENT |
| BACKUP LOCK | NULL | NULL | INTENTION_EXCLUSIVE | TRANSACTION |
| SCHEMA | fruitsDB | NULL | INTENTION_EXCLUSIVE | TRANSACTION |
| TABLE | fruitsDB | fruits | SHARED_UPGRADABLE | TRANSACTION |
| TABLESPACE | NULL | fruitsDB/fruits | INTENTION_EXCLUSIVE | TRANSACTION |
| TABLE | fruitsDB | #sql-930_2e | EXCLUSIVE | STATEMENT |
| TABLE | fruitsDB | fruits | EXCLUSIVE | TRANSACTION |
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION |
+-------------+--------------------+-----------------+---------------------+---------------+
9 rows in set (0.00 sec)
(root@Master) [fruitsDB] 00:13:49> commit;
Query OK, 0 rows affected (0.00 sec)
说明：经测试MySQL 5.7.21查询未显示结果.
--客户端1
(root@Master) [fruitsDB] 00:13:49> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端2
(root@Master) [fruitsDB] 00:19:00> commit;
Query OK, 0 rows affected (0.00 sec)
--再次查询元数据锁
(root@Master) [(none)] 00:12:58> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+----------------+-------------+---------------+
| object_type | object_schema | object_name | lock_type | lock_duration |
+-------------+--------------------+----------------+-------------+---------------+
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION |
+-------------+--------------------+----------------+-------------+---------------+
1 row in set (0.00 sec)
3.3.4、share_write与exclusive互斥演示
--客户端1
(root@Master) [fruitsDB] 00:19:00> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 00:22:12> update fruits set f_price=6.5 where f_id='a1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(root@Master) [fruitsDB] 00:22:48> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+----------------+--------------+---------------+
| object_type | object_schema | object_name | lock_type | lock_duration |
+-------------+--------------------+----------------+--------------+---------------+
| TABLE | fruitsDB | fruits | SHARED_WRITE | TRANSACTION |
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION |
+-------------+--------------------+----------------+--------------+---------------+
2 rows in set (0.00 sec)
--客户端2
(root@Master) [fruitsDB] 00:19:05> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 00:24:01> alter table fruits drop column f_attribute;
(hang住)
说明：share_write与exclusive互斥，此时客户端2 hang住.
--客户端3查询锁信息
(root@Master) [(none)] 00:22:53> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+-----------------+---------------------+---------------+
| object_type | object_schema | object_name | lock_type | lock_duration |
+-------------+--------------------+-----------------+---------------------+---------------+
| TABLESPACE | NULL | fruitsDB/fruits | INTENTION_EXCLUSIVE | TRANSACTION |
| TABLE | fruitsDB | #sql-930_2e | EXCLUSIVE | STATEMENT |
| TABLE | fruitsDB | fruits | EXCLUSIVE | TRANSACTION |
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION |
| TABLE | fruitsDB | fruits | SHARED_WRITE | TRANSACTION |
| TABLE | performance_schema | metadata_locks | SHARED_READ | TRANSACTION |
| GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | STATEMENT |
| BACKUP LOCK | NULL | NULL | INTENTION_EXCLUSIVE | TRANSACTION |
| SCHEMA | fruitsDB | NULL | INTENTION_EXCLUSIVE | TRANSACTION |
| TABLE | fruitsDB | fruits | SHARED_UPGRADABLE | TRANSACTION |
+-------------+--------------------+-----------------+---------------------+---------------+
10 rows in set (0.00 sec)
3.4、意向锁
3.4.1、理论知识
为解决在执行DML时行锁与表锁的冲突，InnoDB引入意向锁，使表锁不用检查每行数据是否加行锁.原理：当开启事务，客户端执行update语句后，事务会添加行锁，同时会对整张表添加意向锁，若此时客户端2要对该表加表锁，那么它会先去检查该表是否存在意向锁，通过意向锁的情况来判断能不能在该表上加表锁，若意向锁与当前所加的表锁兼容，那么表锁能成功添加，反之则出现阻塞，直到客户端1提交事务.因此当添加表锁时，就不用去判断表的每行是否有行锁，而是通过该表是否有意向锁以及意向锁的类型决定表锁是否能添加成功.
意向锁分为两种：
1） 意向共享锁(IS)：由语句select … lock in share mode添加，与表共享读锁(read)兼容，与表独占写锁(write)互斥；
2） 意向排他锁(IX)：由insert、update、delete、select …… for update添加，与表共享读锁(read)及表独占写锁(write)都互斥，意向锁之间不会互斥.
3.4.2、意向共享锁(IS)演示
--客户端1
(root@Master) [fruitsDB] 10:29:55> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 10:31:27> select * from fruits where f_id='a1' lock in share mode;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| a1 | 101 | apple | 6.00 |
+------+------+--------+---------+
1 row in set (0.00 sec)
说明：添加上"lock in share mode"后会对该行加上共享锁，同时为fruits表加上意向共享锁
--查锁的情况
(root@Master) [(none)] 10:30:00> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+--------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+---------------+--------------+
| fruitsDB | fruits | NULL | TABLE | IS | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | S,REC_NOT_GAP | 'a1 ' |
+---------------+-------------+------------+-----------+---------------+--------------+
2 rows in set (0.00 sec)
说明："RECORD"代表行锁，同时为fruits表添加"IS"意向共享锁.
--客户端2验证是否能成功对表添加表锁共享锁
(root@Master) [fruitsDB] 10:29:46> use fruitsDB;
Database changed
(root@Master) [fruitsDB] 10:32:23> lock tables fruits read;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 10:32:34> unlock tables;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 10:38:46> lock tables fruits write;
(此处hang住)
结论：如上所示，客户端2可以对表添加表锁共享锁，但是不能添加表锁排他锁.
--客户端1提交事务
(root@Master) [fruitsDB] 10:31:42> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端2表独占写锁成功添加
(root@Master) [fruitsDB] 10:38:46> lock tables fruits write;
Query OK, 0 rows affected (1 min 1.43 sec)
--客户端3查看锁情况
(root@Master) [(none)] 10:40:50> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)
--客户端2解锁
(root@Master) [fruitsDB] 10:43:39> unlock tables;
Query OK, 0 rows affected (0.00 sec)
--客户端3查看锁情况
(root@Master) [(none)] 10:44:12> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)
3.4.3、意向排他锁(IX)演示
--以下演示关于意向排他锁与表锁的兼容情况，客户端1
(root@Master) [fruitsDB] 10:42:58> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 10:48:45> update fruits set f_price=6.5 where f_id='a1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
说明：客户端1执行的sql语句此时会为该行添加行锁，同时也会为该表添加意向锁，此时的意向锁为意向排他锁.
--客户端3查锁情况
(root@Master) [(none)] 10:48:49> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+--------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+---------------+--------------+
| fruitsDB | fruits | NULL | TABLE | IX | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | X,REC_NOT_GAP | 'a1 ' |
+---------------+-------------+------------+-----------+---------------+--------------+
2 rows in set (0.00 sec)
--客户端2添加表共享读锁
(root@Master) [fruitsDB] 10:44:09> lock tables fruits read;
(此时hang住)
说明：客户端2执行该语句时，会去判断该表意向锁情况，此时为意向排他锁，其与表共享读锁互斥，此时客户端2便会处于阻塞状态.当客户端1提交事务后，客户端2的表共享读锁便会添加成功.
--客户端1提交事务
(root@Master) [fruitsDB] 10:48:58> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端2表共享读锁成功添加
(root@Master) [fruitsDB] 10:44:09> lock tables fruits read;
Query OK, 0 rows affected (4 min 14.07 sec)
(root@Master) [fruitsDB] 10:54:43> unlock tables;
Query OK, 0 rows affected (0.00 sec)
说明：客户端2执行lock tables fruits write也会hang住.以上为意向锁的演示，意向锁主要是解决添加表锁与行锁的冲突问题.
4、行级锁
4.1、理论知识
每次操作锁住对应的行数据，锁定力度最小，发生锁冲突的概率最低，并发度最高，应用在InnoDB存储引擎中.InnoDB的数据是基于索引组织的，行级锁是通过对索引上的索引项加锁来实现的，而不是对记录加锁.
行级锁主要分为以下三类：
1） 行锁(Record Lock)：锁定单个行记录，防止其它事务对此进行update和delete，在RC、RR隔离级别下都支持；
2） 间隙锁(Gap Lock)：锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其它事务在该间隙进行insert，产生幻读，在RR隔离级别下支持；
3） 临健锁(Next-Key Lock)：行锁和间隙锁组合，同时锁住数据以及数据前面的间隙Gap，在RR隔离级别下支持.
InnoDB实现以下两种类型的行锁：
1） 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；
2） 排他锁(X)：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁.
默认情况下InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读.
1） 针对唯一索引进行检索时，对已存在的记录进行等值匹配将会自动优化为行锁.
2） InnoDB的行锁是针对索引加锁，若不通过索引条件检索数据，此时InnoDB将对表中所有记录加锁，也就会升级为表锁.
4.2、共享锁与共享锁兼容演示
--客户端1
(root@Master) [fruitsDB] 10:54:43> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 11:20:42> select * from fruits where f_id='a1' lock in share mode;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| a1 | 101 | apple | 6.50 |
+------+------+--------+---------+
1 row in set (0.00 sec)
--锁信息查询
(root@Master) [(none)] 11:20:32> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+--------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+---------------+--------------+
| fruitsDB | fruits | NULL | TABLE | IS | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | S,REC_NOT_GAP | 'a1 ' |
+---------------+-------------+------------+-----------+---------------+--------------+
2 rows in set (0.00 sec)
--客户端2也创建共享锁，旨在验证共享锁与共享锁之间的兼容性.
(root@Master) [fruitsDB] 10:55:38> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 11:21:45> select * from fruits where f_id='a1' lock in share mode;
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| a1 | 101 | apple | 6.50 |
+------+------+--------+---------+
1 row in set (0.00 sec)
(root@Master) [(none)] 11:20:59> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+--------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+---------------+--------------+
| fruitsDB | fruits | NULL | TABLE | IS | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | S,REC_NOT_GAP | 'a1 ' |
| fruitsDB | fruits | NULL | TABLE | IS | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | S,REC_NOT_GAP | 'a1 ' |
+---------------+-------------+------------+-----------+---------------+--------------+
4 rows in set (0.00 sec)
小结：客户端2也能创建共享锁，说明共享锁与共享锁之间兼容.
4.3、共享锁与排他锁兼容演示
--客户端2
(root@Master) [fruitsDB] 11:21:50> commit;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 11:23:47> begin;
Query OK, 0 rows affected (0.00 sec)
--客户端3查锁情况
(root@Master) [(none)] 11:21:58> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+--------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+---------------+--------------+
| fruitsDB | fruits | NULL | TABLE | IS | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | S,REC_NOT_GAP | 'a1 ' |
+---------------+-------------+------------+-----------+---------------+--------------+
2 rows in set (0.00 sec)
--客户端2添加排他锁
(root@Master) [fruitsDB] 11:24:03> update fruits set f_price=2.5 where f_id='a2';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
(root@Master) [fruitsDB] 11:26:01> update fruits set f_price=6.5 where f_id='a1';
(此时hang住)
说明：客户端1已对f_id为a1的行添加共享锁，客户端2对f_id为a1执行update产生排它锁，共享锁与排它锁互斥，因此hang住.
--客户端3查锁情况
(root@Master) [(none)] 11:23:50> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+--------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+---------------+--------------+
| fruitsDB | fruits | NULL | TABLE | IX | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | X,REC_NOT_GAP | 'a2 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X,REC_NOT_GAP | 'a1 ' |
| fruitsDB | fruits | NULL | TABLE | IS | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | S,REC_NOT_GAP | 'a1 ' |
+---------------+-------------+------------+-----------+---------------+--------------+
5 rows in set (0.00 sec)
4.4、排他锁与排他锁兼容演示
--客户端1
(root@Master) [fruitsDB] 11:20:49> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端2
(root@Master) [fruitsDB] 11:27:06> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端3查锁情况
(root@Master) [(none)] 11:28:56> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)
--客户端1开启事务
(root@Master) [fruitsDB] 11:28:52> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 11:30:55> update fruits set f_price=6.0 where f_id='a1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--客户端2
(root@Master) [fruitsDB] 11:30:02> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 11:31:07> update fruits set f_price=6.0 where f_id='a1';
(此时hang住)
说明：客户端1已产生排它锁，若客户端2在更新相同f_id的行记录，排它锁与排它锁之间互斥，所以会hang住，不过1分钟后会自动释放锁.
--客户端3查锁信息
(root@Master) [(none)] 11:32:08> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+--------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+---------------+--------------+
| fruitsDB | fruits | NULL | TABLE | IX | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | X,REC_NOT_GAP | 'a1 ' |
| fruitsDB | fruits | NULL | TABLE | IX | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | X,REC_NOT_GAP | 'a1 ' |
+---------------+-------------+------------+-----------+---------------+--------------+
4 rows in set (0.00 sec)
4.5、行锁为索引添加的锁演示
说明：以下验证InnoDB行锁是针对索引加的锁，若不通过索引检索数据，则InnoDB将对表中所有服务器托管网记录加锁，此时行锁升级为表锁.
4.5.1、无索引情况
--客户端1
(root@Master) [fruitsDB] 11:31:01> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端2
(root@Master) [fruitsDB] 11:35:10> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端3查锁情况
(root@Master) [(none)] 11:41:09> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)
--客户端1
(root@Master) [fruitsDB] 11:41:06> desc fruits;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| f_id | char(10) | NO | PRI | NULL | |
| s_id | int | NO | | NULL | |
| f_name | char(255) | NO | MUL | NULL | |
| f_price | decimal(8,2) | NO | | NULL | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
(root@Slave01) [fruitsDB] 11:46:13 46> show index from fruits G
*************************** 1. row ***************************
Table: fruits
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: f_id
Collation: A
Cardinality: 16
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.00 sec)
(root@Master) [fruitsDB] 11:43:16 46> select * from fruits;
+------+------+---------+---------+
| f_id | s_id | f_name | f_price |
+------+------+---------+---------+
| a1 | 101 | apple | 6.00 |
| a2 | 103 | cac | 2.50 |
| b1 | 101 | pair | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
+------+------+---------+---------+
16 rows in set (0.00 sec)
(root@Master) [fruitsDB] 11:42:14> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 11:42:33> update fruits set f_name='abc' where f_name='pair';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--客户端2
(root@Master) [fruitsDB] 11:46:09> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 11:47:40> update fruits set f_name='lemon' where f_id='a1';
(此时hang住)
说明：此时虽然更新的不是同一行，但客户端1 update的字段上没有索引，因此会升级为表锁.
--客户端3查索引情况
(root@Master) [(none)] 11:53:02> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+---------------+------------------------+
| fruitsDB | fruits | NULL | TABLE | IX | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | X,REC_NOT_GAP | 'a1 ' |
| fruitsDB | fruits | NULL | TABLE | IX | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | X | supremum pseudo-record |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'a1 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'a2 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'b1 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'b2 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'b5 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'bs1 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'bs2 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'c0 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'l2 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'm1 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'm2 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'm3 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 'o2 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 't1 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 't2 ' |
| fruitsDB | fruits | PRIMARY | RECORD | X | 't4 ' |
+---------------+-------------+------------+-----------+---------------+------------------------+
20 rows in set (0.00 sec)
4.5.2、有索引情况
--给f_name添加索引后在做测试
--客户端1
(root@Master) [fruitsDB] 11:47:22> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端2
(root@Master) [fruitsDB] 11:53:55> commit;
Query OK, 0 rows affected (0.01 sec)
--客户端3查锁情况
(root@Master) [(none)] 11:53:58> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)
--客户端1
(root@Master) [fruitsDB] 11:54:14> create index idx_fruits_name on fruits(f_name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@Master) [fruitsDB] 11:55:14> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 11:55:22> update fruits set f_name='pair' where f_name='abc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--客户端2
(root@Master) [fruitsDB] 11:54:18> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [fruitsDB] 11:55:46> update fruits set f_name='cac' where f_id='a2';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
说明：如上所示，当在fruits表的f_name字段上添加索引后，客户端1在执行update语句时，不会将所有的行都锁上，客户端2成功执行update.
--客户端3查询锁情况
(root@Master) [(none)] 12:06:19> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+-----------------+-----------+---------------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+-----------------+-----------+---------------+------------------------+
| fruitsDB | fruits | NULL | TABLE | IX | NULL |
| fruitsDB | fruits | PRIMARY | RECORD | X,REC_NOT_GAP | 'a2' |
| fruitsDB | fruits | NULL | TABLE | IX | NULL |
| fruitsDB | fruits | idx_fruits_name | RECORD | X | 'abc', 'b1' |
| fruitsDB | fruits | PRIMARY | RECORD | X,REC_NOT_GAP | 'b1' |
| fruitsDB | fruits | idx_fruits_name | RECORD | X,GAP | 'apple', 'a1' |
+---------------+-------------+-----------------+-----------+---------------+------------------------+
解锁：
--客户端1
(root@Master) [fruitsDB] 11:55:29> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端2
(root@Master) [fruitsDB] 11:55:52> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端3查询锁情况
(root@Master) [(none)] 12:32:51> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)
4.6、间隙锁和临键锁
4.6.1、理论知识
默认情况下InnoDB在REPEATABLE READ事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读.
1） 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁；
2） 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁；
3） 索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止.
注意：间隙锁唯一目的是防止其它事务插入间隙，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁.
4.6.2、给不存在的值加锁演示
--客户端1
(root@Master) [booksDB] 12:49:36> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title | copyright |
+-------+---------------------------+-----------+
| 11026 | Guide to MySQL 5.7 | 2008 |
| 11028 | Learning C++ | 2005 |
| 11033 | Study Html | 2011 |
| 11035 | How to use php | 2003 |
| 11041 | Inside VC++ | 2011 |
| 11069 | MYSQL professional | 2009 |
| 11072 | Teach yourself javascript | 2005 |
| 11078 | Learning MYSQL | 2010 |
+-------+---------------------------+-----------+
8 rows in set (0.00 sec)
(root@Master) [booksDB] 12:49:40> desc books;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bk_id | int | NO | PRI | NULL | |
| bk_title | varchar(50) | NO | | NULL | |
| copyright | year | NO | MUL | NULL | |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
(root@Master) [booksDB] 12:49:52> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [booksDB] 12:50:12> update books set bk_title='Math' where bk_id=11029;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
说明：bk_id为11029的记录在表books中不存在.
--客户端3查询锁情况
(root@Master) [(none)] 12:33:01> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+-----------+
| booksDB | books | NULL | TABLE | IX | NULL |
| booksDB | books | PRIMARY | RECORD | X,GAP | 11033 |
+---------------+-------------+------------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
说明：lock_mode中存在GAP指的是间隙锁，锁的是book_id为11028至11033之间的间隙，间隙锁不包含11028和11033这两个临界值.
--客户端2
(root@Master) [booksDB] 12:32:55> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [booksDB] 13:03:58> insert into books values(11030,'MySQL',2023);
(此时hang住)
小结：由上可知，客户端1执行update语句后将11028和11033之间的间隙加锁，以至客户端2 hang住.
--客户端3查锁情况
(root@Master) [(none)] 13:05:00> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+------------------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+------------------------+-----------+
| booksDB | books | NULL | TABLE | IX | NULL |
| booksDB | books | PRIMARY | RECORD | X,GAP,INSERT_INTENTION | 11033 |
| booksDB | books | NULL | TABLE | IX | NULL |
| booksDB | books | PRIMARY | RECORD | X,GAP | 11033 |
+---------------+-------------+------------+-----------+------------------------+-----------+
4 rows in set (0.01 sec)
解锁：
--客户端1
(root@Master) [booksDB] 12:50:27> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端2
(root@Master) [booksDB] 13:06:00> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端3查锁情况
(root@Master) [(none)] 13:05:10> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)
4.6.3、非唯一索引等值查询时加锁演示
非唯一索引上的等值查询，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁.
--客户端1
(root@Master) [booksDB] 13:08:53> alter table books add index ind_books_cpt(copyright);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 1
(root@Master) [booksDB] 13:10:10> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [booksDB] 13:10:16> select * from books where copyright='2008' lock in share mode;
+-------+--------------------+-----------+
| bk_id | bk_title | copyright |
+-------+--------------------+-----------+
| 11026 | Guide to MySQL 5.7 | 2008 |
+-------+--------------------+-----------+
1 row in set (0.00 sec)
--客户端3查锁情况
(root@Master) [(none)] 13:12:01> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+---------------+-----------+---------------+------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+---------------+-----------+---------------+------------+
| booksDB | books | NULL | TABLE | IS | NULL |
| booksDB | books | ind_books_cpr | RECORD | S | 108, 11026 | #临键锁，将11026前的数据加锁
| booksDB | books | PRIMARY | RECORD | S,REC_NOT_GAP | 11026 | #给11026行添加行锁
| booksDB | books | ind_books_cpr | RECORD | S,GAP | 109, 11069 | #间隙锁，将11026至11069的间隙加锁
+---------------+-------------+---------------+-----------+---------------+------------+
4 rows in set (0.00 sec)
说明：为防止出现幻读现象，所以会将11026至11069之间的间隙锁住，11026之前的间隙也锁住,11026行数据也锁住.这就是非唯一索引在进行等值查询时所加的锁.
解锁
--客户端1
(root@Master) [booksDB] 13:12:09> commit;
Query OK, 0 rows affected (0.00 sec)
--客户端3查锁情况
(root@Master) [(none)] 13:19:45> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
Empty set (0.00 sec)
4.6.4、唯一索引范围查询时加锁演示
说明：唯一索引上的范围查询，会访问到不满足条件的第一个值为止.
--客户端1
(root@Master) [booksDB] 13:19:50> begin;
Query OK, 0 rows affected (0.00 sec)
(root@Master) [booksDB] 13:20:37> select * from books where bk_id>11070 lock in share mode;
+-------+---------------------------+-----------+
| bk_id | bk_title | copyright |
+-------+---------------------------+-----------+
| 11072 | Teach yourself javascript | 2005 |
| 11078 | Learning MYSQL | 2010 |
+-------+---------------------------+-----------+
2 rows in set (0.00 sec)
--客户端3查锁情况
(root@Master) [(none)] 13:19:52> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+-----------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode | lock_data |
+---------------+-------------+------------+-----------+-----------+------------------------+
| booksDB | books | NULL | TABLE | IS | NULL |
| booksDB | books | PRIMARY | RECORD | S | supremum pseudo-record | #临键锁，将11078至正无穷大加锁
| booksDB | books | PRIMARY | RECORD | S | 11078 | #临键锁，将11072至11078加锁
| booksDB | books | PRIMARY | RECORD | S | 11072 | #临键锁，将11069至11072加锁
+---------------+-------------+------------+-----------+-----------+------------------------+
4 rows in set (0.00 sec)
说明：客户端1执行的SQL语句会加临键锁以及11072到无穷大的数据被锁住.
--客户端2
(root@Master) [booksDB] 13:09:02> insert into books values(11070,'oracle',2053);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
(root@Master) [booksDB] 13:40:31> insert into books values(11073,'oracle',2053);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
(root@Master) [booksDB] 13:41:33> insert into books values(11080,'oracle',2053);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
说明：如上所示，客户端2无法在11069至正无穷添加记录.
参考网址：https://www.bilibili.com/video/BV1Z8411F7M3?p=11&spm_id_from=pageDriver&vd_source=8c872e2fd1d99229b38a73ed6718b776服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net
你好，我是豌豆花下猫。这里记录每周值得分享的 Python 及通用技术内容，部分为英文，已在小标题注明。（标题取自其中一则分享，不代表全部内容都是该主题，特此声明。） 文章&教程 1、掌握Python面向对象编程的关键：类与对象 介绍类和对象概念，通过…
