## 1.MySQL锁机制
MySQL中为了保证数据访问的一致性与有效性等功能,实现了锁机制,MySQL中的锁是在服务器层或者存储引擎层实现的。
2.行锁与表锁
各引擎对锁的支持情况如下:
行锁 | 表锁 | 页锁 | |
---|---|---|---|
MyISAM | √ | ||
BDB | √ | √ | |
InnoDB | √ | √ |
2.1 行锁
针对数据表中行记录的锁。例如事务A更新了一行,而这时候事务B也要更新同一行,则必须等事务A的操作完成后才能进行更新。
A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.
上文出自MySQL的官方文档,从这里我们可以看出行锁是作用在索引上的,哪怕你在建表的时候没有定义一个索引,InnoDB也会创建一个聚簇索引并将其作为锁作用的索引。
这里还是讲一下InnoDB中的聚簇索引。每一个InnoDB表都需要一个聚簇索引,有且只有一个。如果你为该表表定义一个主键,那么MySQL将使用主键作为聚簇索引;如果你不为定义一个主键,那么MySQL将会把第一个唯一索引(而且要求NOT NULL)作为聚簇索引;如果上诉两种情况都GG,那么MySQL将自动创建一个名字为GEN_CLUST_INDEX的隐藏聚簇索引。
因为是聚簇索引,所以B+树上的叶子节点都存储了数据行,那么如果现在是二级索引呢?InnoDB中的二级索引的叶节点存储的是主键值(或者说聚簇索引的值),所以通过二级索引查询数据时,还需要将对应的主键去聚簇索引中再次进行查询。
用一张直观的图来表示行锁:
接下来以两条SQL的执行为例,讲解一下InnoDB对于单行数据的加锁原理:
update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';
第一条SQL使用主键查询,只需要在 id = 49 这个主键索引上加上锁。第二条 SQL 使用二级索引来查询,那么首先在 name = Tom 这个索引上加写锁,然后由于使用 InnoDB 二级索引还需再次根据主键索引查询,所以还需要在 id = 49 这个主键索引上加锁。
也就是说使用主键索引需要加一把锁,使用二级索引需要在二级索引和主键索引上各加一把锁。
根据索引对单行数据进行更新的加锁原理了解了,那如果更新操作涉及多个行呢,比如下面 SQL 的执行场景。
update user set age = 10 where id > 49;
上述 SQL 的执行过程如下图所示。MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录,然后 InnoDB 引擎会将第一条记录返回并加锁,接着 MySQL Server 发起更新改行记录的 UPDATE 请求,更新这条记录。一条记录操作完成,再读取下一条记录,直至没有匹配的记录为止。
2.2 表锁
上面我们讲解行锁的时候,操作语句中的条件判断列都是有建立索引的,那么如果现在的判断列不存在索引呢?InnoDB既支持行锁,也支持表锁,当没有查询列没有索引时,InnoDB就不会去搞什么行锁了,毕竟行锁一定要有索引,所以它现在搞表锁,把整张表给锁住了。那么具体啥是表锁?还有其他什么情况下也会进行锁表呢?
表锁使用的是一次性锁技术,也就是说,在会话开始的地方使用 lock 命令将后续需要用到的表都加上锁,在表释放前,只能访问这些加锁的表,不能访问其他表,直到最后通过 unlock tables 释放所有表锁。
除了使用 unlock tables 显示释放锁之外,会话持有其他表锁时执行lock table 语句会释放会话之前持有的锁;会话持有其他表锁时执行 start transaction 或者 begin 开启事务时,也会释放之前持有的锁。
表锁由 MySQL Server 实现,行锁则是存储引擎实现,不同的引擎实现的不同。在 MySQL 的常用引擎中 InnoDB 支持行锁,而 MyISAM 则只能使用 MySQL Server 提供的表锁。
2.3 两种锁的比较
表锁:加锁过程的开销小,加锁的速度快;不会出现死锁的情况;锁定的粒度大,发生锁冲突的几率大,并发度低;
- 一般在执行DDL语句时会对整个表进行加锁,比如说 ALTER TABLE 等操作;
- 如果对InnoDB的表使用行锁,被锁定字段不是主键,也没有针对它建立索引的话,那么将会锁整张表;
- 表级锁更适合于以查询为主,并发用户少,只有少量按索引条件更新数据的应用,如Web 应用。
行锁:加锁过程的开销大,加锁的速度慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;
- 最大程度的支持并发,同时也带来了最大的锁开销。
- 在 InnoDB 中,除单个 SQL 组成的事务外,锁是逐步获得的,这就决定了在 InnoDB 中发生死锁是可能的。
- 行级锁只在存储引擎层实现,而 MySQL 服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。
3.MyISAM表锁
3.1 MyISAM表级锁模式
- 表共享读锁(Table Read Lock):不会阻塞其他线程对同一个表的读操作请求,但会阻塞其他线程的写操作请求;
- 表独占写锁(Table Write Lock):一旦表被加上独占写锁,那么无论其他线程是读操作还是写操作,都会被阻塞;
默认情况下,写锁比读锁具有更高的优先级;当一个锁释放后,那么它会优先相应写锁等待队列中的锁请求,然后再是读锁中等待的获取锁的请求。
This ensures that updates to a table are not “starved” even when there is heavy SELECT activity for the table. However, if there are many updates for a table, SELECT statements wait until there are no more updates.
这种设定也是MyISAM表不适合于有大量更新操作和查询操作的原因。大量更新操作可能会造成查询操作很难以获取读锁,从而过长的阻塞。同时一些需要长时间运行的查询操作,也会使得线程“饿死”,应用中应尽量避免出现长时间运行的查询操作(在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”,使每一步查询都能在较短的时间内完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。)
4. InnoDB行锁与表锁
4.1 InnoDB锁模式
InnoDB中的行锁
- 共享锁(读锁)(S):加了锁的记录,所有事务都能去读取但不能修改,同时阻止其他事务获得相同数据集的排他锁;
- 排他锁(写锁)(X):允许已经获得排他锁的事务去更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁;
InnoDB表锁——意向锁
由于表锁和行锁虽然锁定范围不同,但是会相互冲突。当你要加表锁时,势必要先遍历该表的所有记录,判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式,MySQL引入了意向锁,来检测表锁和行锁的冲突。
Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table。
The intention locking protocol is as follows:
- Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
- Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.
意向锁也是表级锁,分为意向共享锁(读意向锁)(IS锁)和意向排它锁(写意向锁)(IX锁)。当事务要在记录上加上行锁时,要首先在表上加上意向锁。这样判断表中是否有记录正在加锁就很简单了,只要看下表上是否有意向锁就行了,从而就能提高效率。
- 意向共享锁(IS):一个事务给一行数据行加读锁时,必须先获得表的IS锁
- 意向排它锁(IX):一个事务给一行数据行加写锁时,必须先获得该表的IX锁
意向锁之间是不会产生冲突的,它只会阻塞表级读锁或写锁。意向锁不于行级锁发生冲突。
4.2 锁模式的兼容矩阵
下面表显示了了各种锁之间的兼容情况:
X | IX | S | IS | |
---|---|---|---|---|
X | ||||
IX | 兼容 | 兼容 | ||
S | 兼容 | 兼容 | ||
IS | 兼容 | 兼容 | 兼容 |
注意上面的X与S是说表级的X锁和S锁,意向锁不和行级锁发生冲突)
如果一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务;如果两者不兼容,那么该事务就需要等待锁的释放。
4.3 InnoDB的加锁方法
- 当开启事务时,Repeatable Read和Serializable事务隔离级别是不需要手动加锁的,这两个事务级别中加锁是没有意义,其他会话的事务是无法取得这两种事务中执行的数据的。详情参考mysql事务。
- 意向锁是 InnoDB 自动加的,不需要用户干预;
- 对于UPDATE、DELETE和INSERT语句,InnoDB 会自动给涉及的数据集加上排他锁;
- 对于普通的SELECT语句,InnoDB不会加任何锁;可以通过以下语句显示给记录集添加共享锁或排他锁:
- 共享锁(S):
select * from table_name where ... lock in share mode
。此时其他 session 仍然可以查询记录,并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作,则很有可能造成死锁。 - 排他锁(X):
select * from table_name where ... for update
。其他session可以查询记录,但是不能对该记录加共享锁或排他锁,只能等待锁释放后在加锁。
- 共享锁(S):
select for update
在执行这个 select 查询语句的时候,会将对应的索引访问条目加上排他锁(X锁),也就是说这个语句对应的锁就相当于update带来的效果;
使用场景: 为了让确保自己查找到的数据一定是最新数据,并且查找到后的数据值允许自己来修改,此时就需要用到select for update语句;
性能分析: select for update语句相当于一个update语句。在业务繁忙的情况下,如果事务没有及时地commit或者rollback可能会造成事务长时间的等待,从而影响数据库的并发使用效率。
select lock in share mode
in share mode 子句的作用就是将查找的数据加上一个share锁,这个就是表示其他的事务只能对这些数据进行简单的 select 操作,而不能进行 DML 操作。
使用场景: 为了确保自己查询的数据不会被其他事务正在修改,也就是确保自己查询到的数据是最新的数据,并且不允许其他事务来修改数据。与select for update不同的是,本事务在查找完之后不一定能去更新数据,因为有可能其他事务也对同数据集使用了 in share mode 的方式加上了S锁;
性能分析: select lock in share mode 语句是一个给查找的数据上一个共享锁(S 锁)的功能,它允许其他的事务也对该数据上S锁,但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。
T1:begin tran
select * from table lock in share mode
update table set column1='hello'
T2:begin tran
select * from table lock in share mode
update table set column1='world'
假设 T1 和 T2 同时达到 select,T1 对 table 加共享锁,T2 也对 table 加共享锁,当 T1 的 select 执行完,准备执行 update 时,根据锁机制,T1 的共享锁需要升级到排他锁才能执行接下来的 update。在升级排他锁前,必须等 table 上的其它共享锁(T2)释放,同理,T2 也在等 T1 的共享锁释放。于是死锁产生了。
4.4 行锁小结
- 只有通过索引条件检索数据时,InnoDB才会使用行级锁,否则会使用表级锁(索引失效,行锁变表锁)
- 即使是访问不同行的记录,如果使用的是相同的索引键,会发生锁冲突
- 如果数据表建有多个索引时,可以通过不同的索引锁定不同的行
- 针对一条当前读的SQL语句,InnoDB与MySQL Server的交互,是一条一条进行的,因此,加锁也是一条一条进行的。先对一条满足条件的记录加锁,返回给MySQL Server,做一些DML操作;然后在读取下一条加锁,直至读取完毕。
5.Innodb隐式锁
- 当事务需要加锁的时,如果这个锁不可能发生冲突,InnoDB会跳过加锁环节,这种机制称为隐式锁。
- 隐式锁是InnoDB实现的一种延迟加锁机制,其特点是只有在可能发生冲突时才加锁,从而减少了锁的数量,提高了系统整体性能。
- 隐式锁是针对被修改的B+ Tree记录,因此都是记录类型的锁,不可能是间隙锁或Next-Key类型。
- 隐式锁主要用在插入场景,在特殊情况下,才会将隐式锁转换为显示锁,例如事务1插入记录且未提交,此时事务2尝试对该记录加锁,那么事务2必须先判断记录上保存的事务id是否活跃,如果活跃则帮助事务1建立一个锁对象,而事务2自身进入等待事务1的状态。
- 查询锁状态 select * from performance_schema.data_locks;
6.其他的一些问题
6.1 解决热点行更新导致的性能问题
- 如果能确保这个业务一定不会出现死锁,可以临时把死锁检测关闭掉。一般不建议采用
- 控制并发度,对应相同行的更新,在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
- 将热更新的行数据拆分成逻辑上的多行来减少锁冲突,但是业务复杂度可能会大大提高。
- 事务开启后,更新操作放到最后,减少锁等待时间的影响
- 使用乐观锁ver
6.2 死锁
- 死锁定义:当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态。关键是两个(或以上)的Session加锁的顺序不一致。
- 死锁策略:
- 进入等待,直到超时,通过
innodb_lock_wait_timeout
参数设置 - 发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。参数
innodb_deadlock_detect
设置为on(默认开启)。但死锁检测要耗费大量的CPU资源,时间复杂度O(n)
- 进入等待,直到超时,通过
- 避免死锁的条件
- 不同的session加锁顺序一致,尽可能一次性锁定所需的数据行
- 尽量基于primary(主键)或unique key更新数据
- 单次操作数据量不宜过多,涉及表尽量少
- 尽量使用相同条件访问数据,这样可以避免间隙锁对并发的插入影响
- 精心设计索引,尽量使用索引访问数据,尽可能的缩小锁定范围,避免造成不必要的锁定而影响其他 Query 的执行。
- 尽可能减少基于范围的数据检索过滤条件,避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
- 尽量控制事务的大小,减少锁定的资源量和锁定时间长度。
- 在业务环境允许的情况下,尽量使用较低级别的事务隔离,以减少 MySQL 因为实现事务隔离级别所带来的附加成本。
6.3 两阶段锁协议
- 在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务 commit 时才释放(RR和RC都是)。这样做是为了满足事务的隔离性
- InnoDB行级锁是通过锁索引记录实现的。如果更新的列没有索引,可能会锁全表
- 如果事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
6.4 乐观锁与悲观锁
- 悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作。
- 乐观锁:假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性,通过记录数据版本或时间戳来实现
乐观锁的思路一般是表中增加版本字段,更新时where语句中增加版本的判断,算是一种CAS(Compare And Swep)操作,商品库存场景中version起到了版本控制的作用( AND number=#{number})。
悲观锁之所以是悲观,在于他认为本次操作会发生并发冲突,所以一开始就对商品加上锁(SELECT … FOR UPDATE),然后就可以安心的做判断和更新,因为这时候不会有别人更新这条商品库存。
7.间隙锁 gap lock
间隙锁(Gap Lock):锁加在不存在的空闲空间,可以是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引之后的空间。
当我们用范围条件而不是相等条件索引数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项枷锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”。
InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)
7.1间隙锁的危害
因为Query执行过程中通过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值并不存在。间隙锁有一个比较致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜的锁定,也造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。
7.2间隙锁与死锁
间隙锁是innodb中行锁的一种, 但是这种锁锁住的却不止一行数据,他锁住的是多行,是一个数据范围。间隙锁的主要作用是为了防止出现幻读,但是它会把锁定范围扩大,
有时候也会给我们带来麻烦,我们就遇到了。 在数据库参数中, 控制间隙锁的参数是:
innodb_locks_unsafe_for_binlog,
这个参数默认值是OFF, 也就是启用间隙锁, 他是一个bool值, 当值为true时表示disable间隙锁。
那为了防止间隙锁是不是直接将innodb_locaks_unsafe_for_binlog设置为true就可以了呢? 不一定!
而且这个参数会影响到主从复制及灾难恢复, 这个方法还尚待商量。
间隙锁的出现主要集中在同一个事务中先delete后 insert的情况下, 当我们通过一个参数去删除一条记录的时候, 如果参数在数据库中存在,那么这个时候产生的是普通行锁,锁住这个记录, 然后删除, 然后释放锁。如果这条记录不存在,问题就来了, 数据库会扫描索引,发现这个记录不存在, 这个时候的delete语句获取到的就是一个间隙锁,然后数据库会向左扫描扫到第一个比给定参数小的值,向右扫描扫描到第一个比给定参数大的值, 然后以此为界,构建一个区间, 锁住整个区间内的数据, 一个特别容易出现死锁的间隙锁诞生了。