你的位置:首页 > 数据库

[数据库]MySQL锁机制总结(二)


前言:

Mysql是一个支持插件式存储引擎的数据库系统,本文讨论的锁机制也主要包含两部分SERVER层的锁和存储引擎的锁,存储引擎是指innodb,其它存储引暂不讨论。              

1. 数据库中锁相关的基本概念

1)  乐观锁,悲观锁

      乐观锁和悲观锁都是一种并发控制策略。悲观锁假定多个事务会同时访问同一个资源,采用的策略是“先上锁,后访问”,这种策略会有死锁的风险。乐观锁相对于悲观锁而言,假定多个事务在运行过程中不会相互影响,写入在读取和写入记录时,不上锁,取而代之是产生一个时间戳或版本号,事务提交阶段,检查记录的版本号是否有被修改(若修改,则表示有其他事务读写),确定是否需要回滚事务。目前在数据库领域,几乎所有的DBMS都是采用悲观锁机制。

2)  MVCC(Multi-Version Concurrency Control)

      MVCC也是一种并发控制方法,MVCC对悲观锁控制机制做了改进,通过冗余数据的历史版本,达到“读不上锁,读写不冲突”的效果,提高了并发效果。MVCC主要作用于读提交和可重复读两种隔离级别上。

3)  两阶段锁协议

      所谓两段锁协议是指上锁分为两个阶段,加锁和解锁阶段,保证加锁和解锁阶段不交错。对于数据库系统而言,事务开始时,处于加锁阶段;事务提交或rollback时,事务进入解锁阶段。只有满足两段锁协议的数据库系统,并发调度的事务才是可以串行化的。

4)  意向锁

     意向锁机制约定如果对一个节点加锁,必需先对它的上一层节点加意向锁。比如,对一个记录加锁之前,首先对该记录所在的表加意向锁。意向锁主要包括IS和IX,他们与S和X的兼容关系不在这里赘述。意向锁的主要作用在于提高表锁和行锁冲突检测效率。

5) 表锁,记录(行)锁

      表锁和记录锁是锁系统里面最基本的锁。分别用于锁定表和记录。对于表而言,根据情况可以有S,X,IS和IX四种锁类别;对表上IS和IX,表示需要读记录和写记录;记录锁则主要包括X锁和S锁。有关行锁的实现,可以参考之前的文章,INNODB行锁源码学习

6) 字典锁(metadata lock)

    字典锁是保护元数据的一种锁,主要为了防止DDL和DML冲突的情况。有关MDL的详细介绍,可以参考之前的文章,MYSQL METADATA LOCK

7)  死锁

    所谓死锁是指两个或多个事务,各自占有对方的期望获得的资源,形成的循环等待,彼此无法继续执行的一种状态。 

2. 举个栗子

   上面列了这么多种类的锁,下面通过一个简单的例子说明各种锁是如何作用的,它们加锁和释放锁的先后顺序如何。这里假设隔离级别是RC,ID为主键。

begin:update t3 set c1=1 where id=1;commit;

流程

执行语句

执行内容

字典锁

行锁/表锁

1

Begin

 

释放MDL

release_transactional_locks

释放表锁,行锁

2

update t3 set c1=1 where id=1;

 

上字典锁

GLOBAL:STATMENT

MDL_INTENTION_EXCLUSIVE

 

3

TABLE:TRANSACTION

MDL_SHARED_WRITE

 

4

上行锁

 

LOCK_TABLE:IX

(table:t3)

5

 

LOCK_REC:X

(id=1)

6

执行更新

 

 

7

释放MDL

GLOBAL:STATMENT

 

8

commit;

 

COMMIT

字典锁

COMMIT: MDL_EXPLICIT

MDL_INTENTION_EXCLUSIVE

 

9

执行提交

 

 

10

释放引擎锁

 

lock_release

11

释放MDL

 

COMMIT: MDL_EXPLICIT

MDL_INTENTION_EXCLUSIVE

 

12

release_transactional_locks

TABLE:TRANSACTION

 

      可以看到,第一行begin,表示开启一个新事务,隐含提交会话的上一个事务,需要释放之前的锁。第2到7行是两阶段锁中的上锁阶段,分别先后上了字典锁、表的意向锁和行锁。上完锁后,才开始真正的更新阶段,从这里也可以看到MySQL的写操作是符合悲观锁策略。第4行和第5行,我们可以看到意向锁是如何运作的,上记录id=1的行锁之前,先对表t3上了意向锁。第7行,语句执行完后,可以释放STATEMENT级别的字典锁,避免长时间持有锁阻塞该表的DDL操作。8-12是提交阶段,进入两段锁中的释放锁过程,先后释放引擎层的表锁和行锁;然后释放TRANSACTION级别的MDL锁。

3. 常用语句加锁分析

假设隔离级别:RC,id为主键

典型语句

SQL层面(MDL锁)

存储引擎

innodb

 

 

       范围/对象

持有时间

表锁

行锁

SELECT操作

SELECT * FROM T

TABLE:

MDL_SHARED_READ

MDL_TRANSACTION

None

None

Show create table T

TABLE:

MDL_SHARED_HIGH_PRIO

MDL_TRANSACTION

 

 

LOCK TABLE T READ

TABLE:

MDL_SHARED_READ

MDL_TRANSACTION

None

None

LOCK TABLE T WRITE

GLOBAL:

MDL_INTENTION_EXCLUSIVE

MDL_STATEMENT

 

None

None

SCHEMA:

MDL_INTENTION_EXCLUSIVE

TABLE:

MDL_SHARED_NO_READ_WRITE

 

TRANSACTION

Flush table t with read lock

TABLE:

MDL_SHARED_NO_WRITE

TRANSACTION

None

None

Flush table with read lock

GLOBAL:

MDL_SHARED

 

MDL_EXPLICIT

None

None

COMMIT:

MDL_SHARED

 

MDL_EXPLICIT

DML操作

SELECT * FROM T FOR UPDATE;

Update T set c1=? Where id=?

GLOBAL:

MDL_INTENTION_EXCLUSIVE

MDL_STATEMENT

IX

X

TABLE:

MDL_SHARED_WRITE

TRANSACTION

 

DDL操作

Alter table t add column c1 int;

Truncate table t;

GLOBAL:

MDL_INTENTION_EXCLUSIVE

 

MDL_STATEMENT

None

X

SCHEMA:

MDL_INTENTION_EXCLUSIVE

TABLE:

MDL_EXCLUSIVE

TRANSACTION

 

COMMIT:

MDL_INTENTION_EXCLUSIVE

MDL_EXPLICIT

Set global read_only=1;

GLOBAL:

MDL_SHARED

COMMIT:

MDL_SHARED

MDL_EXPLICIT

 

None

 

None