MySQL InnoDB锁类型及锁原理实例解析

MySQL
287
0
0
2023-06-24
MySQL参数调优实例探究讲解
上一篇
MySQL 中的排序在底层是怎样实现的呢？
下一篇
标签   MySQL锁
目录
  • 锁
  • 共享锁
  • 排他锁
  • 意向锁
  • 记录锁
  • 间隙锁
  • 临键锁
  • 死锁
  • 死锁产生条件
  • 行锁发生死锁
  • 表锁发生死锁
  • 锁的释放
  • 事务阻塞
  • 死锁的避免
  • 锁的日志
  • 行锁的原理
  • 不带任何索引的表
  • 带主键索引的表
  • 带唯一索引的表
  • 结论
  • 1.表必定有索引
  • 2.唯一索引数据行加锁，主键索引同样被锁

锁

锁是用来解决事务对数据的并发访问的问题的。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:从系统启动到现在锁定的总时间长度，单位ms
Innodb_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

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

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

MySQL参数调优实例探究讲解
上一篇
MySQL 中的排序在底层是怎样实现的呢？
下一篇