1.锁的类型
InnoDB存储引擎实现了如下两种标准的行级锁:
- 共享锁( S Lock),允许事务读一行数据。
- 排他锁( X LocK),允许事务删除或更新一行数据。
如果一个事务T1已经获得了行r的共享锁,那么另外的事务T2可以立即获得行r的共享锁,因为读取并没有改变行r的数据,称这种情况为锁兼容( Lock Compatible)。
但若有其他的事务T3想获得行r的排他锁,则其必须等待事务T1、T2释放行r上的共享锁——这种情况称为锁不兼容。下表显示了共享锁和排他锁的兼容性。
排他锁和共享锁的兼容性
X
S
X
不兼容
不兼容
S
不兼容
兼容
从表中可以发现X锁与任何的锁都不兼容,而S锁仅和S锁兼容。需要特别注意的是,S和X锁都是行锁,兼容是指对同一记录(row)锁的兼容性情况。
此外, InnoDB存储引擎支持多粒度(granular)锁定,这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作, InnoDB存储引擎支持一种额外的锁方式,称之为意向锁(Intention lock)。意向锁是将锁定的对象分为多个层次,意向锁意味着事务希望在更细粒度(fine granularity)上进行加锁,如下图所示。
若将上锁的对象看成一棵树,那么对最下层的对象上锁,也就是对最细粒度的对象进行上锁,那么首先需要对粗粒度的对象上锁。例如图6-3,如果需要对页上的记录r进行上X锁,那么分别需要对数据库A、表、页上意向锁IX,最后对记录r上ⅹ锁。若其中任何一个部分导致等待,那么该操作需要等待粗粒度锁的完成。举例来说,在对记录r加ⅹ锁之前,已经有事务对表1进行了S表锁,那么表1上已存在S锁,之后事务需要对记录r在表1上加上IX,由于不兼容,所以该事务需要等待表锁操作的完成。
InnoDB存储引擎支持意向锁设计比较简练,其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁:
- 意向共享锁( IS Lock),事务想要获得一张表中某几行的共享锁
- 意向排他锁( IX Lock),事务想要获得一张表中某几行的排他锁
由于 InnoDB存储引擎支持的是行级别的锁,因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下表所示。
InnoDB存储引擎中锁的兼容性
IS
IX
S
X
IS
兼容
兼容
兼容
不兼容
IX
兼容
兼容
不兼容
不兼容
S
兼容
不兼容
兼容
不兼容
X
不兼容
不兼容
不兼容
不兼容
用户可以通过命令 SHOW ENGINE INNODB STATUS命令来查看当前锁请求的信息：
可以看到SQL语句 select* from t where a<4 lock in share mode在等待, RECORD LOCKS space id 30 page no 3 n bits 72 index' PRIMARY 'of table'test''t trx id 48B89BD lock mode X locks rec but not gap表示锁住的资源。 locks rec but not gap代表锁住的是一个索引,不是一个范围。
在InnoDB1.0版本之前,用户只能通过命令 SHOW FULL PROCESSLIST,SHOW ENGINE INNODB STATUS等来查看当前数据库中锁的请求,然后再判断事务锁的情况。从 InnoDB1.0开始,在 INFORMATION SCHEMA架构下添加了表 INNODB_TRX、INNODB_LOCKS、 INNODB_LOCK_WAITS。通过这三张表,用户可以更简单地监控当前事务并分析可能存在的锁问题。我们将通过具体的示例来分析这三张表,在之前,首先了来看下表中表 INNODB_TRX的定义,其由8个字段组成。
表INNODB_TRX的结构说明
字段名
说明
trx_id
InnoDB存储引擎内部唯一的事务ID
trx_state
当前事务的状态
trx_started
事务的开始时间
trx_requested_lock_ id
等待事务的锁ID。如 trx_state的状态为 LOCK WAIT,那么该值代表当前的事务等待之前事务占用锁资源的ID。若 trx_state不是 LOCK WAIT,则该值为NULL
trx_wait_started
事务等待开始的时间
trx_weight
事务的权重,反映了一个事务修改和锁住的行数。在 InnoDB存储引擎中,当发生死锁需要回滚时, InnoDB存储引擎会选择该值最小的进行回滚
trx_mysql_thread_id
MySQL中的线程ID, SHOW PROCESSLIST显示的结果
trx_query
事务运行的SQL语句
接着来看一个具体的例子: 
通过列state可以观察到trx_id为730FEE的事务当前正在运行,而trx_id为7311F4的事务目前处于“ LOCK WAIT”状态,且运行的SQL语句是 select * from parent lock in share mode。该表只是显示了当前运行的 InnoDB事务,并不能直接判断锁的一些情况。
如果需要查看锁,则还需要访问表 INNODB_LOCKS,该表的字段组成如下表所示。
表INNODB_LOCKS的结构
字段名
说明
lock_id
锁的ID
lock_trx_id
事务ID
lock_mode
锁的模式
lock_type
锁的类型,表锁还是行锁
lock_table
要加锁的表
lock_index
锁住的索引
lock_space
锁对象的 space id
lock_page
事务锁定页的数量。若是表锁,则该值为NULL
lock_rec
事务锁定行的数量,若是表锁,则该值为NULL
lock_data
事务锁定记录的主键值,若是表锁,则该值为NULL
接着上面的例子,继续查看表 INNODB_LOCKS：
这次用户可以清晰地看到当前锁的信息。 trx_id为730FEE的事务向表 parent加了一个X的行锁,ID为7311F4的事务向表parent申请了一个S的行锁。 lock_data都是1,申请相同的资源,因此会有等待。这也可以解释INNODB_TRX中为什么一个事务的trx_state是“ RUNNING”,另一个是“ LOCK WAIT”了。
另外需要特别注意的是,我发现 lock_data这个值并非是“可信”的值。例如当用户运行一个范围査找时, lock_data可能只返回第一行的主键值。与此同时,如果当前资源被锁住了,若锁住的页因为 InnoDB存储引擎缓冲池的容量,导致该页从缓冲池中被刷出,则査看 INNODB LOCKS表时,该值同样会显示为NUL,即 InnoDB存储引擎不会从磁盘进行再一次的查找。
在通过表 INNODB_LOCKS查看了每张表上锁的情况后,用户就可以来判断由此引发的等待情况了。当事务较小时,用户就可以人为地、直观地进行判断了。但是当事务量非常大,其中锁和等待也时常发生,这个时候就不这么容易判断。但是通过表INNODB_LOCK_WAITS,可以很直观地反映当前事务的等待。表 INNODB_LOCK_WAITS由4个字段组成,如下表所示。
表INNODB_LOCK_WAITS的结构
字段
说明
requesting_trx_id
申请锁资源的事务
blocking_trx_id
阻塞的事务ID
requesting_lock_id
申请的锁的ID
blocking_lock_id
阻塞的锁的ID
接着上面的例子,运行如下查询:
mysql> SELECT* FROM information_schema.INNODB_LOCK_WAITS\G; *****************************1.row***************************************** requesting_trx_id: 7311F4 requested_lock_id: 7311F4:96:3:2 blocking_trx_id: 730FEE blocking_lock_id: 730FEE:96:3:2 1 row in set (0.00 sec)
通过上述的SQL语句,用户可以清楚直观地看到哪个事务阻塞了另一个事务。
当然,这里只给出了事务和锁的ID。如果需要,用户可以根据表 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS得到更为直观的详细信息。例如,用户可以执行如下联合查询:
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G;
2.一致性非锁定读
一致性的非锁定读( consistent nonlocking read)是指 InnoDB存储引擎通过行多版本控制(multi versioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE或 UPDATE操作,这时读取操作不会因此去等待行上锁的释放。相反地, InnoDB存储引擎会去读取行的一个快照数据。如图所示。 
图直观地展现了 InnoDB存储引擎一致性的非锁定读。之所以称其为非锁定读,因为不需要等待访问的行上X锁的释放。
快照数据是指该行的之前版本的数据,该实现是通过undo段来完成。而 undo用来在事务中回滚数据,因此快照数据本身是没有额外的开销。此外,读取快照数据是不需要上锁的,因为没有事务需要对历史的数据进行修改操作。
可以看到,非锁定读机制极大地提高了数据库的并发性。在 InnoDB存储引擎的默认设置下,这是默认的读取方式,即读取不会占用和等待表上的锁。但是在不同事务隔离级别下,读取的方式不同,并不是在每个事务隔离级别下都是采用非锁定的一致性读。此外,即使都是使用非锁定的一致性读,但是对于快照数据的定义也各不相同。
通过图6-4可以知道,快照数据其实就是当前行数据之前的历史版本,每行记录可能有多个版本。就图6-4所显示的,一个行记录可能有不止一个快照数据,一般称这种技术为行多版本技术。由此带来的并发控制,称之为多版本并发控制(Multi version Concurrency Control, MVCC)。
在事务隔离级别 READ COMMITTED和 REPEATABLE READ( InnoDB存储引擎的默认事务隔离级别)下, InnoDB存储引擎使用非锁定的一致性读。然而,对于快照数据的定义却不相同。在 READ COMMITTED事务隔离级别下,对于快照数据,非一致性读总是读取被锁定行的最新一份快照数据。而在REPEATABLE READ事务隔离级别下,对于快照数据,非一致性读总是读取事务开始时的行数据版本。来看下面的一个例子,首先在当前 MySQL数据库的连接会话A中执行如下SQL语句:
#Session A mysql> BEGIN; Query OK,0 rows affected (0.00 sec) mysql> SELECT * FROM parent WHERE id =1; id 1
会话A中已通过显式地执行命令 BEGIN开启了一个事务,并读取了表 parent中id为1的数据,但是事务并没有结束。与此同时,用户再开启另一个会话B,这样可以模拟并发的情况,然后对会话B做如下的操作
#Session B mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE parent set id=3 WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
在会话B中将事务表 parent中i为1的记录修改为id=3,但是事务同样没有提交,这样id=1的行其实加了一个X锁。这时如果在会话A中再次读取id为1的记录,根据 InnoDB存储引擎的特性,即在 READ COMMITTED和 REPEATETABLE READ的事务隔离级别下会使用非锁定的一致性读。回到之前的会话A,接着上次未提交的事务,执行SQL语句 SELECT* FROM parent WHERE id=1的操作,这时不管使用READ COMMITTED还是 REPEATABLE READ的事务隔离级别,显示的数据应该都是:
#Session A mysql> SELECT FROM parent WHERE id = 1; id 1 1 row in set (0.00 sec)
由于当前id=1的数据被修改了1次,因此只有一个行版本的记录。接着,在会话B中提交上次的事务:
# Session B mysql> commit Query OK, 0 rows affected (0.01 sec)
在会话B提交事务后,这时在会话A中再运行 SELECT * FROM parent WHERE id=1的SQL语句,在READ COMMITTED和REPEATABLE事务隔离级别下得到结果就不一样了。对于READ COMMITTED的事务隔离级别,它总是读取行的最新版本,如果行被锁定了,则读取该行版本的最新一个快照(fresh snapshot)。在上述例子中,因为会话B已经提交了事务,所以READ COMMITTED事务隔离级别下会得到如下结果:
mysql> SELECT @@tx_isolation\G; ******************1.row******************** @@tx_isolation: READ-COMMITTED l row in set (0.00 sec) mysql> SELECT FROM parent WHERE id =1; Empty set (0.00 sec)
而对于 REPEATABLE READ的事务隔离级别,总是读取事务开始时的行数据。因此对于REPEATABLE READ事务隔离级别,其得到的结果如下:
mysql> SELECT @@tx_isolation\G; ******************1.row******************** @@tx_isolation:: REPEATABLE-READ 1 row in set (0.00 sec) mysql> SELECT FROM parent WHERE id = 1; id 1 1 row in set (0.00 sec)
下面将从时间的角度展现上述演示的示例过程,如下表所示。需要特别注意的是,对于READ COMMITTED的事务隔离级别而言,从数据库理论的角度来看,其违反了事务ACID中的I的特性,即隔离性。
示例执行的过程
时间
会话A
会话B
1
BEGIN
2
SELECT * FROM parent WHERE id= 1
3
BEGIN
4
UPDATE parent SET id=3 WHERE id=1
5
SELECT * FROM parent WHERE id=1
6
COMMIT
7
SELECT * FROM parent WHERE id=1;
8
COMMIT
3.一致性锁定读
在默认配置下,即事务的隔离级别为 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。
4.自增长与锁
自增长在数据库中是非常常见的一种属性,也是很多DBA或开发人员首选的主键方式。在 InnoDB存储引擎的内存结构中,对每个含有自增长值的表都有一个自增长计数器(auto-increment counter)。当对含有自增长的计数器的表进行插入操作时,这个计数器会被初始化,执行如下的语句来得到计数器的值:
SELECT MAX (auto_inc_col) FROM t FOR UPDATE;
插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制,为了提高插入的性能,锁不是在一个事务完成后才释放,而是在完成对自增长值插入的SQL语句后立即释放。
虽然AUTO-INC Locking从一定程度上提高了并发插入的效率,但还是存在一些性能上的问题。首先,对于有自增长值的列的并发插入性能较差,事务必须等待前一个插入的完成(虽然不用等待事务的完成)。其次,对于 INSERT… SELECT的大数据量的插人会影响插人的性能,因为另一个事务中的插入会被阻塞。
从 MySQL5.1.22版本开始, InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制,这种机制大大提高了自增长值插入的性能。并且从该版本开始, InnoDB存储引擎提供了一个参数 innodb_autoinc_lock_mode来控制自增长的模式,该参数的默认值为1。
在继续讨论新的自增长实现方式之前,需要对自增长的插入进行分类,如下表所示：
插入类型
插入类型
说明
insert-like
指所有的插入语句,如 INSERT、REPLACE、INSERT… SELECT,REPLACE… SEECT、LOAD DATA等
simple inserts
simple inserts指能在插入前就确定插入行数的语句。这些语句包括INSERT、REPLACE等。需要注意的是; simple inserts不包含 INSERT… ON DUPLICATE KEY UPDATE这类SQL语句
bulk inserts
指在插入前不能确定得到插人行数的语句,如 INSERT… SELECT,REPLACE ... SELECT, LOAD DATA
mixed-mode inserts
指插入中有一部分的值是自增长的,有一部分是确定的。如INSERT INTO t (c1,c2) VALUES(1,'a'),(NULL,'b'),(5,'c'),(NULL,'d');也可以是指INSERT… ON DUPLICATE KEY UPDATE这类SQL语句
接着来分析参数 innodb_autoinc_lock_mode以及各个设置下对自增的影响,其总共有三个有效值可供设定,即0、1、2,具体说明如表所示。
参数 innodb_autoinc_lock_mode的说明
innodb_autoinc_lock_mode
说明
0
这是 MySQL5.1.22版本之前自增长的实现方式,即通过表锁的AUTO-INC Locking方式。因为有了新的自增长实现方式,0这个选项不应该是新版用户的首选项。
1
这是该参数的默认值。对于“ simple inserts”,该值会用互斥量(mutex)去对内存中的计数器进行累加的操作。对于“bulk inserts”,还是使用传统表锁的AUTO-INC Locking方式。在这种配置下,如果不考虑回滚操作,对于自增值列的增长还是连续的。并且在这种方式下, statement-based方式的 replication还是能很好地工作。需要注意的是,如果已经使用 AUTO-INC Locing方式去产生自增长的值,而这时需要再进行“simple inserts”的操作时,还是需要等待 AUTO-INC Locking的释放。
2
在这个模式下,对于所有“ INSERT-like”自增长值的产生都是通过互斥量,而不是AUTO-INC Locking的方式。显然,这是性能最高的方式。然而,这会带来定的问题。因为并发插人的存在,在每次插入时,自增长的值可能不是连续的。此外,最重要的是,基于 Statement- Base Replication会出现问题。因此,使用这个模式,任何时候都应该使用row-base replication。这样才能保证最大的并发性能及replication主从数据的一致。
此外,还需要特别注意的是InnoDB存储引擎中自增长的实现和 MyISAM不同。MyIsAM存储引擎是表锁设计,自增长不用考虑并发插入的问题。因此在master上用InnoDB存储引擎,在slave上用 MyISAM存储引擎的 replication架构下,用户必须考虑这种情况。
另外,在 InnoDB存储引擎中,自增长值的列必须是索引,同时必须是索引的第一个列。如果不是第一个列,则 MySQL数据库会抛出异常,而 MyISAM存储引擎没有这个问题,下面的测试反映了这两个存储引擎的不同。 
5.外键和锁
外键主要用于引用完整性的约束检查。在 InnoDB存储引擎中,对于一个外键列,如果没有显式地对这个列加索引, InnoDB存储引擎自动对其加一个索引,因为这样可以避免表锁——这比 Oracle数据库做得好, Oracle数据库不会自动添加索引,用户必须自已手动添加,这也导致了 Oracle数据库中可能产生死锁。
对于外键值的插入或更新,首先需要查询父表中的记录,即 SELECT父表。但是对于父表的 SELECT操作,不是使用一致性非锁定读的方式,因为这样会发生数据不一致的问题,因此这时使用的是 SELECT… LOCK IN SHARE MODE方式,即主动对父表加一个S锁。如果这时父表上已经这样加X锁,子表上的操作会被阻塞,如下表所示。
外键测试用例
时间
会话A
会话B
1
BEGIN
2
DELETE FROM parent WHERE id=3;
3
BEGIN
4
INSERT INTO child SELECT 2,3
#第二列是外键,执行该句时被阻塞(waiting)
在上述的例子中,两个会话中的事务都没有进行COMMIT或ROLLBAGK操作,而会话B的操作会被阻塞。这是因为id为3的父表在会话A中已经加了一个X锁,而此时在会话B中用户又需要对父表中id为3的行加一个S锁,这时INSERT的操作会被阻塞。设想如果访问父表时,使用的是一致性的非锁定读,这时 Session B会读到父表有id=3的记录,可以进行插入操作。但是如果会话A对事务提交了,则父表中就不存在id为3的记录。数据在父、子表就会存在不一致的情况。若这时用户查询INNODB_LOCKS表,会看到如下结果:
