目录
概述
InnoDB 中锁非常多，总的来说，可以如下分类：
相对其他数据库而言，MySQL的锁机制比较简单，特点是不同的存储引擎支持不同的锁机制，比如MyISAM和MEMORY采用的是表级锁，BDB采用的是页面锁（也支持表级锁），InnoDB既支持行级锁，也支持表级锁，默认情况下采用的是行锁。
- 表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，锁冲突概率最高，并发度最低
- 行级锁：开销大，加锁慢，会出现死锁，锁定粒度最小，所冲突概率最低，并发度最高
- 页面锁：开销和加锁时间介于表锁和行锁之间，会出现死锁，粒度介于表锁和行锁之间，并发度一般
InnoDB的表级锁
表级别的 S 锁、X 锁
在对某个表执行 SELECT、INSERT、DELETE、UPDATE 语句时，InnoDB 存储引擎是不会为这个表添加表级别的 S 锁或者 X 锁的。
另外，在对某个表执行一些如 ALTER TABLE、DROP TABLE 这类的 DDL 语句时，其他事务对这个表并发执行如 SELECT、INSERT、DELETE、UPDATE 的语句会发生阻塞，同理，某个事务中对某个表执行 SELECT、INSERT、DELETE、UPDATE 语句时，在其他会话中对这个表执行 DDL 语句也会发生阻塞。这个过程其实是通过在 server 层使用一种称之为元数据锁来实现的。
给InnoDB 引擎的表 加 S 锁或者 X 锁可以这么写：
LOCK TABLES t READ：表级别的 S 锁。
LOCK TABLES t WRITE：表级别的 X 锁。
表级别的 IS 锁、IX 锁
当我们在对使用 InnoDB 存储引擎的表的某些记录加 S 锁之前，那就需要先在表级别加一个 IS 锁，当我们在对使用 InnoDB 存储引擎的表的某些记录加 X 锁之前，那就需要先在表级别加一个 IX 锁。
IS 锁和 IX 锁的使命只是为了后续在加表级别的 S 锁和 X 锁时判断表中是否有已经被加锁的记录，以避免用遍历的方式来查看表中有没有上锁的记录。我们并不能手动添加意向锁，只能由 InnoDB 存储引擎自行添加。
表级别的各种锁的兼容性:
| 兼容性 | X | IX | S | IS |
| X | 冲突 | 冲突 | 冲突 | 冲突 |
| IX | 冲突 | 兼容 | 冲突 | 兼容 |
| S | 冲突 | 冲突 | 兼容 | 兼容 |
| IS | 冲突 | 兼容 | 兼容 | 兼容 |
表级别的 AUTO-INC 锁
在使用 MySQL 过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值系统实现这种自动给AUTO_INCREMENT 修饰的列递增赋值的原理主要是两个：
- 采用 AUTO-INC 锁，也就是在执行插入语句时就在表级别加一个 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值，在该语句执行结束后，再把 AUTO-INC 锁释放掉。这样一个事务在持有 AUTO-INC 锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。
- 采用一个轻量级的锁，在为插入语句生成 AUTO_INCREMENT 修饰的列的值时获取一下这个轻量级锁，然后生成本次插入语句需要用到的AUTO_INCREMENT 列的值之后，就把该轻量级锁释放掉，并不需要等到整个插入语句执行完才释放锁。
说明
如果我们的插入语句在执行前不可以确定具体要插入多少条记录（无法预计即将插入记录的数量），比方说使用 INSERT ... SELECT、REPLACE ... SELECT 或者 LOAD DATA 这种插入语句，一般是使用 AUTO-INC 锁为 AUTO_INCREMENT 修饰的列生成对应的值。
如果插入语句在执行前就可以确定具体要插入多少条记录，比如关于表 t 的例子中，在语句执行前就可以确定要插入 2 条记录，那么一般采用轻量级锁的方式对 AUTO_INCREMENT 修饰的列进行赋值。这种方式可以避免锁定表，可以提升插入性能。
InnoDB 提供了一个称之为 innodb_autoinc_lock_mode 的系统变量来控制到底使用上述两种方式中的哪种来为 AUTO_INCREMENT 修饰的列进行赋值，当 innodb_autoinc_lock_mode 值为 0 时，一律采用 AUTO-INC 锁；当 innodb_autoinc_lock_mode 值为 2 时，一律采用轻量级锁；当innodb_autoinc_lock_mode 值为 1 时，两种方式混着来（也就是在插入记录数量确定时采用轻量级锁，不确定时使用 AUTO-INC 锁）。不过当 innodb_autoinc_lock_mode 值为 2 时，可能会造成不同事务中的插入语句为 AUTO_INCREMENT 修饰的列生成的值是交叉的，在有主从复制的场景中是不安全的。
MySQL5.7.X 中缺省为 1，MySQL8.0 中 缺省为2。
锁定读（Locking Reads）
也称当前读, 读取的是最新版本, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。
哪些是当前读呢？select lock in share mode (共享锁)、select for update (排他锁)、update (排他锁)、insert (排他锁)、delete (排他锁)、串行化事务隔离级别都是当前读。
锁定读的select语句：
- 共享锁（S）SELECT ... LOCK IN SHARE MODE;
- 排他锁（X）SELECT ... FOR UPDATE;
说明：
用SELECT ... LOCK IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人第这个记录进行UPDATE或者DELETE操作，但是如果当前事务也需要进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT ... FOR UPDATE方式获得排他锁。
InnoDB的行级锁
InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB行锁分为：
- Record lock:对索引项加锁。
- Gap lock: 对索引项之间的间隙、第一条记录前的间隙或最后一条记录后的间隙加锁。
- Next-key lock: 前两种的组合，对记录及其前面的间隙加锁。
InnoDB行锁实现意味着如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果和表锁一样。
创建表tab_no_index，开始没有索引：
mysql> create table tab_no_index(id int, name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
| session_1 | session_2 |
| mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_no_index where id=1; +------+------+ | id | name | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) | mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_no_index where id=2; +------+------+ | id | name | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) |
| mysql> select * from tab_no_index where id=1 for update; +------+------+ | id | name | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) | |
| mysql> select * from tab_no_index where id=2 for update; 等待 |
在没有索引的情况下，InnoDB会对表中所有的记录都加锁
创建tab_with_index表，id字段有普通索引：
mysql> create table tab_with_index(id int, name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.09 sec)
mysql> alter table tab_with_index add index id(id);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
| session_1 | session_2 |
| mysql> set autocommit=0; mysql> select * from tab_with_index where id=1; | mysql> set autocommit=0; mysql> select * from tab_with_index where id=2; |
| mysql> select * from tab_with_index where id=1 for update; +------+------+ | id | name | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.01 sec) | |
| mysql> select * from tab_with_index where id=2 for update; +------+------+ | id | name | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) |
当增加一个索引后，InnoDB就只锁定符合条件的行。
说明：
- 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽是访问不同行的记录，但是如果使用相同的索引键，是会出现锁冲突的。
- 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，不论是使用主键索引、唯一索引还是普通索引，InnoDB都会使用行锁来对数据加锁。
- 即便在条件中使用了索引字段，如果MySQL认为全表扫描效率更高，就会放弃使用索引，这种情况下会对所有记录加锁。
Next-key锁
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫"间隙"，InnoDB也会对这个间隙加锁。
| session_1 | session_2 |
| mysql> select @@transaction_isolation; mysql> set autocommit=0; | mysql> select @@transaction_isolation; mysql> set autocommit=0; |
| mysql> select * from tab_with_index where id=5 for update; Empty set (0.00 sec) | |
| mysql> insert into tab_with_index values(5,'5'); 阻塞等待 | |
| mysql> rollback; Query OK, 0 rows affected (0.00 sec) | |
| 由于session_1的rollback后释放了Next-key锁 可以获得锁并插入成功 mysql> insert into tab_with_index values(5,'5'); |
Next-key锁的意义：
默认情况下，InnoDB 以 REPEATABLE READ 隔离级别运行。在这种情况下，一方面时为了防止幻读，以满足相关隔离级别的要求，如果不使用间隙锁，在查询的时候，其他事务插入了一条不存在的记录，那么就会发生幻读，另一个方面时为了满足其恢复和复制的需要。
Next-key锁的不足：
在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，往往会造成严重的锁等待，因此，在实际开发中，尤其是并发插入较多的应用，尽量优化业务逻辑，使用相等条件来访问更新数据，避免使用范围条件。
获取InnoDB行锁争用情况
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁争用情况：
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.00 sec)
如果发现锁争用比较严重，如Innodb_row_lock_time_avg和Innodb_row_lock_waits的值比较高，可以通过查询information_schema数据库中innodb_locks表来查看锁情况（MySQL:8.0后innodb_locks表由performance_schema.data_locks表所代替，innodb_lock_waits表则由performance_schema.data_lock_waits表代替）。
mysql> select * from performance_schema.data_locks \G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 3858:1063
ENGINE_TRANSACTION_ID: 3858
THREAD_ID: 51
EVENT_ID: 19
OBJECT_SCHEMA: dongsq
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139816620571960
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 3858:2:13:3
ENGINE_TRANSACTION_ID: 3858
THREAD_ID: 51
EVENT_ID: 19
OBJECT_SCHEMA: dongsq
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_order_no
OBJECT_INSTANCE_BEGIN: 139816620569080
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 'DD000000001', 2
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 3858:2:8:3
ENGINE_TRANSACTION_ID: 3858
THREAD_ID: 51
EVENT_ID: 19
OBJECT_SCHEMA: dongsq
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139816620569424
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 3858:2:13:4
ENGINE_TRANSACTION_ID: 3858
THREAD_ID: 51
EVENT_ID: 19
OBJECT_SCHEMA: dongsq
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: idx_order_no
OBJECT_INSTANCE_BEGIN: 139816620569768
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 'DD000000002', 3
4 rows in set (0.00 sec)
查看事务加锁情况
可以通过show engine innodb status查看事务加锁的情况，不过一般情况下，看不到哪个事务对哪些记录加了那些锁，需要修改系统变量 innodb_status_output_locks（MySQL5.6.16 引入），缺省是 OFF。
show variables like 'innodb_status_output_locks';
mysql> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output_locks | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
set global innodb_status_output_locks = ON;
mysql> set global innodb_status_output_locks = ON;
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output_locks | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 where create_time >'2022-03-01 13:58:35'
-> and create_time < '2022-03-01 14:37:58' and order_status =1 order by expire_time desc for update;
+----+-------------+---------------------+---------------------+--------------+
| id | order_no | create_time | expire_time | order_status |
+----+-------------+---------------------+---------------------+--------------+
| 41 | DD000000040 | 2022-03-01 14:36:58 | 2022-03-01 14:37:58 | 1 |
| 40 | DD000000039 | 2022-03-01 14:35:58 | 2022-03-01 14:36:58 | 1 |
mysql> show engine innodb status\G;
上图红色方框中表示事务ID为3173 对dongsq库下t1表添加了一个表级意向独占锁
space id 2: 表空间为2
page no 11: 页编号为11
n bits 824: 表示这个索引记录锁结构上留有824个bit位
lock_mode X：存放的是一个 X 型的 next-key 锁
死锁
发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务，但在涉及外部锁或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决，这个参数并不是只能用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需要的锁而挂起，会占用大量计算机资源，通过设置合适的锁等待超时阈值，可以避免这种情况发生。
通常来说，死锁都是应用设计的问题，通过调整业务流程，数据库对象设计，事务大小，以及访问数据库SQL语句，绝大部分可以避免。下面列举几个避免造成死锁的例子：
1、在应用中如果不同的程序会并发存取多个表，应尽量约定以相同顺序来访问表
| session_1 | session_2 |
| mysql> set autocommit=0; Query OK, 0 rows affected (0.02 sec) | mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) |
| mysql> select order_no, create_time from t1 where order_no='DD000000001' for update; +-------------+---------------------+ | order_no | create_time | +-------------+---------------------+ | DD000000001 | 2022-03-01 13:57:35 | +-------------+---------------------+ 1 row in set (0.01 sec) | |
| mysql> INSERT INTO dongsq.t2(order_no, create_time, expire_time, order_status)VALUES('DD000681780', '2023-06-18 02:19:06', '2023-06-18 02:20:06', 0); Query OK, 1 row affected (0.00 sec) | |
| mysql> INSERT INTO dongsq.t2(order_no, create_time, expire_time, order_status)VALUES('DD000681780', '2023-06-18 02:19:06', '2023-06-18 02:20:06', 0); 等待 | |
| mysql> select order_no, create_time from t1 where order_no='DD000000001' for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
2、在程序中以批量处理数据的时候，如果事先对数据排序，保证每个线程按照固定的顺序来处理，也可以降低出现死锁的可能。
| session_1 | session_2 |
| mysql> select order_no, create_time from t1 where order_no='DD000000001' for update; | |
| mysql> select order_no, create_time from t1 where order_no='DD000000002' for update; +-------------+---------------------+ | order_no | create_time | +-------------+---------------------+ | DD000000002 | 2022-03-01 13:58:39 | +-------------+---------------------+ 1 row in set (0.00 sec) | |
| mysql> select order_no, create_time from t1 where order_no='DD000000002' for update; 等待 | |
| mysql> select order_no, create_time from t1 where order_no='DD000000001' for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
3、在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功，如果都试图插入一条，就会出现死锁，这种情况下，将隔离级别改成READ COMMITED，就可以避免问题。
| session_1 | session_2 |
| mysql> select @@transaction_isolation; mysql> set autocommit=0; | mysql> select @@transaction_isolation; mysql> set autocommit=0; |
| 对不存在的记录加for update锁 mysql> select order_no, create_time from t2 where order_no='DD000681782' for update; | |
| 对不存在的记录加for update锁 mysql> select order_no, create_time from t2 where order_no='DD000681782' for update; | |
| mysql> INSERT INTO dongsq.t2(order_no, create_time, expire_time, order_status)VALUES('DD000681782', '2023-06-18 02:19:06', '2023-06-18 02:20:06', 0); | |
| mysql> INSERT INTO dongsq.t2(order_no, create_time, expire_time, order_status)VALUES('DD000681782', '2023-06-18 02:19:06', '2023-06-18 02:20:06', 0); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
什么时候使用表锁
对于InnoDB表，在绝大多数情况下都应使用行级锁，因为事务和行锁往往时我们选择InnoDB引擎的理由。但在个别特殊事务中，也可考虑使用表级锁
- 第一种情况：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率底，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
- 第二种情况：事务涉及多个表，比较复杂，很可能引起死锁，造成大量的事务回滚，这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少因事务回滚带来的开销。
当然这两种情况不能太多，不然就应该考虑MyISAM引擎了，在InnoDB下使用表锁要注意以下两点：
- 使用LOCK TABLES 虽然可以给InnoDB加表锁，但是表锁不是由InnoDB引擎层管理的，而是由上一层---MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1(默认设置)时，InnoDB层才知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则，InnoDB将无法自动检测并处理这种死锁。
- 在使用LOCK TABLES对InoDB表加锁时需要注意，要将autocommit设为0，否则MySQL不会给表加锁，事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含的提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表锁，必须用UNLOCK TABLES 释放表锁，例如，可以这样做：
SET AUTOCOMMMIT=0;
LOCK TABLES t1 WRITE, t2 READ...
...
...
COMMIT;
UNLOCK TABLES;锁结构
所谓的锁其实是一个内存中的结构，在事务执行前本来是没有锁的，也就是说一开始是没有锁结构和记录进行关联的，当一个事务想对这条记录做改动时，首先会看看内存中有没有与这条记录关联的锁结构，当没有的时候就会在内存中生成一个锁结构与之关联。比如说事务 T1 要对记录做改动，就需要生成一个锁结构与之关联。锁结构里至少要有两个比较重要的属性：
trx 信息：代表这个锁结构是哪个事务生成的。
is_waiting：代表当前事务是否在等待。
当事务 T1 改动了条记录后，就生成了一个锁结构与该记录关联，因为之前没有别的事务为这条记录加锁，所以 is_waiting 属性就是 false，加锁成功，然后就可以继续执行操作了。
在事务 T1 提交之前，另一个事务 T2 也想对该记录做改动，先去看看有没有锁结构与这条记录关联，发现有一个锁结构与之关联后，然后也生成了一个锁结构与这条记录关联，不过锁结构的 is_waiting 属性值为 true，表示当前事务需要等待
在事务 T1 提交之后，就会把该事务生成的锁结构释放掉，然后看看还有没有别的事务在等待获取锁，发现了事务 T2 还在等待获取锁，所以把事务 T2 对应的锁结构的 is_waiting 属性设置为 false，然后把该事务对应的线程唤醒，让它继续执行，此时事务 T2 就算获取到锁了。
对一条记录加锁的本质就是在内存中创建一个锁结构与之关联，那么一个事务对多条记录加锁时，是不是就要创建多个锁结构呢。比如 SELECT * FROM t1 LOCK IN SHARE MODE;
很显然，这条语句需要为 t1表中的所有记录进行加锁。那么，是不是需要为每条记录都生成一个锁结构呢?其实理论上创建多个锁结构没有问题，反而更容易理解。但是如果一个事务要获取 10,000 条记录的锁，要生成 10,000 个这样的结构，不管是执行效率还是空间效率来说都是很不划算的，所以实际上，并不是一个记录一个锁结构。
锁所在的事务信息：无论是表级锁还是行级锁，一个锁属于一个事务，这里记载着该锁对应的事务信息。
索引信息：对于行级锁来说，需要记录一下加锁的记录属于哪个索引。表锁/行锁信息:表级锁结构和行级锁结构在这个位置的内容是不同的。具体表现为表级锁记载着这是对哪个表加的锁，还有其他的一些信息；而行级锁记载了记录所在的表空间、记录所在的页号、区分到底是为哪一条记录加了锁的数据结构。
锁模式：锁是 IS，IX，S，X 中的哪一种。
锁类型：表锁还是行锁，行锁的具体类型。
其他一些和锁管理相关的数据结构，比如哈希表和链表等。
基本上来说，同一个事务里，同一个数据页面，同一个加锁类型的锁会保存在一起。
1505
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
