【MySQL技术内幕】6.5-锁问题、阻塞、死锁、锁升级

1.锁问题

通过锁定机制可以实现事务的隔离性要求,使得事务可以并发地工作。锁提高了并发,但是却会带来潜在的问题。不过好在因为事务隔离性的要求,锁只会带来三种问题,如果可以防止这三种情况的发生,那将不会产生并发异常。

1.1 脏读

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

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

脏数据却截然不同,脏数据是指未提交的数据,如果读到了脏数据,即一个事务可以读到另外一个事务中未提交的数据,则显然违反了数据库的隔离性脏读指的就是在不同的事务下,当前事务可以读到另外事务未提交的数据,简单来说就是可以读到脏数据。下表的例子显示了一个脏读的例子。

脏读的示例

Time

会话A

会话B

1

SET @@tx_isolation='read-uncommitted';

2

SET @@tx_isolation='read-uncommitted';

3

BEGIN

4

SELECT * FROM t;

a:1

5

INSERT INTO t SELECT 2

6

SELECT* FROM t;

a:1

a:2

在上述例子中,事务的隔离级别进行了更换,由默认的 REPEATABLE READ换成了 READ UNCOMMITTED。因此在会话A

中,在事务并没有提交的前提下,会话B中的两次 SELECT操作取得了不同的结果,并且2这条记录是在会话A中并未提交的数据,即产生了脏读,违反了事务的隔离性。

脏读现象在生产环境中并不常发生,从上面的例子中就可以发现,脏读发生的条件是需要事务的隔离级别为 READ NCOMMITTED,而目前绝大部分的数据库都至少设置成 READ COMMITTED。 InnodB存储引擎默认的事务隔离级别为READ REPEATABLE, Microsoft SQL Server数据库为 READ COMMITTED, Oracle数据库同样也是 READ COMMITTED。

脏读隔离看似毫无用处,但在一些比较特殊的情况下还是可以将事务的隔离级别设置为 READ UNCOMMITTED。例如 replication环境中的 slave节点,并且在该save上的查询并不需要特别精确的返回值。

1.2 不可重复读

不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时,另外一个事务也访问该同一数据集合,并做了一些DML操作。因此,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况,这种情况称为不可重复读。

不可重复读和脏读的区别是:脏读是读到未提交的数据,而不可重复读读到的却是已经提交的数据,但是其违反了数据库事务一致性的要求。可以通过下面一个例子来观察不可重复读的情况,如下表所示。

不可重复读的示例

Time

会话A

会话B

1

SET@@tx_isolation=read-committed;

2

SET@@tx_isolation=read-committed;

3

BEGIN

BEGIN

4

SELECT * FROM t

a:1

5

INSERT INTO t SELECT 2

6

COMMIT

7

SELECT * FROM t

a:1

a:2

在会话A中开始一个事务,第一次读取到的记录是1,在另一个会话B中开始了另一个事务,插入一条为2的记录,在没有提交之前,对会话A中的事务进行再次读取时,读到的记录还是1,没有发生脏读的现象。但会话B中的事务提交后,在对会话A中的事务进行读取时,这时读到是1和2两条记录。这个例子的前提是,在事务开始前,会话A和会话B的事务隔离级别都调整为 READ COMMITTED。

一般来说,不可重复读的问题是可以接受的,因为其读到的是已经提交的数据,本身并不会带来很大的问题。因此,很多数据库厂商(如 Oracle、 Microsoft SQL Server)

将其数据库事务的默认隔离级别设置为READ COMMITTED,在这种隔离级别下允许不可重复读的现象。

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

1.3丢失更新

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

1. 事务T1将行记录r更新为v1,但是事务T1并未提交
2. 与此同时,事务T2将行记录r更新为v2,事务T2未提交。
3. 事务T1提交。
4. 事务T2提交。

但是,在当前数据库的任何隔离级别下,都不会导致数据库理论意义上的丢失更新问题。这是因为,即使是 READ UNCOMMITTED的事务隔离级别,对于行的DML操作,需要对行或其他粗粒度级别的对象加锁。因此在上述步骤2)中,事务T2并不能对行记录r进行更新操作,其会被阻塞,直到事务T1提交。

虽然数据库能阻止丢失更新问题的产生,但是在生产应用中还有另一个逻辑意义的丢失更新问题,而导致该问题的并不是因为数据库本身的问题。实际上,在所有多用户计算机系统环境下都有可能产生这个问题。简单地说来,出现下面的情况时,就会发生丢失更新:

• 事务T1查询一行数据,放入本地内存,并显示给一个终端用户User1。
• 事务T2也查询该行数据,并将取得的数据显示给终端用户User2。
• User修改这行记录,更新数据库并提交。
• User2修改这行记录,更新数据库并提交。

显然,这个过程中用户Uerl的修改更新操作“丢失”了,而这可能会导致一恐怖”的结果。设想银行发生丢失更新现象,例如一个用户账号中有10000元人民币,他用两个网上银行的客户端分别进行转账操作。第一次转账9000.人民币,因为网络和数据的关系,这时需要等待。但是这时用户操作另一个网上银行客户端,转账1元,如果最终两笔操作都成功了,用户的账号余款是9999人民币,第一次转的9000民币并没有得到更新,但是在转账的另一个账号却会收到这9000元,这导致的结果就是钱变多,而账不平。也许有读者会说,不对,我的网银是绑定 USB Key的,不会发生这种情况。是的,通过 USB Key登录也许可以解决这个问题,但是更重要的是在数据库层解决这个问题,避免任何可能发生丢失更新的情况。

要避免丢失更新发生,需要让事务在这种情况下的操作变成串行化,而不是并行的操作。即在上述四个步骤的1)中,对用户读取的记录加上一个排他X锁。同样,在步骤2)的操作过程中,用户同样也需要加一个排他X锁。通过这种方式,步骤2)就必须等待一步骤1)和步骤3)完成,最后完成步骤4)。下表所示的过程演示了如何避免这种逻辑上丢失更新问题的产生。

丢失更新问题的处理方法

Time

会话A

会话B

1

BEGIN

2

SELECT cash into @cash FROM account

WHERE user= pUser FOR UPDATE;

3

SELECT cash into @cash FROM account

WHERE user= pUser FOR UPDATE; #等待

...

...

...

m

UPDATE account SET cash=@cash-9000

WHERE user=pUser

m+1

COMMIT

m+2

UPDATE account SET cash=@cash-1

WHERE user=pUser

m+3

COMMIT

2.阻塞

因为不同锁之间的兼容性关系,在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源,这就是阻塞。阻塞并不是一件坏事,其是为了确保事务可以并发且正常地运行。

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

mysql> set innodb_lock_wait timeout=60;
Query OK, 0 rows affected (0.00 sec)

而innodb_rollback_on_timeout是静态的,不可在启动时进行修改。

当发生超时, MySQL数据库会抛出一个1205的错误,如:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT FROM t WHERE a=1 FOR UPDATE;
ERROR 1205(HY000): Lock wait timeout exceeded; try restarting transaction

需要牢记的是,在默认情况下 InnoDB存储引擎不会回滚超时引发的错误异常。其实 InnoDB存储引擎在大部分情况下都不会对异常进行回滚。如在一个会话中执行了如下语句:

在会话A中开启了一个事务,在 Next-Key Lock算法下锁定了小于4的所有记录(其实也锁定了4这个记录本身)。在另一个会话B中执行如下语句:

可以看到,在会话B中插入记录5是可以的,但是在插入记录3时,因为会话A中Next-Key Lock算法的关系,需要等待会话A中事务释放这个资源,所以等待后产生了超时。但是在超时后用户再进行 SELECT操作时会发现,5这个记录依然存在。

这是因为这时会话B中的事务虽然抛出了异常,但是既没有进行COMMIT操作,也没有进行 ROLLBACK。而这是十分危险的状态,因此用户必须判断是否需要COMMIT还是 ROLLBACK,之后再进行下一步的操作。

3.死锁

3.1死锁的概念

死锁是指两个或两个以上的事务在执行过程中,因争夺锁资源而造成的一种互相等待的现象。若无外力作用,事务都将无法推进下去。解决死锁问题最简单的方式是不要有等待,将任何的等待都转化为回滚,并且事务重新开始。毫无疑问,这的确可以避免死锁问题的产生。然而在线上环境中,这可能导致并发性能的下降,甚至任何一个事务都不能进行。而这所带来的问题远比死锁问题更为严重,因为这很难被发现并且浪费资源。

解决死锁问题最简单的一种方法是超时,即当两个事务互相等待时,当一个等待时间超过设置的某一阈值时,其中一个事务进行回滚,另一个等待的事务就能继续进行。

在 InnoDB存储引擎中,参数 innodb_lock_wait_timeout用来设置超时的时间超时机制虽然简单,但是其仅通过超时后对事务进行回滚的方式来处理,或者说其是根据FIFO的顺序选择回滚对象。但若超时的事务所占权重比较大,如事务操作更新了很多行,占用了较多的 undo log,这时采用FIFO的方式,就显得不合适了,因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。

因此,除了超时机制,当前数据库还都普遍采用wait-for graph(等待图)的方式来进行死锁检测。较之超时的解决方案,这是一种更为主动的死锁检测方式。 InnoDB存储引擎也采用的这种方式。wait-for graph要求数据库保存以下两种信息:

• 锁的信息链表
• 事务等待链表

通过上述链表可以构造出一张图,而在这个图中若存在回路,就代表存在死锁,因此资源间相互发生等待。在 wait-for graph中,事务为图中的节点。而在图中,事务T1指向T2边的定义为:

• 事务T1等待事务T2所占用的资源
• 事务T1最终等待T2所占用的资源,也就是事务之间在等待相同的资源,而事务T1发生在事务T2的后面

下面来看一个例子,当前事务和锁的状态如图所示。

在 Transaction Wait Lists中可以看到共有4个事务t1、t2、t3、t4,故在wait-for graph中应有4个节点。而事务t2对row1占用x锁,事务t对row2占用s锁。事务t1需要等待事务t2中row1的资源,因此在 wait-for graph中有条边从节点t指向节点t2。事务t2需要等待事务t1、t4所占用的row2对象,故而存在节点t2到节点t1、t4的边。同样,存在节点t3到节点t1、t2、t4的边,因此最终的 wait-for graph如图所示。

通过图6-6可以发现存在回路(t1,t2),因此存在死锁。通过上述的介绍,可以发现wait-for graph是一种较为主动的死锁检测机制,在每个事务请求锁并发生等待时都会判断是否存在回路,若存在则有死锁,通常来说InnoDB存储引擎选择回滚undo量最小的事务。

wait-for graph的死锁检测通常采用深度优先的算法实现,在 InnoDB1.2版本之前,都是采用递归方式实现。而从1,2版本开始,对 wait-for graph的死锁检测进行了优化,将递归用非递归的方式实现,从而进一步提高了 InnoDB存储引擎的性能。

3.2死锁概率

死锁应该非常少发生,若经常发生,则系统是不可用的。此外,死锁的次数应该还要少于等待,因为至少需要2次等待才会产生一次死锁。本节将从纯数学的概率角度来分析,死锁发生的概率是非常小的。

假设当前数据库中共有n+1个线程执行,即当前总共有n+1个事务。并假设每个事务所做的操作相同。若每个事务由r+1个操作组成,每个操作为从R行数据中随机地操作一行数据,并占用对象的锁。每个事务在执行完最后一个步骤释放所占用的所有锁资源。最后,假设nr<<R,即线程操作的数据只占所有数据的一小部分在上述的模型下,事务获得一个锁需要等待的概率是多少呢?当事务获得一个锁,其他任何一个事务获得锁的情况为

(1+2+3+…+r)/(r+1)≈r/2

由于每个操作为从R行数据中取一条数据,每行数据被取到的概率为1/R,因此,事务中每个操作需要等待的概率PW为:

PW=nr/2R

事务是由r个操作所组成,因此事务发生等待的概率PW(T)为:

死锁是由于产生回路,也就是事务互相等待而发生的,若死锁的长度为2,即两个等待节点间发生死锁,那么其概率为:

由于大部分死锁发生的长度为2,因此上述公式基本代表了一个事务发生死锁的概率。从整个系统来看,任何一个事务发生死锁的概率为:

从上述的公式中可以发现,由于m<R,因此事务发生死锁的概率是非常低的。同时,事务发生死锁的概率与以下几点因素有关:

• 系统中事务的数量(n),数量越多发生死锁的概率越大。
• 每个事务操作的数量(),每个事务操作的数量越多,发生死锁的概率越大,
• 操作数据的集合(R),越小则发生死锁的概率越大。

3.3 死锁的示例

如果程序是串行的,那么不可能发生死锁。死锁只存在于并发的情况,而数据库本身就是一个并发运行的程序,因此可能会发生死锁。下表的操作演示了死锁的一种经典的情况,即A等待B,B在等待A,这种死锁问题被称为AB-BA死锁。

死锁用例1

时间

会话A

会话B

1

BEGIN

2

SELECT * FROM t WHERE a=1 FOR UPDATE

a:1

BEGIN

3

SELECT * FROM t WHERE a=2 FOR UPDATE

a:2

4

SELECT * FROM t WHERE a=2 FOR UPDATE

#等待

5

SELECT * FROM t WHERE a=1 FOR UPDATE

ERROR 1213(40001): Deadlock found when trying to get lock; try restarting transaction

在上述操作中,会话B中的事务抛出了1213这个错误提示,即表示事务发生了死锁。死锁的原因是会话A和B的资源在互相等待。大多数的死锁 InnoDB存储引擎本身可以侦测到,不需要人为进行干预。但是在上面的例子中,在会话B中的事务抛出死锁异常后,会话A中马上得到了记录为2的这个资源,这其实是因为会话B中的事务发生了回滚,否则会话A中的事务是不可能得到该资源的。

InnoDB存储引擎并不会回滚大部分的错误异常,但是死锁除外。发现死锁后, InnoDB存储引擎会马上回滚一个事务,这点是需要注意的。因此如果在应用程序中捕获了1213这个错误,其实并不需要对其进行回滚。

此外还存在另一种死锁,即当前事务持有了待插入记录的下一个记录的ⅹ锁,但是在等待队列中存在一个S锁的请求,则可能会发生死锁。来看一个例子,首先根据如下代码创建测试表t,并导入一些数据:

CREATE TABLE t(
a INT PRIMARY Key
)ENGINE=InnoDB;
INSERT INTO t VALUES(1),(2),(4),(5);

表t仅有一个列a,并插入4条记录。接着运行下表所示的查询。

死锁用例2

时间

会话A

会话B

1

BEGIN

2

BEGIN

3

SELECT * FROM t WHERE a=4 FOR UPDATE

4

SELECT * FROM t WHERE a < 4 LOCK IN SHARE MODE

#等待

5

INSERT INTO t VALUES(3);

ERROR 1213(40001): Deadlock found when trying to get lock; try restarting transaction

6

#事务获得锁,正常运行

可以看到,会话A中已经对记录4持有了X锁,但是会话A中插入记录3时会导致死锁发生。这个问题的产生是由于会话B中请求记录4的S锁而发生等待,但之前请求的锁对于主键值记录1、2都已经成功,若在事件点5能插入记录,那么会话B在获得记录4持有的S锁后,还需要向后获得记录3的记录,这样就显得有点不合理。因此 InnoDB存储引擎在这里主动选择了死锁,而回滚的是 undo log记录大的事务,这与AB-BA死锁的处理方式又有所不同。

4.锁升级

锁升级(Lock Escalation)是指将当前锁的粒度降低。举例来说,数据库可以把一个表的1000个行锁升级为一个页锁,或者将页锁升级为表锁。如果在数据库的设计中认为锁是一种稀有资源,而且想避免锁的开销,那数据库中会频繁出现锁升级现象。

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

即使在 Microsoft SQL Server2005版本之后, SQL Server数据库支持了行锁,但是其设计和 InnoDB存储引擎完全不同,在以下情况下依然可能发生锁升级:

• 由一句单独的SQL语句在一个对象上持有的锁的数量超过了阈值,默认这个阈值为5000。值得注意的是,如果是不同对象,则不会发生锁升级
• 锁资源占用的内存超过了激活内存的40%时就会发生锁升级

在 Microsoft SQL Server数据库中,由于锁是一种稀有的资源,因此锁升级会带来一定的效率提高。但是锁升级带来的一个问题却是因为锁粒度的降低而导致并发性能的降低。

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

假设一张表有3000000个数据页,每个页大约有100条记录,那么总共有300000000条记录。若有一个事务执行全表更新的SQL语句,则需要对所有记录加锁。若根据每行记录产生锁对象进行加锁,并且每个锁占用10字节,则仅对锁管理就需要差不多需要3GB的内存。而 InnoDB存储引擎根据页进行加锁,并采用位图方式,假设每个页存储的锁信息占用30个字节,则锁对象仅需90MB的内存。由此可见两者对于锁资源开销的差距之大。