MySQL第41题怎么评分_【MySQL技术内幕】41-锁问题

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

1、脏读

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

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

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

Time

会话A

会话B

Time

会话A

会话B

1

SET@@tx_isolation='read-uncommitted';

2

SET@@tx_isolation='read-uncommitted';

3

BEGIN

4

SELECT*FROMt;a:1

5

INSERTINTOtSELECT2

6

SELECT*FROMt;a:1a: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上的查询并不需要特别精确的返回值。

2、不可重复读

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

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

Time

会话A

会话B

Time

会话A

会话B

1

SET@@tx_isolation=read-committed;

2

SET@@tx_isolation=read-committed;

3

BEGIN

BEGIN

4

SELECT*FROMta:1

5

INSERTINTOtSELECT2

6

COMMIT

7

SELECT*FROMta:1a: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算法,避免了不可重复读的现象。

3、丢失更新

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

事务T1将行记录r更新为v1,但是事务T1并未提交

与此同时,事务T2将行记录r更新为v2,事务T2未提交。

事务T1提交。

事务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