文章目录
- 一、MySQL中各方面锁的分类
- 二、解释锁
- 2.1 全局锁
- 2.1.1 解释
- 2.1.2 全局锁的作用
- 2.1.3 释放锁
- 2.1.4 全局锁的场景（mysqldump）
- 2.1.5 全局锁加锁的方法
- 2.2 表级锁
- 2.2.1 表锁
- 2.2.1.1 解释
- 2.2.1.2 表锁的作用
- 2.2.1.3 释放锁
- 2.2.2 MDL（元数据锁）
- 2.2.2.1 解释
- 2.2.2.2 MDL的作用
- 2.2.2.3 MDL锁测试
- 2.2.2.4 解决MDL锁问题
- 2.3 行级锁
- 2.3.0 什么叫共享锁和排它锁？
- 2.3.1 解释
- 2.3.2 加锁方式
- 2.3.3 测试
- 2.3.3.1 共享锁
- 2.3.3.2 排它锁
- 2.4 行锁算法
- 2.4.1 间隙锁
- 2.4.1.1 解释
- 2.4.1.2 间隙锁的条件
- 2.4.1.3 测试
- 2.4.2 Recordlock锁
- 2.4.3 next-key锁
- 2.5 插入意向锁
一、MySQL中各方面锁的分类
粒度：全局锁、表锁、行锁、页锁(不做解释)
算法：记录锁（Record lock）、间隙锁（gap lock）、下一键锁（next-key lock）
实现机制：悲观锁、乐观锁
兼容性：共享锁（读锁）、排他锁（写锁）、意向锁。
二、解释锁
2.1 全局锁
2.1.1 解释
全局锁，字面意思就是MySQL全局的锁，MySQL使用**Flush tables with read lock (FTWRL)**来加全局读锁。
2.1.2 全局锁的作用
从加锁语句中，我们可以看到全局锁实际上是一个读锁，并且作用在全实例上面，如果你想要让整个实例处于一个只读的状态，那就可以加FTWRL。
包含部分语句都将不能执行，比如 create 、 drop 、delete 、update 、insert等
mysql> Flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database gengjin;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> alter table sbtest1 add index idx_g(id);
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> update sbtest1 set k=12344 where id =34;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> delete from sbtest;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> create table gengjin(id int);
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> create database ggg;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql>
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database gengjin;
Query OK, 3 rows affected (0.03 sec)
mysql>2.1.3 释放锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql>2.1.4 全局锁的场景（mysqldump）
我们可以打开mysql的general_log。然后进行备份操作，general_log中会有加锁过程的。
mysql> show variables like "gen%";
+------------------+----------------------------------+
| Variable_name | Value |
+------------------+----------------------------------+
| general_log | ON |
| general_log_file | /data/mysql/data/3306/node02.log |
+------------------+----------------------------------+
2 rows in set (0.00 sec)
mysql>general_log
2021-09-06T14:42:30.078603+08:00 144 Connect root@localhost on using Socket
2021-09-06T14:42:30.079905+08:00 144 Query /*!40100 SET @@SQL_MODE='' */
2021-09-06T14:42:30.081459+08:00 144 Query /*!40103 SET TIME_ZONE='+00:00' */
2021-09-06T14:42:30.082533+08:00 144 Query FLUSH /*!40101 LOCAL */ TABLES
2021-09-06T14:42:30.083060+08:00 144 Query FLUSH TABLES WITH READ LOCK #加锁
2021-09-06T14:42:30.083730+08:00 144 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2021-09-06T14:42:30.084201+08:00 144 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2021-09-06T14:42:30.086380+08:00 144 Query SHOW VARIABLES LIKE 'gtid\_mode'
2021-09-06T14:42:30.120313+08:00 144 Query SELECT @@GLOBAL.GTID_EXECUTED
2021-09-06T14:42:30.120696+08:00 144 Query SHOW MASTER STATUS
2021-09-06T14:42:30.121973+08:00 144 Query UNLOCK TABLES #解锁mysqldump作为mysql自带的逻辑备份工具，在备份的时候也是会进行加全局读锁的，尽管使用参数–single-transaction，导数据之前就会启动一个事务，来确保拿到一致性快照视图
那为什么还需要全局读锁呢？
原因在于，部分存储引擎是不支持事务的，比如myisam存储引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。
2.1.5 全局锁加锁的方法
1.Flush tables with read lock
2.set global read_only=1
添加全局读锁的方法，目前仅支持以上两种，但是为什么我们不会用第二种呢？
原因：
1.read_only虽然的确可以起到MySQL全局只读的效果，但是在一个高可用软件中，read_only是判断主从身份的一个重要手段，如果我们将主库的该参数进行修改，那对主从架构将会有很大的影响。
2.在异常处理机制上有差异。如果执行FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
3.read_only不能限制拥有super权限的用户，也就是说，如果这个用户拥有super权限，就算你打开该参数，该用户也能进行写操作。虽然mysql已经引进另外一个参数super_read_only，但是我们还是不建议使用。
2.2 表级锁
MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
2.2.1 表锁
2.2.1.1 解释
表锁，顾名思义就是限制单表的一种锁，可以通过语句（lock tables 表名 read/write）来限制该表。下面用小测试来讲述。
lock tables sbtest1 read
#当前会话：
当前会话对sbtest1无写权限，当前会话对别的表无修改及查看权限
mysql> lock tables sbtest1 read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from sbtest1 limit 2;
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 50095 | 33524783776-74656600149-90205850992-68486233199-38139582553-31032853662-46232099172-74461690624-87909115866-85457687836 | 61164888858-85154493460-35907316816-37705648466-22765373704 |
| 2 | 50157 | 91282821879-27789870274-87640051743-39804400153-74473090426-79246130794-92788146526-95290529525-17128128654-00698810889 | 39117864852-84907815709-14221566017-79416635061-15970385071 |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> delete from sbtest1;
ERROR 1099 (HY000): Table 'sbtest1' was locked with a READ lock and can't be updated
mysql> select * from sbtest2 limit 2;
ERROR 1100 (HY000): Table 'sbtest2' was not locked with LOCK TABLES
mysql> delete from sbtest2 where id = 3;
ERROR 1100 (HY000): Table 'sbtest2' was not locked with LOCK TABLES
mysql>#其他会话：
对所有表都有查询权限，对sbtest1表无修改权限
mysql> select * from sbtest1 limit 2;
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 50095 | 33524783776-74656600149-90205850992-68486233199-38139582553-31032853662-46232099172-74461690624-87909115866-85457687836 | 61164888858-85154493460-35907316816-37705648466-22765373704 |
| 2 | 50157 | 91282821879-27789870274-87640051743-39804400153-74473090426-79246130794-92788146526-95290529525-17128128654-00698810889 | 39117864852-84907815709-14221566017-79416635061-15970385071 |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from sbtest2 limit 2;
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 49851 | 49437401037-97762696168-23266683172-53063189105-58925491697-00106480546-10024369781-57478416525-33768754369-87166831339 | 47328242323-08400347702-59924112291-23393276689-85999736088 |
| 2 | 50339 | 90691841825-92113940745-46948343460-63452860128-13642232178-75173357968-99081180773-73444812273-65928647460-85455624578 | 36116300692-38522419200-59052290502-56511681674-29972993336 |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> delete from sbtest1 where id = 456;
夯住。。。
mysql> show processlist;
+-----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
| 143 | root | localhost | xbk | Query | 0 | System lock | show processlist |
| 145 | root | localhost | xbk | Query | 54 | Waiting for table metadata lock | delete from sbtest1 where id = 456 |
+-----+------+-----------+------+---------+------+---------------------------------+------------------------------------+
2 rows in set (0.00 sec)
mysql>lock tables sbtest1 write
#当前会话
对sbtest1拥有查询权限和修改权限，对其他表没有修改权限和查询权限
mysql> lock tables sbtest1 write;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from sbtest1 where id = 3;
Query OK, 1 row affected (0.03 sec)
mysql> select * from sbtest1 limit 2;
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 50095 | 33524783776-74656600149-90205850992-68486233199-38139582553-31032853662-46232099172-74461690624-87909115866-85457687836 | 61164888858-85154493460-35907316816-37705648466-22765373704 |
| 2 | 50157 | 91282821879-27789870274-87640051743-39804400153-74473090426-79246130794-92788146526-95290529525-17128128654-00698810889 | 39117864852-84907815709-14221566017-79416635061-15970385071 |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> delete from sbtest2 where id = 34;
ERROR 1100 (HY000): Table 'sbtest2' was not locked with LOCK TABLES
mysql> select * from sbtest2 limit 2;
ERROR 1100 (HY000): Table 'sbtest2' was not locked with LOCK TABLES
mysql>#其他会话
对sbtest1没有查询权限和修改权限，对其他表有查询权限和修改权限
mysql> delete from sbtest1 where id = 4;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from sbtest1 limit 2;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> delete from sbtest2 where id = 34;
Query OK, 1 row affected (0.00 sec)
mysql> select * from sbtest2 limit 2;
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 49851 | 49437401037-97762696168-23266683172-53063189105-58925491697-00106480546-10024369781-57478416525-33768754369-87166831339 | 47328242323-08400347702-59924112291-23393276689-85999736088 |
| 2 | 50339 | 90691841825-92113940745-46948343460-63452860128-13642232178-75173357968-99081180773-73444812273-65928647460-85455624578 | 36116300692-38522419200-59052290502-56511681674-29972993336 |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>总结：
1.当对sbtest1表加上表锁的时候（不管read还是write），该会话对其他的表就没有了任何权限。
2.当对sbtest1加上read的表锁的时候，当前会话对sbtest1就只有读的权限，当对sbtest1加上write的表锁的时候，当前会话对sbtest1既有读的权限也有写的权限。
3.当对sbtest1加上read的表锁的时候，其他会话对sbtest1就只有读的权限，当对sbtest1加上write的表锁的时候，其他会话对sbtest1没有任何权限。
2.2.1.2 表锁的作用
在没有出现粒度更小的行锁的情况下，表锁是控制并发主要的方法。但是现在基本上没有人会锁定整个表，因为这样的对业务影响太巨大。
2.2.1.3 释放锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql>2.2.2 MDL（元数据锁）
MDL全称metadata lock，所以又称元数据锁。
2.2.2.1 解释
MDL是另外一种表级别的锁，它不需要显示使用，也就是说，当你对一个表进行查询的时候，它会自动获得MDL读锁，当你对一个表进行增删改查的时候（比如修改表结构），会获得MDL写锁。
2.2.2.2 MDL的作用
MDL的作用是，保证读写的正确性。比如当你在进行查询的时候，另外一个人将你的表结构改了，想想就心头一颤。。
2.2.2.3 MDL锁测试
1.会话1（查询）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from city limit 2;
+----+----------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
+----+----------+-------------+----------+------------+
2 rows in set (0.00 sec)
mysql>2.会话2（查询）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from city limit 2;
+----+----------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
+----+----------+-------------+----------+------------+
2 rows in set (0.00 sec)
mysql>3.会话3（修改表结构）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table city drop index idx_gj;
4.会话4 （查询）
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from city limit 3;
mysql> show processlist;
+-----+------+-----------+-------+---------+------+---------------------------------+------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+-------+---------+------+---------------------------------+------------------------------------+
| 143 | root | localhost | world | Sleep | 212 | | NULL |
| 145 | root | localhost | world | Sleep | 207 | | NULL |
| 152 | root | localhost | world | Query | 190 | Waiting for table metadata lock | alter table city drop index idx_gj |
| 153 | root | localhost | world | Query | 183 | Waiting for table metadata lock | select * from city limit 3 |
| 154 | root | localhost | NULL | Query | 0 | System lock | show processlist |
+-----+------+-----------+-------+---------+------+---------------------------------+------------------------------------+
5 rows in set (0.01 sec)
mysql>总结：我们可以看到当有两个线程相继拿到MDL读锁的时候，再进行修改表结构等操作的时候，该线程会去拿该表的MDL写锁，但是读锁没有释放，写锁是拿不到的，所以该操作就会夯住，此刻该表就完全锁住了，后面的查询操作或者写操作也会被夯住，而且表锁不像行锁，不会有超时时间，会一直夯下去，直到你COMMIT或ROLLBACK。所以是比较危险的。
2.2.2.4 解决MDL锁问题
解决这个问题，也就是提交或者回滚这个事务，我们可以通过information_schema.innodb_trx这个表找到该事务。
1.#找到大事务
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G
*************************** 1. row ***************************
trx_id: 422116095372000
trx_state: RUNNING
trx_started: 2021-09-06 16:55:34
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 156
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
trx_id: 422116095371088
trx_state: RUNNING
trx_started: 2021-09-06 16:55:30
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 155
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
2.#找到show processlist;中对应的线程
mysql> show processlist;
+-----+------+-----------+-------+---------+------+---------------------------------+------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+-------+---------+------+---------------------------------+------------------------------------------------+
| 154 | root | localhost | NULL | Query | 0 | System lock | show processlist |
| 155 | root | localhost | world | Sleep | 115 | | NULL |
| 156 | root | localhost | world | Sleep | 111 | | NULL |
| 157 | root | localhost | world | Query | 106 | Waiting for table metadata lock | alter table city add index idx_gj(CountryCode) |
| 158 | root | localhost | world | Query | 102 | Waiting for table metadata lock | select * from city limit 3 |
+-----+------+-----------+-------+---------+------+---------------------------------+------------------------------------------------+
5 rows in set (0.00 sec)
mysql>
#kill掉对应线程即可
kill 157;
kill 158;2.3 行级锁
2.3.0 什么叫共享锁和排它锁？
共享锁(S)：允许拥有共享锁的事务读取该行数据。当一个事务拥有一行的共享锁时，另外的事务可以在同一行数据也获得共享锁，但另外的事务无法获得同一行数据上的排他锁
排它锁(X)：允许拥有排它锁的事务修改或删除该行数据。 当一个事务拥有一行的排他锁时，另外的事务在此行数据上无法获得共享锁和排它锁，只能等待第一个事务的锁释放
2.3.1 解释
MySQL行级锁是施加在索引行数据上的锁，例如select * from sbtest where ID = 24 for update,则表示获得了id=24这一列的排它锁，其他线程将不能对其进行修改和查询。
当一个InnoDB表没有任何索引时， 则行级锁会施加在隐含创建的聚簇索引上，所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X(排它)锁，这个类似于表锁，但原理上和表锁应该是完全不同的
2.3.2 加锁方式
共享锁：select * from sbtest1 where ID = 24 lock in share mode;
排它锁：select * from sbtest where ID = 24 for update；
2.3.3 测试
2.3.3.1 共享锁
1.#获取共享锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from city where id < 10 lock in share mode;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
+----+----------------+-------------+---------------+------------+
9 rows in set (0.00 sec)
mysql>
2.#其他线程获取共享锁
mysql> select * from city where id < 10 lock in share mode ;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
+----+----------------+-------------+---------------+------------+
9 rows in set (0.00 sec)
3.#其他线程获取排它锁
mysql> select * from city where id < 10 for update ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
4.#当然我们也可以直接去查询和修改
mysql> select * from city where id < 10;
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
+----+----------------+-------------+---------------+------------+
9 rows in set (0.00 sec)
mysql> update city set countrycode = 'CHN' where id < 10;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
5.#修改其他结果集
mysql> update city set countrycode = 'CHN' where id > 10 and id < 100;
Query OK, 89 rows affected (0.00 sec)
Rows matched: 89 Changed: 89 Warnings: 0
mysql>总结：当一个线程获得一部分结果集（某些行）的共享锁的时候，其他线程可以获得该结果集的共享锁（可查询），但是不能获得该结果集的排它锁（不可修改）
2.3.3.2 排它锁
1.#获取排它锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from city where id = 10 for update;
+----+---------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+---------+-------------+---------------+------------+
| 10 | Tilburg | NLD | Noord-Brabant | 193238 |
+----+---------+-------------+---------------+------------+
1 row in set (0.00 sec)
2.#其他线程获得共享锁
mysql> select * from city where id = 10 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
3.#其他线程获得排它锁
mysql> select * from city where id = 10 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
4.#获取其他结果集
mysql> select * from city where id = 11 lock in share mode;
+----+-----------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-----------+-------------+-----------+------------+
| 11 | Groningen | CHN | Groningen | 172701 |
+----+-----------+-------------+-----------+------------+
1 row in set (0.00 sec)
mysql> select * from city where id = 11 for update;
+----+-----------+-------------+-----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-----------+-------------+-----------+------------+
| 11 | Groningen | CHN | Groningen | 172701 |
+----+-----------+-------------+-----------+------------+
1 row in set (0.00 sec)
mysql>
5.#锁信息查看
#查看锁等待
select * from information_schema.innodb_lock_waits;
#查看锁状态
select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked from information_schema.innodb_trx;
#查看锁的信息
select lock_id,lock_trx_id,lock_mode,lock_type,lock_index,lock_rec,lock_data from information_schema.innodb_locks;总结：当一个线程获得某部分结果集的排它锁的时候，其他线程不能获得该部分结果集的共享锁和排它锁。
2.4 行锁算法
PS : Next-KeyLocks=Gap锁+ Recordlock锁
2.4.1 间隙锁
(不锁记录，仅仅记录前面的Gap)
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件 的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”， InnoDB也会对这个“间隙”加锁。
2.4.1.1 解释
间隙锁是施加在索引记录之间的间隔上的锁， 锁定一个范围的记录、但不包括记录本身,比如SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE语句，尽管有可能对c1字段来说当前表里没有=15 的值，但还是会阻止=15的数据的插入操作，是因为间隙锁已经把索引查询范围内的间隔数据也都锁住了。2.4.1.2 间隙锁的条件
gap lock的前置条件：
1 事务隔离级别为REPEATABLE-READ， innodb_locks_unsafe_for_binlog参数为0，且sql走的索引为非唯一索引（无论是等值检索还是范围检索）
2 事务隔离级别为REPEATABLE-READ， innodb_locks_unsafe_for_binlog参数为0，且sql是一个范围的当前读操作，这时即使不是非唯一索引也会加gap lock
2.4.1.3 测试
1.#查看全部数据
mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | 小罗 |
| 5 | 小黄 |
| 7 | 小明 |
| 11 | 小红 |
+----+--------+
4 rows in set (0.00 sec)
mysql>
2.#获取5-7的排它锁
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where id >= 5 and id <= 7 for update;
+----+--------+
| id | name |
+----+--------+
| 5 | 小黄 |
| 7 | 小明 |
+----+--------+
2 rows in set (0.00 sec)
3.查看数据插入及锁定情况
mysql> INSERT INTO `test` (`id`, `name`) VALUES (3, '小东');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO `test` (`id`, `name`) VALUES (4, '小东');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test` (`id`, `name`) VALUES (5, '小东');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test` (`id`, `name`) VALUES (6, '小东');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test` (`id`, `name`) VALUES (7, '小东');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test` (`id`, `name`) VALUES (8, '小东');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test` (`id`, `name`) VALUES (9, '小东');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test` (`id`, `name`) VALUES (10, '小东');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test` (`id`, `name`) VALUES (11, '小东');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO `test` (`id`, `name`) VALUES (12, '小东');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO `test` (`id`, `name`) VALUES (13, '小东');
Query OK, 1 row affected (0.00 sec)
mysql>
总结：我们虽然只是给5-7加了一个排它锁，但是它会给5-7的间隙和7-11的间隙里面的”空ID“也会加上间隙锁，
这种情况下是插入不了数据的。2.4.2 Recordlock锁
（锁数据，不锁Gap）
记录锁是对索引记录的锁。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;阻止任何其他事务插入、更新或删除 t.c1 值为 10 的行。 记录锁总是锁定索引记录，即使一个表没有定义索引。对于这种情况，InnoDB 会创建一个隐藏的聚集索引并使用该索引进行记录锁定。
2.4.3 next-key锁
**(同时锁住记录(数据)，并且锁住记录前面的Gap) **
next-key 锁是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。 InnoDB 执行行级锁定的方式是，当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁或排他锁。因此，行级锁实际上是索引记录锁。索引记录上的下一个键锁也会影响该索引记录之前的“间隙”。也就是说，next-key 锁是一个索引记录锁加上一个在索引记录之前的间隙上的间隙锁。如果一个会话对索引中的记录 R 具有共享锁或排他锁，则另一个会话不能在索引顺序中紧靠 R 之前的间隙中插入新的索引记录。 假设一个索引包含值 10、11、13 和 20。该索引可能的 next-key 锁涵盖以下区间，其中圆括号表示排除区间端点，方括号表示包含端点。
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)2.5 插入意向锁
插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。该锁用以表示插入意向，当多个事务在同一区间（gap）插入位置不同的多条数据时，事务之间不需要互相等待。假设存在两条值分别为 4 和 7 的记录，两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占的（排他）锁前，都会获取（4，7）之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突（阻塞等待）。
总结来说，插入意向锁的特性可以分成两部分：
1.插入意向锁是一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。
2.插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。
需要强调的是，虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
