InnoDB存储引擎实现了如下两种标准的行级锁:
共享锁( S Lock),允许事务读一行数据。
排他锁( X LocK),允许事务删除或更新一行数据。
如果一个事务T1已经获得了行r的共享锁,那么另外的事务T2可以立即获得行r的共享锁,因为读取并没有改变行r的数据,称这种情况为锁兼容( Lock Compatible)。
但若有其他的事务T3想获得行r的排他锁,则其必须等待事务T1、T2释放行r上的共享锁——这种情况称为锁不兼容。下表显示了共享锁和排他锁的兼容性。
X
S
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存储引擎支持的是行级别的锁,因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下表所示。
IS
IX
S
X
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个字段组成。
字段名
说明
字段名
说明
trx_id
InnoDB存储引擎内部唯一的事务ID
trx_state
当前事务的状态
trx_started
事务的开始时间
trx_requested_lock_id
等待事务的锁ID。如trx_state的状态为LOCKWAIT,那么该值代表当前的事务等待之前事务占用锁资源的ID。若trx_state不是LOCKWAIT,则该值为NULL
trx_wait_started
事务等待开始的时间
trx_weight
事务的权重,反映了一个事务修改和锁住的行数。在InnoDB存储引擎中,当发生死锁需要回滚时,InnoDB存储引擎会选择该值最小的进行回滚
trx_mysql_thread_id
MySQL中的线程ID,SHOWPROCESSLIST显示的结果
trx_query
事务运行的SQL语句
接着来看一个具体的例子:
通过列state可以观察到trx_id为730FEE的事务当前正在运行,而trx_id为7311F4的事务目前处于“ LOCK WAIT”状态,且运行的SQL语句是 select * from parent lock in share mode。该表只是显示了当前运行的 InnoDB事务,并不能直接判断锁的一些情况。
如果需要查看锁,则还需要访问表 INNODB_LOCKS,该表的字段组成如下表所示。
字段名
说明
字段名
说明
lock_id
锁的ID
lock_trx_id
事务ID
lock_mode
锁的模式
lock_type
锁的类型,表锁还是行锁
lock_table
要加锁的表
lock_index
锁住的索引
lock_space
锁对象的spaceid
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个字段组成,如下表所示。
字段
说明
字段
说明
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;