MySQL之InnoDB的锁类型与锁原理

时间:2023-02-09 20:01:03

(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:从系统启动到现在锁定的总时间长度,单位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

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

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