mysql 选择锁类型_【MySQL技术内幕】35-锁的类型

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;