行级锁是施加在索引行数据上的锁, 比如 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE 语句是在 t.c1=10 的索引行上增加锁 , 来阻止其他事务对对应索引行的 insert/update/delete 操作.
当一个 InnoDB 表没有任何索引时, 则行级锁会施加在隐含创建的聚簇索引上, 所以说当一条 sql 没有走任何索引时, 那么将会在每一条聚集索引后面加 X(排它)锁, 这个类似于表锁, 但原理上和表锁应该是完全不同的
1 2 |
int,name varchar(10)); mysql> insertinto temp values(1,'a'),(2,'b'),(3,'c'); |
开启两个会话链接, 链接同一个数据库, 模拟排他锁
+ View Code?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | session1:
mysql>set
mysql> update tempset name='aa' where >
session2:
mysql> update tempset name='bb'where > session1 ##commit 释放锁 mysql> commit;
session2
|
看看事务的详细信息:
+ View Code?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | session1: set autocommit=0; update tempset name='a' where > session2: select
from
trx_rows_locked: 4 1 rowin set (0.00 sec) update tempset name='b' where > session3: # 查看锁等待 select *from
# 查看锁状态 select
from
# 查看锁的信息 select
from
|
看看该事务的锁信息
lock_type : RECORD 行级锁 lock_index:GEN_CLUSE_INDEX 聚集索引
给 temp 表加一个主键索引在看看
alter table temp add primary key(id); ## 增加索引之后, 记得把前面事务提交
1 2 3 4 5 6 7 8 9 10 11 12 | session1: update 加排它锁 set autocommit=0; update tempset name='a' where > session2: update tempset name='b' where >
commit; |
间隔锁
当 我们用范围条件而不是相等条件检索数据, 并请求共享或排他锁时, InnoDB 会给符合条件 的已有数据记录的索引项加锁; 对于键值在条件范围内但并不存在的记录, 叫做 "间隙(GAP)", InnoDB 也会对这个 "间隙" 加锁
间隔锁是施加在索引记录之间的间隔上的锁, 锁定一个范围的记录, 但不包括记录本身, 比如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE 语句, 尽管有可能对 c1 字段来说当前表里没有 = 15 的值, 但还是会阻止 = 15 的数据的插入操作, 是因为间隔锁已经把索引查询范围内的间隔数据也都锁住了
间隔锁的使用只在部分事务隔离级别才是生效的
间隔锁只会阻止其他事务的插入操作
gap lock 的前置条件: 1 事务隔离级别为 REPEATABLE-READ, innodb_locks_unsafe_for_binlog 参数为 0, 且 sql 走的索引为非唯一索引(无论是等值检索还是范围检索)
2 事务隔离级别为 REPEATABLE-READ, innodb_locks_unsafe_for_binlog 参数为 0, 且 sql 是一个范围的当前读操作, 这时即使不是非唯一索引也会加 gap lock
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
set autocommit=0; mysql> update temp2set name='abc' where id between 4 and 6;
session2: insertinto temp2 values(4,'d');
session1: 释放锁 commit; session2:
|
例子如果链接 1 的 update 语句是 update temp set name='abc' where id>4; 而链接 2 的插入数据的>
Next-key 锁
在默认情况下, MySQL 的事务隔离级别是可重复读, 并且 innodb_locks_unsafe_for_binlog 参数为 0, 这时默认采用 next-key locks. 所谓 Next-Key Locks, 就是记录锁和间隔锁的结合, 即除了锁住记录本身, 还要再锁住索引之间的间隙.
插入意图锁
插入意图锁是在插入数据时首先获得的一种间隔锁, 对这种间隔锁只要不同的事务插入的数据位置是不一样的, 虽然都是同一个间隔, 也不会产生互斥关系
比如有一个索引有 4 和 7 两个值, 如果两个事务分别插入 5 和 6 两个值时, 虽然两个事务都会在索引 4 和 7 之间施加间隔锁, 但由于后续插入的数值不一样, 所以两者不会互斥
比如下例中事务 A 对索引 > 100 的值施加了排他间隔锁, 而事务 B 在插入数据之前就试
图先施加插入意图锁而必须等待
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
int
# 开启事务 A START TRANSACTION;
+-----+ | id | +-----+ | 102 | +-----+ 1 rowin set (0.00 sec)
START TRANSACTION;
|
可以通过 show engine innodb status 命令查看插入意向锁被阻止
自增锁
自增锁是针对事务插入表中自增列时施加的一种特殊的表级锁, 即当一个事务在插入自增数据时, 另一个事务必须等待前一个事务完成插入, 以便获得顺序的自增值
参数 innodb_autoinc_lock_mode 可以控制自增锁的使用方法
InnoDB 锁相关系统变量
查看当前系统隔离级别
1 2 3 4 5 6 | show variables like'tx_isolation';
|
查看是否开启自动提交
1 2 | show variables like'autocommit'; show variables like
; |
查看 innodb 事务等待事务的超时时间(秒)
1 2 3 4 5 6 |
;
|
innodb_locks_unsafe_for_binlog 参数用来控制 innodb 中是否允许间隔锁, 默认是 OFF 代表允许间隔锁, 设置成 ON 则代表不使用间隔锁, 只是使用行级锁
+ View Code?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
;
on 选项 • 链接 1: • mysql>set autocommit=0;
set name='abc' where id>=4; • 链接 2: • mysql>set autocommit=0;
• mysql> insertinto
|
InnoDB 事务隔离级别
InnoDB 存储引擎提供了四种事务隔离级别, 分别是:
READ UNCOMMITTED: 读取未提交内容
READ COMMITTED: 读取提交内容
REPEATABLE READ: 可重复读, 默认值.
SERIALIZABLE: 串行化
可以通过 - transaction-isolation 参数设置实例级别的事务隔离级别, 也可以通过 set [session/global] transaction isolationlevel 语句修改当前数据库链接或者是后续创建的所有数据库链接的事务隔离级别
1 2 3 4 5 6 7 |
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } |
REPEATABLE READ: 可重复读, 默认值. 表明对同一个事务来说第一次读数据时会创建快照, 在事务结束前的其他读操作 (不加锁) 会获得和第一次读相同的结果. 当读操作是加锁的读语句(select ... for update 或者 lock in share mode), 或者 update 和 delete 语句时, 加锁的方式依赖于语句是否使用唯一索引访问唯一值或者范围值
当访问的是唯一索引的唯一值时, 则 InnoDB 会在索引行施加行锁当访问唯一索引的范围值时, 则会在扫描的索引行上增加间隔锁或者 next-key 锁以防止其他链接对此范围的插入
READ COMMITTED: 读取提交内容. 意味着每次读都会有自己最新的快照. 对于加锁读语句(select ... for update 和 lock in share mode), 或者 update, delete 语句会在对应的行索引上增加锁, 但不像可重复读一样会增加间隔锁, 因此其他的事务执行插入操作时如果是插入非索引行上的数值, 则不影响插入
由于该隔离级别是禁用间隔锁的, 所以会导致幻读的情况如果是使用此隔离级别, 就必须使用行级别的二进制日志此隔离级别还有另外的特点: 对于 update 和 delete 语句只会在约束条件对应的行上增加锁对 update 语句来说, 如果对应的行上已经有锁, 则 InnoDB 会执行半一致读的操作, 来确定 update 语句对应的行在上次 commit 之后的数据是否在锁的范围, 如果不是, 则不影响 update 操作, 如果是, 则需要等待对应的锁解开
例:
+ View Code?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | session1: # 设置为读提交 mysql>set
mysql>set autocommit=0; mysql> update tempset name='aaa' where > mysql>select *from temp; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | b | | 3 | c | | 4 | d | +----+------+ session2: mysql>set
mysql>set autocommit=0; mysql>select *from temp; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | session1:
commit; session2: mysql>select *from temp; +----+------+ | id | name | +----+------+ | 1 | aaa | | 2 | b | | 3 | c | | 4 | d | +----+------+
mysql> commit; |
比如如下情况
1 2 3 |
COMMIT; |
表中并没有任何索引, 所以会使用隐藏创建的聚簇索引来施加行级锁当第一个链接执行修改:
1 2 3 4 5 6 |
SET autocommit = 0;
SET autocommit = 0;
|
对可重复读隔离级别来说, 第一个事务的修改会在每行记录上都增加排他锁, 并且直到事务结束后锁才会释放
而第二个事务会一直等待前面事务的锁被释放后才能执行
READ UNCOMMITTED: 读取未提交内容, 所读到的数据可能是脏数据
SERIALIZABLE: 串行化, 此隔离级别更接近于可重复读这个级别, 只是当 autocommit 功能被禁用后, InnoDB 引擎会将每个 select 语句隐含的转化为 select ... lock in share mode
总结: 在数据库操作中, 为了有效保证并发读取数据的正确性, 提出的事务隔离级别. 我们的数据库锁, 也是为了构建这些隔离级别存在的.
隔离级别 | 脏读 | 不可重复读 | 幻读 |
可重复读 | 不可能 | 不可能 | 可能 |
提交读 | 不可能 | 可能 | 可能 |
未提交读 | 可能 | 可能 | 可能 |
串行化 | 不可能 | 不可能 | 不可能 < br ztid="921" ow="0" oh="0"> |
未提交读(Read Uncommitted): 允许脏读, 也就是可能读取到其他会话中未提交事务修改的数据
提交读(Read Committed): 只能读取到已经提交的数据. Oracle 等多数数据库默认都是该级别
可重复读(Repeated Read): 可重复读. 在同一个事务内的查询都是事务开始时刻一致的, InnoDB 默认级别. 在 SQL 标准中, 该隔离级别消除了不可重复读, 但是还存在幻象读
串行读(Serializable): 完全串行化的读, 每次读都需要获得表级共享锁, 读写相互都会阻塞
Autocommit/commit/rollback
当设置 autocommit 属性开启时, 每个 SQL 语句都会隐含成为独立的事务. 默认情况下 autocommit 属性是开启的, 也就意味着当每个 SQL 语句最后执行结果不返回错误时都会执行 commit 语句, 当返回失败时会执行 rollback 语句
而当 autocommit 属性开启时, 可以通过执行 start transaction 或者 begin 语句来显示的开启一个事务, 而事务里可以包含多个 SQL 语句, 最终事务的结束是由 commit 或者 rollback 来终结
而当在数据库链接里执行 set autocommit=0 代表当前数据库链接禁止自动提交, 事务的终结由 commit 或者 rollback 决定, 同时也意味着下一个事务的开始
如果一个事务在 autocommit=0 的情况下数据库链接退出而没有执行 commit 语句, 则这个事务会回滚
一些特定的语句会隐含的终结事务, 就好比是执行了 commit 语句
commit 语句代表将此事务的数据修改永久化, 并对其他事务可见, 而 rollback 则代表将此事务的数据修改回滚
commit 和 rollback 都会把当前事务执行所施加的锁释放
当使用多语句事务时, 如果全局的 autocommit 属性是开启的, 则开始此事务的方式可以使 set autocommit=0 将当前链接的属性关闭, 最后执行 commit 和 rollback; 或者是显示的使用 start transaction 语句开启事务
+ View Code?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
on.
int,name varchar(48));
'Heikki');
• mysql> COMMIT;
'John');
'Paul');
'Heikki';
• | a | b |
• | 10 | Heikki | |
一致读
在默认的隔离级别下一致读是指 InnoDB 在多版本控制中在事务的首次读时产生一个镜像, 在首次读时间点之前其他事务提交的修改可以读取到, 而首次读时间点之后其他事务提交的修改或者是未提交的修改都读取不到唯一例外的情况是在首次读时间点之前的本事务未提交的修改数据可以读取到在读取提交数据隔离级别下, 一致读的每个读取操作都会有自己的镜像
一致读操作不会施加任何的锁, 所以就不会阻止其他事务的修改动作
在下面的例子中, 链接 A 对链接 B 所做的修改, 只有在它的事务和链接 B 的事务都提交的情况下才能看到
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT * FROM t; emptyset
SELECT * FROM t; emptyset COMMIT; SELECT * FROM t; emptyset COMMIT; SELECT * FROM t;
| 1 | 2 |
|
首次读时间点之前的本事务未提交的修改数据可以读取到在读取提交数据隔离级别下, 一致读的每个读取操作都会有自己的镜像例 一直读每个读取操作前自己的镜像
+ View Code?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
mysql>set autocommit=0;
mysql> update tempset name='aaa' ;
commit;
mysql>select *from temp; +----+---------+ | id | name | +----+---------+ | 1 | 1111111 | | 2 | 1111111 | | 3 | 1111111 | | 4 | 1111111 | +----+---------+
mysql> update tempset name='bbb';
mysql> commit;
mysql>select *from temp; +----+---------+ | id | name | +----+---------+ | 1 | 1111111 | | 2 | 1111111 | | 3 | 1111111 | | 4 | 1111111 | +----+---------+
mysql>select *from temp; +----+------+ | id | name | +----+------+ | 1 | bbb | | 2 | bbb | | 3 | bbb | | 4 | bbb | +----+------+ |
一致读在某些 DDL 语句下不生效: 碰到 drop table 语句时, 由于 InnoDB 不能使用被 drop 的表, 所以无法实现一致读碰到 alter table 语句时, 也无法实现一致读
当碰到 insert into... select, update ... select 和 create table ... select 语句时, 在默认的事务隔离级别下, 语句的执行更类似于在读取提交数据的隔离级别下
加锁读操作
当在一个事务中在读操作结束后会执行 insert 和 update 操作时, 普通的读操作无法阻止其他事务对相同数据执行修改操作,
所以 InnoDB 提供了两种在读操作时就增加锁的方式
select ... lock in share mode: 在读取的行数据上施加共享锁, 其他的事务可以读相同的数据但无法修改; 如果在执行此语句时有其他事务对相同的数据已经施加了锁, 则需要等待事务完结释放锁
select ... for update: 和 update 操作一样, 在涉及的行上施加排他锁, 并阻止任何其他事务对涉及行上的修改操作, 以及加锁读操作, 但不会阻止对涉及行上的一般读 (不加锁) 操作
同样, 锁的释放也是在事务提交或者回滚之后
比如在子表中插入一行数据, 要确保对应的列在父表中有值, 通过一般的读操作先查父表有值然后再插入的方法是不保险的, 因为在读操作和插入操作之间就有可能其他事务会将父表的数据修改掉. 那保险的做法是在查询父表是用加锁读的方式, 比如: SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
再比如当表中有一个行数计数字段时, 使用一致读和 lock in share mode 都有可能导致重复错误数据出现, 因为有可能两个事务会读到相同的值, 在这种情况下就要使用 select ... for update 语句保证一个事务在读时, 另一个事务必须等待
SQL 语句对应的锁
加锁读, 修改和删除 SQL 语句都会在索引扫描过的每一行增加锁, 也就是说不光是在 where 条件限制的索引行上增加锁, 也会对扫描到的间隔增加间隔锁
如果 SQL 语句是使用二级索引查找数据而且施加的是排他锁, 则 InnoDB 也会在对应的聚簇索引行上施加锁
如果 SQL 语句没有任何索引可以使用, 则 MySQL 需要扫描全表数据, 而每行数据都会被施加锁, 所以一个良好的习惯是为 InnoDB 添加合适的索引
针对不同的语句, InnoDB 会施加不同的锁: Select...from 语句属于一致性读, 在默认情况下不施加任何的锁, 除非在可串行化隔离级别下, 会施加共享 next-key 锁在扫描的索引行上, 当碰到使用唯一索引查找唯一值时只在唯一值上施加锁
Select...lock in share mode 语句会在索引扫描行上施加共享 next-key 锁, 除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁
Select...for update 语句会对扫描索引的行上施加排他 next-key 锁, 除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁
Update 语句会对扫描索引的行上施加排他 next-key 锁, 除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁.
Delete 语句会对扫描索引的行上施加排他 next-key 锁, 除非是当碰到使用唯一索引查找唯一值时只在唯一值上施加锁
Insert 语句会对索引扫描的行上施加锁, 但不是 next-key 锁, 所以不会阻止其他事务对该行值前的间隔上插入数据
Insert into T select...from S 语句会对插入到 T 表的行施加排他锁(非间隔锁), 而在默认隔离级别下会对访问的 S 表上的行施加共享 next-key 锁
当表上有外键约束时, 对任何的 insert,update 和 delete 操作都会在需要检查外键约束的行上施加共享行锁
Lock table 语句是施加表级锁
幻读
幻读问题发生在同一个事务中当相同的读操作在前后两次读数据时返回不同的结果集. 比如在表的 ID 字段上有一个索引, 当希望对 ID>100 的数据进行后续修改时, 我们会使用如下的语句: SELECT * FROM child WHERE id> 100 FOR UPDATE, 而如果表里目前只有 90 和 102 两个值时, 如果没有间隔锁锁住 90 到 102 之间的间隔, 则其他的事务会插入比如 101 这个值, 这样的话在第二次读数据时就会返回三行记录而导致幻读
为了阻止幻读情况的发生, InnoDB 使用了一种方法 next-key 锁将索引行锁和间隔锁合并在一起. InnoDb 会在索引扫描的行上施加行级共享锁或者排他锁, 而 next-key 锁也会在每个索引行之前的间隔上施加锁, 会导致其他的 session 不能在每个索引之前的间隔内插入新的索引值
间隔锁会施加在索引读碰到的行数据上, 所以对上例来说为了阻止插入任何 > 100 的值, 也会将最后扫描的索引值 102 之前的间隔锁住
InnoDB 锁性能监控
1 2 3 4 5 6 7 8 9 10 11 |
'%innodb_row_lock%';
5 rowsin set (0.00 sec) |
Innodb_row_lock_current_waits: 当前等待锁的数量 Innodb_row_lock_time: 系统启动到现在, 锁定的总时间长度 Innodb_row_lock_time_avg: 每次平均锁定的时间 Innodb_row_lock_time_max: 最长一次锁定时间 Innodb_row_lock_waits: 系统启动到现在, 总共锁定次数
InnoDB 死锁
死锁的情况发生在不同的的事务相互之间拥有对方需要的锁, 而导致相互一直无限等待
死锁可能发生在不同的事务都会对多个相同的表和相同的行上施加锁, 但事务对表的操作顺序不相同
为了减少死锁的发生, 要避免使用 lock table 语句, 要尽量让修改数据的范围尽可能的小和快速; 当不同的事务要修改多个表或者大量数据时, 尽可能的保证修改的顺序在事务之间要一致
默认情况下 InnoDB 下的死锁自动侦测功能是开启的, 当 InnoDB 发现死锁时, 会将其中的一个事务作为牺牲品回滚. show variables like 'innodb_deadlock_detect';
通过 innodb_lock_wait_timeout 参数配置自动侦测功能是否开启, 如果关闭的话, InnoDB 就会使用 innodb_lock_wait_timeout 参数来自动回滚等待足够时间的事
MySQL> show variables like '%innodb_lock_wait_timeout%';
可以通过 show engine innodb status 语句查看最后一次发生死锁的情况
比如以下例子产生的死锁:
1 2 3 4 5 | 事务 1:update tempset name=‘aa'where > 事务 2:update tempset name=‘bb'where > 事务 1:update tempset name=‘aaa'where >
set name=‘bbb'where >
get lock;try
|
InnoDB 死锁检测和回滚
默认情况下死锁检测功能是开启的, 当死锁发生时 InnoDB 会自动检测到并牺牲 (回滚) 其中的一个或者几个事务, 以便让其他的事务继续执行下去.
InnoDB 选择牺牲的事务往往是代价比较小的事务, 其代价计算是根据事务 insert,update, delete 的数据行规模决定
如果事务中的某个语句因为错误而回滚, 则这个语句上的锁可能还会保留, 是因为 InnoDB 仅会存储行锁信息, 而不会存储行锁是由事务中的哪个语句产生的
如果在一个事务中, select 语句调用了函数, 而函数中的某个语句执行失败, 则那个语句会回滚, 如果在整个事务结束时执行 rollback, 则整个事务回滚
可以通过 innodb_deadlock_detect 参数关闭死锁检测功能, 而仅仅用 innodb_lock_wait_timeout 的功能来释放锁等待
减少死锁发生的方法
在事务性数据库中, 死锁是个经典的问题, 但只要发生的频率不高则死锁问题不需要太过担心
查看死锁的方法有两种: 通过 show engine innodb status 命令可以查看最后一个死锁的情况通过 innodb_print_all_deadlocks 参数配置可以将所有死锁的信息都打印到 MySQL 的错误日志中
减少死锁发生的方法:
尽可能的保持事务小型化, 减少事务执行的时间可以减少发生影响的概率
及时执行 commit 或者 rollback, 来尽快的释放锁
当要访问多个表数据或者要访问相同表的不同行集合时, 尽可能的保证每次访问的顺序是相同的. 比如可以将多个语句封装在存储过程中, 通过调用同一个存储过程的方法可以减少死锁的发生
增加合适的索引以便语句执行所扫描的数据范围足够小
尽可能的少使用锁, 比如如果可以承担幻读的情况, 则直接使用 select 语句, 而不要使用 select...for update 语句
如果没有其他更好的选择, 则可以通过施加表级锁将事务执行串行化, 最大限度的限制死锁发生
来源: http://www.linuxidc.com/Linux/2019-01/156362.htm