Mysql锁

锁是什么？

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

InnoDB存储引擎中的锁

锁的类型？

InnoDB存储引擎实现如下两种标准的锁

共享锁（S Lock），允许事务读一行数据

排他锁（X Lock），允许事务删除或更新一行数据

如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容（Lock Compatible）。但若有其他的事务T3想获得行r的排他锁，则必须等待事务T1，T2释放行r上的共享锁——这种情况称为锁不兼容。

	S锁	X锁
S锁	兼容	不兼容
X锁	不兼容	不兼容

 

S锁和X锁都是行锁，兼容是指对同一记录（row）锁的兼容情况。

InnoDB存储引擎支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁（Intention Lock）。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁，如图6-3所示。

若将上锁的对象看成一颗树，那么对最下层的对象上锁，也就是对最细粒度的对象进行上锁，那么首先需要对粗粒度的对象上锁。如图所示，如果需要对页上的记录r进行上X锁，那么分别需要对数据库A、表、页上意向锁IX，最后对记录r上X锁。

若其中任何一部分导致等待，那么该操作需要等待粗粒度锁的完成。举例来说，

在对记录r加X锁之前，已经有事务对表1进行了S表锁，那么表1上已存在S锁，之后事务需要对记录r在表1上加上IX，由于不兼容，所以该事务需要等待表锁操作的完成。

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

1. 意向共享锁（IS Lock）,事务想要获得一张表中某几行的共享锁。
2. 意向排它锁（IX Lock）,事务想要获得一张表中某几行的排它锁。

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

 

一致性的非锁定读：一致性非锁定读是指InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行Delete或Update操作,这是读取操作不会因此去等待行上锁的释放。相反地，InnoDB存储引擎会去读取行的一个快照数据。

可以看到，非锁定读机制极大地提高了数据库的并发性。在InnoDB存储引擎的默认设置下，这是默认的读取方式，即读取不会占用和等待表上的锁。但是在不同事务隔离级别下，读取的方式不同，并不是在每个事务隔离级别下都是采用非锁定的一致性读。此外，即使都是使用非锁定的一致性读，但是对于快照数据的定义也各不相同。

一致性锁定读：在默认配置下，即事务的隔离级别为REPEATABLE READ模式下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT的只读操作。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读（locking read）操作：

SELECT…FOR UPDATE

SELECT…LOCK IN SHARE MODE

SELECT…FOR UPDATE 对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。SELECT…LOCK IN SHARE MODE 对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。

对于一致性非锁定读，即使读取的行已被执行了SELECT…FOR UPDATE，也是可以进行读取的。此外，SELECT…FOR UPDATE，SELECT…LOCK IN SHARE MODE必须在一个事务中，当事务提交了，锁也就释放了。因此在使用上述两句SELECT锁定语句时，务必加上BEGIN,START TRANSACTION或者SET AUTOCOMMIT=0。

自增长与锁

自增长在数据库中是非常常见的一种属性，也是很多DBA或开发人员首选的主键方式。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来获得计数器的值：

SELECT MAX(auto_inc_col) FROM t FOR UPDATE;

插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

虽然AUTO-INC Locking从一定程度上提高了并发插入的效率，但是还是存在一些性能上的问题。首先，对于有自增长值的列的并发插入性能较差，事务必须等待上一个插入的完成（虽然不用等待事务的完成）。其次，对于INSERT…SELECT的大数据量的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。

在InnoDB存储引擎中，自增长值的列必须是索引，同时是索引的第一个列。如果不是第一个列，则mysql数据库会抛出异常，而MyISAM存储引擎没有这个问题。

外键与锁

在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一个索引，因为这样避免表锁。

对于外键的插入或更新，首先需要查询父表中的记录，即SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题，因此这时使用的是SELECT…LOCK IN SHARE MODE方式，即主动对父表加一个S锁。如果这时父表上已经添加X锁，子表上的操作会被阻塞。

时间	会话1	会话2
1	BEGIN
2	DELETE FROM parent WHERE id=3;
3		BEGIN
4		INSERT INTO child SELECT2,3 #第二列是外键，执行该句时被阻塞（waiting）

 

在上述的例子中，两个会话中的事务都没有进行COMMIT或ROLLBACK操作，而会话B的操作会被阻塞。这时因为id为3的父表在会话A中已经加了一个X锁，而此时在会话B中用户又需要对id为3的行加一个S锁，这时INSERT的操作会被阻塞。设想如果访问父表时，使用的是一致性的非锁定读，这时Session B会读到父表有id=3的记录，可以进行插入操作。但是如果会话A对事务提交了，则父表中就不存在id为3的记录。数据在父、子表就会存在不一致的情况。

 

行锁的三种算法？

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

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。例如一个索引有10,11,13和20这四个值，那么该索引可能被Next-Key Locking的区间为：

(-无穷，10]

(10,11]

(11,13]

(13,20]

(20,+无穷)

采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计的目的是为了解决Phantom Problem。而利用这种锁定技术，锁定的不是单个值，而是一个范围，是谓词锁的一种改进。除了next-key locking,还有previous-key locking技术。同样上述的索引10,11,13和20若采用previous-key locking技术，那么可以锁定的区间为：

(-无穷，10)

[10,11)

[11,13)

[13,20)

[20,+无穷)

若事务T1已经通过next-key locking锁定了如下范围:

(10,11]、(11,13]

当插入新的记录12时，则锁定的范围变成

(10,11]、(11,12]、(12,13]

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

看下面的例子,首先根据如下代码创建测试表t：

CREATE TABLE t(a INT primary key);

INSERT INTO t SELECT 1;

INSERT INTO t SELECT 2;

INSERT INTO t SELECT 5;

接着来执行表6-12中的SQL语句。

表6-12

时间	会话A	会话B
1	BEGIN;
2	SELECT * FROM t WHERE a=5 FOR UPDATE;
3		BEGIN;
4		INSERT INTO t SELECT 4;
5		COMMIT; #成功，不需要等待
6	COMMIT

 

表t共有1、2、5三个值。在上面的例子中，在会话A中首先对a=5进行X锁定。而由于a是主键且唯一，因此锁定的仅是5这个值，而不是（2,5）这个范围，这样在会话B中插入值4而不会阻塞，可以立即插入并返回。即锁定由Next-Key Lock算法降级为Record Lock，从而提高应用的并发性。

正如前面所介绍的，Next-Key Lock降级为Record Lock仅在查询的类是唯一索引的情况下。若是辅助索引，则情况会完全不同。同样，创建测试表z

CREATE TABLE z(a INT,b INT,PRIMARY KEY(a),KEY(b));

INSERT INTO z SELECT 1,1;

INSERT INTO z SELECT 3,1;

INSERT INTO z SELECT 5,3;

INSERT INTO z SELECT 7,6;

INSERT INTO z SELECT 10,8;

表z的列b是辅助索引，若在会话A中执行下面的SQL语句：

SELECT * FROM z WHERE b=3 FOR UPDATE;

很明显，这时SQL语句通过索引列b进行查询，因此其使用的是传统的Next-Key Locking技术加锁，并且由于有两个索引，其需要分别进行锁定。对于聚集索引，其仅对列a等于5的索引加上Record Lock。而对于辅助索引，其加上的是Next-Key Lock，锁定的范围是(1,3]，特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock，即还有一个辅助索引范围为（3,6）的锁。因此，若在新会话B下运行下面的SQL语句，都会被阻塞：

SELECT * FROM z WHERE a=5 LOCK IN SHARE MODE;

INSERT INTO z SELECT 4,2;

INSERT INTO z SELECT 6,5;

第一个SQL不能执行，因为会话A中执行的SQL语句已经对聚集索引中列a=5的值加上了X锁，因此执行会被阻塞。第二个SQL语句，主键插入4没问题，但是插入的辅助索引值2在锁定的范围（1,3]中，因此执行同样会被阻塞。第三个SQL语句,插入的主键6没有被锁定，5也没有在(1,3]中，但插入的值在另一个锁定的范围(3,6)中，故同样需要等待。下面的语句则可以立即执行：

INSERT INTO z SELECT 8,6;

INSERT INTO z SELECT 2,0;

INSERT INTO z SELECT 6,7;

 

解决Phantom Problem

在默认的事务隔离级别下，即REPEATABLE READ下，InnoDB存储引擎采用Next-Key Locking机制来避免Phantom Problem（幻象问题）。

Phantom Problem 是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次执行SQL语句可能会返回之前不存在的行。下面将演示这个例子，创建表t,插入值1,2,5。

CREATE TABLE t(a INT primary key);

INSERT INTO t SELECT 1;

INSERT INTO t SELECT 2;

INSERT INTO t SELECT 5;

事务T1执行如下SQL语句：

SELECT * FROM t WHERE a>2 FOR UPDATE;

这时事务T1并没有进行提交操作，上述应该返回5这个结果。若与此同时，事务T2插入了4这个值，并且数据库允许该操作，那么事务T1再次执行上述的SQL会得到4,5这两个结果。这与第一次得到的结果不同，违反了事务的隔离性,即当前事务也能看到其他事务的结果。其过程如表6-13

时间	会话A	会话B
1	SET SESSION tx_isolation=’READ-COMMITTED’;
2	BEGIN;
3	SELECT * FROM t WHERE a>2 FOR UPDATE;
4		BEGIN;
5		INSERT INTO t SELECT 4;
6		COMMIT;
7	SELECT * FROM t WHERE a>2 FOR UPDATE;

 

InnoDB存储引擎采用Next-Key Locking的算法避免Phantom Problem。对于上述的SQL语句SELECT * FROM t WHERE a>2 FOR UPDATE;其锁住的不是5这单个值，而是对(2,+无穷)这个范围加了X锁。因此任何对于这个范围的插入都是不被允许的，从而避免了Phantom Problem。

InnoDB存储引擎默认的事务隔离级别是REPEATABLE READ,在该级别下，默认采用的是Next-Key Locking方式来加锁。而在事务隔离级别是READ COMMITTED下，其采用的是Record Lock，因此在上述的示例中，会话A需要将事务的隔离级别设置为READ COMMITTED。

此外，用户可以通过InnoDB存储引擎的Next-Key Locking机制在应用层面实现唯一性检查。例如：

SELECT * FROM table WHERE col=xxx LOCK IN SHARE MODE;

If not found any row：

# unique for insert value;

INSERT INTO table VALUES(……);

如果用户通过索引查询一个值，并对该行加上一个S Lock，那么即使查询的值不在，其锁定的也是一个范围，因此若没有返回任何行，那么新插入的值一定是唯一的。如果在进行第一步SELECT … LOCK IN SHARE MODE操作时，有多个事务并发操作，那么这种唯一性检查机制是否存在问题。其实并不会，因为这时会导致死锁，只有一个事务的插入操作会成功，而其余的事务会抛出死锁的错误，如表6-14

表6-14

时间	会话A	会话B
1	BEGIN;
2	SELECT * FROM z WHERE b=4 LOCK IN SHARE MODE;
3		SELECT * FROM z WHERE b=4 LOCK IN SHARE MODE;
4	INSERT INTO z SELECT 4,4; #阻塞
5		INSERT INTO z SELECT 4,4; ERROR 1213(40001); #抛出死锁异常
6	#INSERT插入成功

 

 

锁问题

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

 

脏读

脏数据是指未提交的数据，如果一个事务能够读到另一个事务中未提交的数据，显然违反了数据库中的隔离性。

脏读是指在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是读到脏数据。表6-15显示了一个脏读的例子。

表6-15

时间	会话A	会话B
1	SET @@tx_isolation=’read-uncommitted’
2		SET @@tx_isolation=’read-uncommited’
3		BEGIN;
4		SELECT * FROM t; 1 row in set
5	INSERT INTO t SELECT 2;
6		SELECT * FROM t; 2 row in set

 

表t为我们之前创建的表，不同的是在上述例子中，事务隔离级别进行了更换，由默认的REPEATABLE READ换成了READ UNCOMMITED。因此在会话A中，在事务还没有提交的前提下，会话B的两次查询结果并不一样。并且2这条记录是在会话A中并未提交的数据，因此产生了脏读，违反了事务的隔离性。脏读发生的条件是事务级别设置为READ UNCOMMITED。而目前大部分的数据库都设置为READ COMMITED。InnoDB存储引擎默认的事务隔离级别是READ REPEATABLE。

SQL SERVER的事务级别是READ COMMITED，ORACLE 数据库也是READ COMMITED。

 

不可重复读

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

不可重复读和脏读的区别是：脏读读到的是未提交的数据；不可重复读读到的是已提交的数据，但是其违反了数据库事务一致性的要求。

通过一个例子来观察不可重复读的情况，表6-16

表6-16不可重复读的示例

时间	会话A	会话B
1	SET@@tx_isolation=’read-committed’
2		SET@@tx_isolation=’read-committed’
3	BEGIN;	BEGIN;
4	SELECT * FROM t； 1 row in set
5		INSERT INTO t SELECT 2;
6		COMMIT
7	SELECT * FROM t 2 row in set

 

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

一般来说，不可重复读的问题是可以接受的，因为其读到的是已经提交的数据，本身并不会带来很大的问题。因此，很多数据库厂商将其数据库事务的默认隔离级别设置为READ COMMITTED,在这种事务隔离级别下允许不可重复读的现象。

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

 

丢失更新

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

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

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

但是在生产应用中还有另一个逻辑意义的丢失更新问题，例如下面这种情况：

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

显然，这个过程中用户User1的修改更新操作“丢失”了，而这可能会导致一个“恐怖”的结果。

要避免丢失更新发生，需要让事务在这种情况下的操作变成串行化，而不是并行的操作。表6-17所示的过程演示了如何避免这种逻辑上丢失更新问题的产生。

 

 

表6-17 丢失更新问题的处理方法

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

 

死锁

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