锁
锁是用来解决事务对数据的并发访问的问题的。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 InnoDB锁类型及锁原理实例解析的详细内容,更多关于MySQL InnoDB锁类型锁原理的资料请关注我们其它相关文章!