【MySQL】3. 锁

【MySQL】3. 锁

开发多用户、数据库驱动的应用时,最大的一个难点就是:一方面要最大程度的利用数据库的并发访问,另一方面还有确保每个用户能以一致的方式读取和修改数据。为此有了 锁(locking) 的机制。

InnoDB存储引擎比MySQL的其他存储引擎在锁的设计上更加优秀,其实现方式与Oracle相似。

(InnoDB 存储引擎不需要锁升级,因为一个锁和多个锁的开销是相同的)

1 . 什么是锁?

锁是数据库区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。InnoDB不仅在行级别上对表数据上锁。还在数据库的内部多个地方使用锁,比如:缓冲池的LRU列表。数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性。

MyISAM 引擎的锁设计方式是 表锁。并发情况下读没有问题,但是并发插入时的性能就要差一些的,若插入是在‘底部’,MyISAM 存储引擎还是可以有一定的并发写入操作的。

Microsoft SQL Server 2005之前都是页锁,相对于MyISAM引擎,并发性能有所提高。页锁容易实现,然而对于数据的插入并发问题无能为力。从2005版本之后,开始支持乐观并发和悲观并发,在乐观并发下开始支持行级锁,但是实现方式与 InnoDB 完全不同,用户再使用过程中,锁是一种稀有的资源,锁越多开销越大,因此有锁升级的功能,锁可以由行级锁升级为表级锁,这时,并发性能大大降低。

InnoDB 存储引擎的锁实现 和 Oracle 数据库非常类似,提供一致性的非锁定读、行级锁支持。行级锁没有相关额外的开销,并可以同时得到并发性和一致性。

2. lock 和 latch:

在数据库中,lock 和 latch 都可以称为“锁”。

latch 一般称为闩锁(轻量级的锁),因为其要求锁定的时间必须非常短。若持续的时间长,则应用的性能会非常差。在InnoDB存储引擎中,latch又可以分为 mutex (互斥量) 和 rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性,并且通常没有死锁检测机制。

lock 的对象是事务,用来锁定的是数据库中的对象,如 表、行、页。并且一般lock的对象仅在事务 commit 或 rollback 后进行释放(不同事务隔离级别释放的时间可能不同),lock 是有死锁检测机制的。

#通过 show engine innodb mutex 查看latch的情况:(debug版本信息更全)

# 通过 show engine innodb status 可以查看lock的情况:(也可以通过数据库查看information_scheme结构库下的 InnoDB_TRX、InnoDB_LOCKS、InnoDB_Lock_waits,我的MySQL 8.x 只有 第一个表)

3. InnoDB 存储引擎中的锁:

3.1 锁的类型

InnoDB 存储引擎实现了两种标准的行级锁:

共享锁(S Lock):允许事务读一行数据
排它锁(X Lock):允许事务删除或更新一行数据

共享锁可以被两个事务同时使用,成为锁兼容。

InnoDB 存储引擎为了支持多粒度(granular)锁定,这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作,InnoDB存储引擎支持一种额外的锁方式,称之为意向锁(Intention Lock)。意向锁是将锁定的对象分为多个层次,意向锁意味着事务希望在更细粒度(fine granularity)上进行加锁。

InnoDB 存储引擎支持意向锁设计比较简练,其意向锁即为表级别的锁。设计目的是为了在一个事务中揭示下一行将被请求的锁类型。支持两种意向锁

意向共享锁(IS Lock):事务想要获得一张表中某几行的共享锁
意向排它锁(IX Lock):事务想要获得一张表中某几行的排它锁
由于Lock 存储引擎支持的是行级别的锁,因此意向锁其实不会阻塞除全表扫描以外的任何请求

如果对表单纯加 S锁 或者 加 X锁,性质就不一样了,IX 和 X 是完全不一样的性质,不过在InnoDB中应该不会出现。

3.2 一致性非锁定读:

一致性的非锁定读(Consistent nonlocking read)是指InnoDB 存储引擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据。

如果此时读取的行正在执行Delete 或者 update 操作,只是读取操作不会因此去等待X锁的释放。InnoDB 存储引擎会去读取行的一个快照数据。

快照数据是指该行的之前版本的数据,该实现是通过undo段完成的。而undo用来在事务中回滚数据,因此快照数据本身是没有额外的开销的。此外,快照数据是不需要上锁的,因为没有事务会修改历史数据。

非锁定读机制极大的提高了数据库的并发性。在InnoB存储引擎的默认设置下,这是默认的读取方式,即读取不会占用和等待表上的锁。

快照数据其实就是当前行之前的历史版本,每行记录可能有多个版本。一个行记录存在多个快照数据,称为行多版本技术。由此带来的并发控制,称之为多版本并发控制(Multi Version Concurrency Control,MVCC)。

在事务隔离级别Read Commited 和 Repeatable Read(InnoDB的默认引擎)模式下,InnoDB存储引擎使用非锁定的一致性读。

然而对于快照的定义不同,在Read Commited 级别下,读取锁定行最新的快照。在Repeatable Read级别下,读取事务开始时的快照数据。

注意:对于Read Commited 事务隔离级别来说,违反了ACID中的I的特性,即隔离性,违反了非一致性读,在每一次select 时创建一个 ReadView,导致两次读取不一致,有不可重复读的问题

3.3 一致性锁定读

在默认配置下,事务的隔离级别为Repeatable Read 模式,InnoDB 存储引擎的select 操作使用一致性非锁定读。某些情况下,用户需要显式的对数据库读取操作进行加锁以保证数据逻辑的一致性。

InnoDB 存储引擎对于 select 语句支持两种一致性的锁定锁(locking read)操作:

select ... for update          # 显式加 X 锁
select ... lock in share mode  # 显式加 S 锁

加锁操作必须一个事务中,当事务提交了,锁也就释放了,设置事务非主动提交set autocommit=0

只有两个事务都做显式加锁时,才有效,加锁时锁定当前的行,非一致性锁定读是读取快照

3.4 自增长 和 锁

在InnoDB存储引擎内存结构中,对每个含有自增长的表都有一个自增长计数器(auto-increment counter)。当对含有自增长的计数器的表进行插入操作时,这个计数器会被初始化,并执行如下的语句来得到计数器的值:

select Max(auto_inc_col) from table for update;  # 对 表 加锁

插入操作会根据这个自增长的计数器加1赋值给自增长列。这个实现方式称为Auto-Inc Locking 。这种锁其实是采用一种特殊的表锁机制,为了提高插入的性能,所不是在一个事务完成后释放,而是在对插入数据之后就立即释放锁。

这种方式从一定程度上提高了并发插入的效率,但还是存在一些性能问题。
1. 对于有自增长值得列的并发插入性能较差,事务必须等待前一个插入完成(比如:两个事务都有插入,B需要等待A插入之后,释放锁)
2. 对于insert...select的大数据量插入会影响插入性能,因为另一个事务中的插入会被阻塞

从 MySQL 5.1.22 版本开始,InnoDB 存储引擎提供了一种轻量级互斥量的自增长实现机制,这种机制大大提高了自增长值插入的性能。从该版本开始,InnoDB存储引擎提供了一个参数来控制自增长的模式(innodb_autoinc_lock_mode)。默认值为1

对不同的插入情况,有不同的处理:

参数的设置对不同情况下的处理:

此外:

在master上用InnoDB存储引擎,在salve上使用MyISAM 存储引擎的replication 架构,在设计时需要考虑 两者的自增长差异:
MyISAM存储引擎中自增长的实现 和 InnoDB 不同,MyISAM存储引擎是表锁设计,自增长不用考虑并发插入的问题,直接锁表。

在InnoDB存储引擎中,自增长值的列必须是索引,同时必须是索引的第一个列,如果不是第一个列,则MySQL 会抛出异常,而 MyISAM 存储引擎没有这个问题。(猜可能跟B+树有关系)??

3.5 外键 和 锁

外键主要用来引用完整性的约束检查。在InnoDB存储引擎中,对于一个外键列,如果没有显式的添加索引,InnoDB存储引擎会自动对其加一个索引,因为这样可以避免表锁—-这比Oracle数据库做的好,Oracle 数据库不会自动添加索引,用户必须要自己手动添加,这也导致了Oracle数据库中可能产生死锁,??

对于外键值得插入或更新,首先需要查询父表中的记录,即Select父表,但是对于父表的select 操作,不是使用一致性非锁定读方式,因为这样会发生数据不一致的问题,因此这时使用的是 select … lock in share mode 方式,主动未父表的记录加一个S锁,如果此时已经有了 X锁,子表上的操作会被阻塞。

4 锁的算法

4.1 行锁的三种算法

InnoDB存储引擎中的锁的算法:

Record Lock: 单个行记录上的锁
Gap Lock: 间隙锁,锁定一个范围,但不包含记录本身
Next-Key Lock: Gap Lock + Record Lock,锁定一个范围,并且锁定记录本身

Record Lock 总是会去锁定索引记录,如果InnoDB引擎表没有设置任何索引,那么会使用隐式主键锁定
Next-Key Lock 结合了Gap Lock 和 Record Lock 的一种锁定算法,在Next-Key Lock算法下,InnoDB 对于行的查询都是采用这种锁定算法

采用Next-Key Lock技术的锁定技术成为 Next-Key Locking。其设计目的是为了解决Phantom Problem。该技术锁定的不是单个值,而是一个范围,是谓词锁(predict lock)的一种改进。

除了next-key locking,还有privious-key locking :( 10,11] 和 [10,11 )

当查询的所有含有唯一属性时,InnoDB存储引擎会对Next-Key Lock 进行优化,将其降级为 Record Lock,即仅锁住索引本身,而不是范围。提高并发性。

Next-Key Lock 降级到 Record Lock 仅在查询的列是唯一索引的时候出现。如果辅助索引,情况就不同了。SQL通过辅助索引列查询,使用传统的Next-Key Locking技术加锁,如果有两个索引,分别锁定,对于聚集索引,对记录加 Record Lock,对于辅助索引,使用Next-Key Lock加锁,锁定范围(x, y),另外 InnoDB 还会对 辅助索引的下一个键值对加上gap lock,即还有一个(y, y+1)的锁。
Gap Lock 的作用为了阻止多个事务将记录插入到同一个范围内,防止发生 Phantom Problem的问题发生。
用户可以通过设置显示关闭 Gap Lock:
1. 将事务的隔离级别设置为 Read Commited
2. 将参数innodb_locks_unsafe_for_bilog 设置为1

如果用户主动关闭了Gap Lock,除了外键约束和唯一性检查依然需要Gap Lock,其余情况仅使用 Record Lock 来锁定。但关闭Gap Lock破坏了事务的隔离性,并且对于replication,可能会导致主从数据的不一致。此外,从性能上看,Read Commited 也不如 Read Repeatable。

在InnoDB存储引擎中,对于insert操作,其会检查插入记录的下一条记录是否被锁定,若已经被锁定,则不允许插入。

对于唯一键值的锁定,Next-Key Lock 降级为 Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成,则查询仅是查找多个唯一索引列的其中一个,那么查询其实是range类型查询,而不是point类型查询,故InnoDB存储引擎依然使用Next-Key Lock 锁定。

4.2 解决Phantom Problem(幻读问题)

在默认的事务隔离机制下, 即Repeatable Read。InnoDB使用Next-Key Locking机制来避免Phantom Problem(幻读问题)。Oracle需要使用Serializable来实现。

幻读是指在同一事务下,连续执行两次同样的SQL语句可能导致不同的结果,第二次的SQL语句可能会返回之前的不存在的行。

InnoDB存储引擎采用Next-Key Locking的算法避免Phantom Problem。在进行范围查找时,不是锁定存在的记录,而是锁定一个范围,不允许在这个范围内插入新纪录,从而避免Phantom Problem。

用户可以通过InnoDB存储引擎的Next-Key Locking 机制在应用层面实现唯一性的检查:
如果用户通过索引查询一个值,并对该行加上一个 S Lock,那么即使查询的值不在,其锁定的也是一个范围,因此若没有返回任何行,那么新插入的值一定是为唯一的。
如果在进行第一步 select ... lock in share mode 操作时,有多个事务并发操作,会导致死锁,最终只有一个事务插入成功,其他事务会抛出死锁的错误。

5. 锁的问题

通过锁的机制实现了事务的隔离性要求,但是也可能会导致问题:

脏读、不可重复读、丢失更新

5.1 脏读

脏数据是指事务对缓冲池中行记录的修改,并且还没有提交。

脏数据和脏页是完全不同的两个概念。
脏页指的是在缓冲池中已经被修改的也,但是还没有刷新到磁盘中,即数据库实例内存中和磁盘中的页的数据不一致,当然在刷新到磁盘之前,日志都已经写入到重做日志文件中了,而所谓脏数据是指事务对缓冲池中行记录的修改,并且还没有被提交。

对于脏页的读取,是正常的。脏页是因为数据库实例内存和磁盘的异步造成的,这并不影响数据的一致性(或者说两者最终会达成一致性,即当脏页都刷回到磁盘)。并且因为脏页的刷新是异步的,不影响数据库的可用性,因此可以带来性能的提高。

脏数据是指未提交的数据,如果读到了脏数据,即一个事务可以读到另外一个事务中未提交的数据,违反数据库的隔离性。

脏读指的就是在不同的事务下,当前事务可以读到另外事务未提交的数据。

事务A 读取某个数据集合,在事务A 还没有结束的时候,事务B 访问同一个数据集合,并修改了数据内容,并没有提交,事务 A 再次再次读取同一数据集合,可以读取到事务B 还没有提交的数据,这种情况称为 脏读。
脏读现象在生产环境中并不常发生,脏读发生的条件是需要事务的隔离级别为Read Uncommited,目前绝大多数数据库的隔离机制至少都是 Read Commited。
InnoDB 的隔离机制为 Read Repeatable ,
Microsoft SQL Server 为 Read Commited ,
Oracle 数据库也是 Read Commited ,

在一些特殊的情况下,Read Uncommited 隔离级别还是有一定意义的,比如:Replication环境的slave节点,并且在该slave上的查询并不需要返回特别精确的值。

5.2 不可重复读

不可重复读是指 一个事务内多次读取同一个数据集合,读取的内容不一致的问题。

事务A读取数据集合,不加锁,在事务A还没有结束的时候,事务B 访问同一数据集合。并做了一些DML操作,提交。此时,事务A再次读取数据集合,可以读取到事务B 修改之后的数据,两次读取的数据不一致,这种情况称为 不可重复读。

不可重复读 和 脏读 都违反了事务一致性要求,其区别是:

脏读是读到未提交的数据,
不可重复读是读到已经提交的数据,

一般来说,不可重复读的问题是可以接受的,因为其读到的是已经提交的数据,本身并不会带来很大的问题。比如:Oracle 和 M… SQL Server默认选择该隔离机制.

在InnoDB存储引擎中,通过使用 Next-Key Lock算法来避免不可重复读的问题,在MySQL的官方文档中,将不可重复读的问题定义为 Phantom Problem,幻像问题。在Next-Key Lock 算法下,对于索引的扫描,不仅是锁住扫描到的索引,而且还锁住这些索引覆盖的范围(gap)。因此在这个范围内的插入都是不允许的。这样就避免了另外的事务在这个范围内插入数据导致的不可重复读的问题,因此,InoDB存储引擎默认的隔离级别 Read Repeatable 采用 Next-Key Lock算法,避免了不可重复读的现象。

5.3 丢失更新

丢失更新是另一个锁导致的问题,简单来说就是一个事务的更新操作会被另一个事务的更新操作所覆盖,从而导致数据的不一致。

在当前的数据库任何隔离级别下,对行记录的DML 操作都会加锁,所以不会出现这种问题。

虽然数据库能够阻止丢失更新问题的发生,但是在生产环境下还是可能出现丢失更新问题,导致该问题的不是数据库层面,而是多用户系统环境可能出现的问题。
1. 事务A 查询一行记录,放到本地内存,并显示为终端用户 User1
2. 事务B 查询同一条记录,并将取得的数据显示为终端用户 User2
3. User1 修改记录,更新数据库提交 (不是事务操作,跟之前的查询不在同一事务中)
4. User2 修改记录,更新数据库提交 (不是事务操作,跟之前的查询不在同一事务中)

问题在于,User2 修改的依据信息是 第一次查询的数据,而在更新的时候,该数据已经被修改了
可以通过 乐观锁CAS 来避免该问题,在更新操作时,判断内容是不是上次获取的内容。???

要避免丢失更新的发生,需要让事务在这种情况下的操作变成串行化,而不是并行的操作。通过对读取加 排他锁(X锁)实现,(业务场景需要先select 后 update)

丢失更新是程序员最容易犯的错误,也是最不容易发现的错误,因为这种现象是随机的、零星出现的,不过其造成的后果是十分严重的。

6. 阻塞

因为不同锁之间的兼容性关系,有时候一个事务中的锁需要等待另一个事务中的锁释放它占用的资源,这就是阻塞。阻塞并不是坏事,是为了事务可以并发和正确的执行。

在InnoDB存储引擎中,参数 innodb_lock_wait_timeout 用来控制等待的时间(默认是50秒),innodb_rollback_on_timeout 用来设定是否在等待超时时对进行中的事务进行回滚操作(默认是OFF,代表不回滚)。
参数innodb_lock_wait_timeout是动态的,可以在MySQL数据库运行时进行调整。
参数innodb_rollback_on_timeout是静态的,不可在启动时进行修改。

默认情况下,InnoDB存储引擎不会回滚超时引起的错误异常,(但是死锁会自动回滚)其实innoDB存储引擎在大部分情况下都不会对异常进行回滚。所以用户必须判断如果发生了异常,下一步处理 是 commit 还是 rollback。

7. 死锁

7.1 死锁的概念

死锁是指两个或两个以上的事务在执行过程中,因争夺资源而造成的一种互相等待的现象。

解决死锁问题最简单的方式就是不要有等待,将任何的等待都转化为回滚,但是在生产环境,这可能导致并发性能的严重下降,带来的问题比死锁还有严重。实际中解决死锁用的方法是 超时检测,即当两个事务互相等待时,当一个等待时间超过设置的某一个阈值,其中一个事务就回滚,另一个事务就可以执行了。在InnoDB存储引擎中,使用 innodb_lock_wait_timeout来设置超时时间。

超时机制很简单,但是仅通过超时之后对事务进行回滚来处理,或者说通过 FIFO的顺序选择回滚事务是不太合理的。如果超时的事务所占的权重比较大,事务操作记录多,占用较多的undo log,这时候使用 FIFO 就不太合适了。
通常是根据权重或者说 undo log的记录,来判断某个事务应该回滚的。

除了超时机制,当前数据库还普遍采用 wait-for graph(等待图)的方式来进行死锁检测。这是一种比超时机制更加主动的死锁检测方式。InnoDB也采用这种方式。

wait-for graph 要求数据库保存以下两种信息:
锁的信息链表
事务等待链表
通过上述链表可以构建一张图,如果图中存在回路,就表示存在死锁。通常 InnoDB会选择回滚 undo 量最小的事务。

wait-for graph 的死锁检测通常采用深度优先的算法实现,在InnoDB 1.2版本之前,都是采用递归方式实现的,从1.2开始,进行优化,采用非递归的方式实现,提高了性能。

7.2 死锁的概率

死锁应该非常少发生,死锁的次数应该还要少于等待,因为至少需要2次等待才会产生一次死锁。

假设当前数据库中有 n+1 个线程执行,即当前有 n+1  个事务,每个事务所做的操作相同,若每个事务由 r+1 个操作组成,每个操作为从 R 行数据中随机操作一行数据,并占用对象的锁。每个事务在执行完最后一个步骤之后释放所占用的所有锁资源。最后,假设 nr << R,即线程操作的数据只占所有数据的一小部分。

系统中任何一个事务发生死锁的概率 ≈ (n^2) * (r^4) / (4(R^2))
因为 nr << R ,所以事务发生死锁的概率是非常低的。

系统中 事务数量 和 操作数量越多,死锁概率越大
系统中 数据集合 越少,发生死锁概率越大

7.3 死锁的实例

死锁只发生在并发的情况下,而数据库本身就是一个并发运行的程序,因此可能会发生死锁。

InnoDB 并不会回滚大部分的错误异常,但是死锁除外。发现死锁之后,InnoDB会马上回滚一个事务。
Oracle 数据库产生死锁的常见原因是 没有对外键添加索引,而InnoDB存储引擎会自动对其添加外键索引,因而能够更好的避免这种情况的发生,而且MySQL在认为删除索引时,会抛出异常。

特殊回滚情况:当前事务持有了待插入记录的下一个记录的X锁,但是在等待队列中存在一个S锁的请求,则可能会发生死锁。(所以,插入记录需要获取待插入记录的后一个记录的锁)

事务A 中已经持有了记录4的X锁,但是事务A中插入记录3时会导致死锁发生。主要是因为事务B持有了主键 1、2的锁,等待获取记录4的锁,如果记录3插入成功,事务B 获取记录 4的锁之后,就没有获得记录 3 的锁,会导致不可预知的后果。因此,对于这种情况,InnoDB 主动选择了死锁,而回滚的是 undo log记录大的事务,这与 AB-BA 死锁的处理方式有所不同

8 锁升级

锁升级(Lock Escalation)是指将当前锁的粒度减低。比如:行锁变页锁。

InnoDB 存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的。InnoDB 是根据每个事务访问的每个页来对锁进行管理的,采用的是位图的方式。因此不管是事务锁定页中的一个记录还是多个记录,其开销是一致的。

假设一张表有3 000 000 个数据页,每个页大约有100条记录,那么总共有300 000 000 条记录。如果一个事务执行全表更新的SQL,需要对所有的记录加X锁,假如每行记录产生锁对象并加锁,每个锁占用10字节,则对锁管理大概需要3GB内存。
而使用 InnoDB存储引擎根据页进行加锁,采用位图方式,假设每个页存储的锁信息占用30个字节,则锁对象仅需要90MB的内存。

关于 Microsoft SQL Server :

Microsoft SQL Server 数据库设计认为锁是一种稀有资源,在适合的时候会自动将行、键或者分页锁升级为更粗粒度的表级锁。这种升级保护了系统资源,防止系统使用太多的内存来维护表,在一定程度上提高了效率。

即使在Microsoft SQL Server 2005版本之后,SQL Server 数据库支持了行锁,但是其设计和InnoDB存储引擎完全不同,在以下情况下依然可能发生锁升级:
1. 单独SQL 在一个对象上持有的锁超过阈值(5000),如果是不同对象,不会升级
2. 锁资源占用的内存超过了激活内存的 40% 时也会发生锁升级

Microsoft SQL Server 中,由于锁是一种稀有资源,因此锁升级带来一定的效率提高,但是锁升级也会因为锁粒度降低导致并发性能变差。

9 总结

脏读、不可重复读、丢失更新

查看事务锁定的资源

参考书籍:
MySQL技术内幕InnoDB存储引擎第2版.pdf    姜承尧
MySQL 是怎样运行的:从根儿上理解 MySQL  掘金网站 小孩子4919
0 0 vote
Article Rating
Subscribe
提醒
guest
0 评论
Inline Feedbacks
View all comments