MySQL之InnoDB的锁类型与锁原理

• 锁
• • 共享锁
  • 排他锁
  • 意向锁
  • 记录锁
  • 间隙锁
  • 临键锁
• 死锁
• • 死锁产生条件
  • 行锁发生死锁
  • 表锁发生死锁
  • 锁的释放
  • 事务阻塞
  • 死锁的避免
  • 锁的日志
• 行锁的原理
• • 不带任何索引的表
  • 带主键索引的表
  • 带唯一索引的表
  • 结论

锁

锁是用来解决事务对数据的并发访问的问题的。MyISAM支持表锁，InnoDB同时支持表锁和行锁。

表加锁语法：

lock tables xxx read;lock tables xxx write;unlock tables;

锁分类

两个行级别的锁：

共享锁Shared Locks、排他锁Exclusive Locks

两个表级别的锁：

意向共享锁、意向排他锁

锁的算法:

三个Record Locks、Gap Locks、Next-Key Locks，把它们叫做锁的算法，也就是分别在什么情况下锁定什么范围。

插入意向锁：

是一个特殊的间隙锁。间隙锁不允许插入数据，但是插入意向锁允许多个事务同时插入数据到同一个范围。比如（4,7),一个事务插入5,一个事务插入6，不会发生锁等待。

自增锁：

是一种特殊的表锁，用来防止自增字段重复，数据插入以后就会释放，不需要等到事务提交才释放。如果需要选择更快的自增值生成速度或者更加连续的自增值，就要通过修改自增锁的模式改变。

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.01 sec)

0:traditonal：每次都会产生表锁1:consecutive：会产生一个轻量锁，simple insert会获得批量的锁，保证连插入，默认值2:interleaved：不会锁表，来一个处理一个，并发最高

共享锁

共享锁是一个行级别的锁，它叫Shared Locks获取一行数据的读锁以后，可以用来读取数据，所以它也叫做读锁注意不要在加上读锁以后去写数据，不然可能会出现死锁的情况多个事务可以共享一把读锁。

作用：

因为共享锁会阻塞其他事务的修改，所以可以用在不允许其他事务修改数据的情况

给一行数据手动加上一把读锁：

SELECT SQL LOCK IN SHARE MODE;

释放锁：

只要事务结束，锁就会自动释放锁

验证

验证共享锁是否可以重复获取

事务1：开启事务，执行查询，不提交事务

BEGIN;SELECT * FROM tableName WHERE id=1 LOCK IN SHARE MODE;

事务2：开启事务，执行查询，正常查询

BEGIN;SELECT * FROM tableName WHERE id=1 LOCK IN SHARE MODE;COMMIT;

事务3：开启事务，执行修改，遇到阻塞

BEGIN;UPDATE tableName set column1='test' WHERE id=1;

排他锁

排他锁是一个行级别的锁，叫做Exclusive Locks，它是用来操作数据的，所以又叫做写锁。

只要一个事务获取了一行数据的排它锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。

加锁方式

1.自动加排他锁：在操作数据的时候，包括增删改，都会默认加上一个排它锁。2.手动加锁：用一个FOR UPDATE给一行数据加上一个排它锁，这个无论是在代码里还是操作数据的工具里，都比较常用。

验证

验证排它锁的特性

事务1：开启事务，执行查询，不提交事务

BEGIN;UPDATE tableName set column1='test' WHERE id=1;

事务2：开启事务，执行查询，出现阻塞

BEGIN;SELECT * FROM tableName WHERE id=1 LOCK IN SHARE MODE;

事务3：开启事务，执行查询，出现阻塞

BEGIN;SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事务4：开启事务，执行查询，出现阻塞

BEGIN;UPDATE tableName set column1='test' WHERE id=1;

意向锁

意向锁是表级别的锁，是由数据库自己维护的，分为：意向共享锁、意向排他锁

当给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁当给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁

反过来：

如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加上了排他锁

验证

事务1：开启事务，执行查询，并手动加上排他锁，事务不提交

BEGIN;SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事务2：开启事务，给表加上锁，出现阻塞

BEGIN;LOCK TABLES tableName WRITE;

释放表锁

unlock tables;

记录锁

当对唯一索引和主键索引使用等值查询，精准匹配一条记录的时候，使用的就是记录锁。

间隙锁

当查询记录不存在，没有命中任何一行数据，无论是用等值查询还是范围查询，它使用的都是间隙锁。

间隙锁主要是阻塞插入insert。相同的间隙锁之间不冲突。

临键锁

当使用范围查询，不仅仅命中Record记录，还包含间隙，在这种情况下使用的是临键锁，它是MySQL里面默认的行锁算法，相当于记录锁加上间隙锁。

唯一性索引，等值查询匹配到一条记录的时候，退化成记录锁。没有匹配到任何记录的时候，退化成间隙锁。

死锁

死锁的发生需要满足一定的条件，在发生死锁时，InnoDB一般都能通过算法（wait-for graph)自动检测到。

死锁产生条件

同一时刻只能有一个事务持有这把锁其他事务需要在这个事务释放锁之后才能获取锁，而不可以强行剥夺当多个事务形成等待环路的时候，即发生死锁说到底就是因为锁本身是互斥的

行锁发生死锁

模拟1

事务1

BEGIN;1.SELECT * FROM tableName WHERE id=1 FOR UPDATE;3.UPDATE tableName SET column1='test'  WHERE id=2; 

事务2

BEGIN;2.DELETE FROM tableName WHERE id=2;4.DELETE FROM tableName WHERE id=1;  

在第一个事务中，检测到死锁，马上退出释放锁，第二个事务获得锁，不需要等待50秒

1213-Deadlock found when trying to get lock; try restarting transaction

模拟2

事务1

BEGIN;1.SELECT * FROM tableName WHERE id=1 LOCK in SHARE MODE;3.UPDATE tableName SET column1='aa'  WHERE id=1; 

事务2

BEGIN;2.SELECT * FROM tableName WHERE id=1 LOCK in SHARE MODE;4.UPDATE tableName SET column1='bb'  WHERE id=1;

在第二个事务中，检测到死锁，马上退出释放锁，第一个事务获得锁，不需要等待50秒

1213-Deadlock found when trying to get lock; try restarting transaction

表锁发生死锁

BEGIN;1.LOCK TABLES tableName1 WRITE;3.LOCK TABLES tableName2 WRITE; 阻塞，直到4执行，释放tableName1锁，获取tableName2的锁

BEGIN;2.LOCK TABLES tableName2 WRITE;4.LOCK TABLES tableName1 WRITE; 锁机制检测到死锁，自动释放锁，获取tableName1

锁的释放

死锁在事务结束（commit、rollback）、或客户端断开连接时释放锁。

事务阻塞

如果一个事务一直未释放锁，其他事务会被阻塞50秒，通过参数控制获取锁的等待时间，默认是50秒。

mysql> show VARIABLES like 'innodb_lock_wait_timeout' ;
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

死锁的避免

在程序中，操作多张表时，尽量以相同的顺序来访问（避免形成等待环路）批量操作单张表数据的时候，先对数据进行排序（避免形成等待环路）申请足够级别的锁，如果要操作数据，就申请排它锁尽量使用索引访问数据，避免没有where条件的操作，避免锁表如果可以，大事务化成小事务使用等值查询而不是范围查询查询数据，命中记录，避免间隙锁对并发的影响

锁的日志

查看行锁信息

mysql> show status like 'innodb_row_lock_%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 436657 |
| Innodb_row_lock_time_avg      | 15057  |
| Innodb_row_lock_time_max      | 51578  |
| Innodb_row_lock_waits         | 29     |
+-------------------------------+--------+
5 rows in set (0.00 sec)

Innodb_row_lock_current_waits:当前正在等待锁定的数量Innodb_row_lock_time:从系统启动到现在锁定的总时间长度，单位msInnodb_row_lock_time_avg:每次等待所花平均时间Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间Innodb_row_lock_waits:从系统启动到现在总共等待的次数

查看当前运行的所有事务，还有具体的语句

select * from information_schema.INNODB_TRX;

当前出现的锁

select * from information_schema.INNODB_LOCKS;

锁等待的对应关系

select * from information_schema.INNODB_LOCK_WAITS;

mysql> select * from information_schema.INNODB_TRX;
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id          | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                                     | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 517172          | LOCK WAIT | 2022-10-09 22:28:59 | 517172:809:3:6        | 2022-10-09 22:33:20 |          4 |                 185 | SELECT * FROM tableName WHERE id=1 FOR UPDATE | starting index read |                 1 |                 2 |                4 |                  1136 |               2 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 329261206788832 | RUNNING   | 2022-10-09 22:28:12 | NULL                  | NULL                |          2 |                 203 | NULL                                          | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+-----------------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+-----------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.00 sec)mysql> select * from information_schema.INNODB_LOCKS;
+-------------------------+-----------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| lock_id                 | lock_trx_id     | lock_mode | lock_type | lock_table         | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-------------------------+-----------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
| 517172:809:3:6          | 517172          | X         | RECORD    | `mydb`.`tableName` | PRIMARY    |        809 |         3 |        6 | 1         |
| 329261206788832:809:3:6 | 329261206788832 | S         | RECORD    | `mydb`.`tableName` | PRIMARY    |        809 |         3 |        6 | 1         |
+-------------------------+-----------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+-------------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id        |
+-------------------+-------------------+-----------------+-------------------------+
| 517172            | 517172:809:3:6    | 329261206788832 | 329261206788832:809:3:6 |
+-------------------+-------------------+-----------------+-------------------------+
1 row in set, 1 warning (0.00 sec)

开启标准监控和锁监控，得到更加详细的锁信息

set GLOBAL innodb_status_output=ON;set GLOBAL innodb_status_output_locks=ON;

行锁的原理

在InnoDB中，行锁是通过锁住索引来实现的。因此，当一个事务锁住一行数据的时候，其他的事务不能操作这一行数据，是因为它锁住了这行数据对应的索引。

不带任何索引的表

不带任何索引的表中，在没有索引或者没有用到索引的情况下，会锁住整张表

事务1：开启事务，执行查询，不提交事务

BEGIN;SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事务2：开启事务，执行修改，进入阻塞

BEGIN;UPDATE tableName SET column1='test'  WHERE id=3;

事务3：开启事务，执行查询，进入阻塞

BEGIN;SELECT * FROM tableName WHERE id=2 FOR UPDATE;

带主键索引的表

在带主键索引的表中，使用相同id加锁会冲突，使用不同id加锁，可以成功

事务1：开启事务，执行查询，不提交事务

BEGIN;SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事务2：开启事务，执行查询，进入阻塞

BEGIN;SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事务3：开启事务，执行修改、查询，正常执行

BEGIN;UPDATE tableName SET column1='test'  WHERE id=3;SELECT * FROM tableName WHERE id=3 FOR UPDATE;

带唯一索引的表

在带唯一索引的表中，使用相同唯一值会加锁会冲突，使用不同唯一值加锁，可以成功。

事务1：开启事务，执行查询，不提交事务

BEGIN;SELECT * FROM tableName WHERE column1='column1' FOR UPDATE;

事务2：开启事务，执行查询，进入阻塞

BEGIN;SELECT * FROM tableName WHERE column1='column1' FOR UPDATE;

事务3：开启事务，执行查询，查询的是上述事务操作的加锁的那条数据，进入阻塞

BEGIN;SELECT * FROM tableName WHERE id=1 FOR UPDATE;

事务4：开启事务，执行查询，正常执行

BEGIN;SELECT * FROM tableName WHERE column1='column2' FOR UPDATE;SELECT * FROM tableName WHERE id=2 FOR UPDATE;

结论

1.表必定有索引

锁是基于索引进行锁数据的，因此，一张表必定有索引

如果定义主键（PRIMARYKEY)，那么InnoDB会选择主键作为聚集索引如果没有显式定义主键，则 InnoDB 会选择第一个不包含有NULL值的唯一索引作为主键索引如果也没有这样的唯一索引，则 InnoDB 会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增

一张不带任何索引的表，造成锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住。

2.唯一索引数据行加锁，主键索引同样被锁

聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据。

辅助索引，也叫非聚集索引，和聚集索引相比，叶子节点中并不包含行记录的全部数据，而是包含二级索引和主键的值。例如column1的索引和主键id值1

主键索引里面除了索引之外，还存储了完整的数据。所以通过辅助索引锁定一行数据的时候，它跟检索数据的步骤是一样的，会通过主键值找到主键索引，因此会锁定。

本质上是因为锁定的是同一行数据，所以会相互冲突。