uncategorized

MySQL锁机制总结

实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。
MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。
在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

在MVCC并发控制中，读操作可以分成两类：

  1. 快照读: 读到的是记录的可见版本，不用加锁。
  2. 当前读: 读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证不会被其他记录修改。

对于大部分的SELECT语句，InnnerDB用的是快照读，因此不用加锁。也有例外， 比如事务隔离级别为”Serializable”。
其他的情况，比如:

  • Select * from Table Where for update
  • Select * from Table Where ? lock in share mode
  • Update * from Table Where ?
  • Delete from Table Where ?
  • Insert into Table Values(?)
    这些都是当前读

影响一个事务或者SQL语句锁类型和锁机制的因素

  1. 数据库存储引擎(InnerDB, MyISAM和MEMORY, BDB)
  2. 数据库事务隔离级别
  • Read Uncommited: 事务A可以看到其他事务没有Commit的修改， 但是这些commit可能会被回滚，从而导致”脏读”。
    这种情况基本上不太会使用
  • Read Commited: 事务A只能看到其他事务已经commit的修改，会有两次读到的结果不一致的情况， 出现”不可重复度”。
    只保证对读到的记录加锁，存在”幻想读”的问题
  • Repeatable Read: 事务A对其他事务的修改(commited/uncommited)都不可见，从而保证A事务的可重复度。
    但是A提交事务的时候，记录可能已经被修改
  • Serializable: 所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。读的时候不能写，写的时候不能读。
    重用的隔离级别：RC和RR
  1. Where子句是否包含包含主键，如果是只需要加对主键索引加X锁就可以了。（RC,RR）
  2. Where子句是否包含唯一类型的索引，如果是需要对记录本上加X锁, 然后对应的主键索引也要加X锁。(RC, RR)
  3. Where子句是否包含非唯一类型的索引，如果是需要加Gap锁，锁住两条记录的”间隙”，防止被插入。

    注: RC级别的隔离，不会加Gap锁

  4. Where子句没有用到索引，即”全表扫”，这个时候默认对全表的记录加锁，并且所有”间隙”加Gap锁。

    注: RC级别的隔离，不会加Gap锁

    这个情况下，MySQL也做了一些优化，就是所谓的semi-consistent read。
    semi-consistent read开启的情况下，同事事务隔离级别必须为RC或者RR，对于不满足查询条件的记录，MySQL会提前放锁。

  5. 4和5混合的情况，这种情况需要结合4，5的处理规则具体情况分析。

    锁类型

    意向锁

    意向锁是表级别的锁，主要是为了解决行锁和表锁之间的冲突，不会阻塞全表扫描之外的其他操作。
    鉴于平时表级操作特别少，因此分析加锁过程的时候，可以忽略意向锁。

行锁

  1. Record Lock：单个行记录上的锁。这种类型的锁加在主键索引上的
  2. Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
  3. Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。这种锁加载行记录上。

插入意向锁

对于已经获得Next-Key Lock的事务执行插入操作，需要请求插入意向锁(Insert intention lock)
插入意向锁和Gap Lock是不兼容的，必须等待其他事务释放间隙锁。

Unique查询，对应三种加锁策略

  1. 找到满足条件的记录，并且记录有效，则对记录加X锁，No Gap锁(lock_mode X locks rec but not gap)
  2. 找到满足条件的记录，但是记录无效(标识为删除的记录)，则对记录加next key锁(同时锁住记录本身，以及记录之前的Gap：lock_mode X)
  3. 未找到满足条件的记录，则对第一个不满足条件的记录加Gap锁，保证没有满足条件的记录插入(locks gap before rec)
    这种策略可能不容易理解，但是在下面的实例中就很容易理解了

实例

表结构

1
2
3
4
5
6
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_b` (`stage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

表数据

id stage
1 a
4 b
9 c
15 d

事务执行时序

T1(41121) T1(41122)
begin; begin;
select * from t where stage = ‘b’ for update;
select * from t where stage = ‘c’ for update;
insert INTO t values (6, e)
insert INTO t values (7, f)
’Deadlock found when trying to get lock; try restarting transaction’
Query OK, 1 row affected

T1的加锁情况

  1. 表意向排他锁(IX)
  2. Record Lock, 记录行锁(id=4)
  3. Next-Key Lock， (a, b]， 闭区间表示(stage=b)
  4. Gap Lock, (b, c)
  5. 插入意向锁(等待)

    T2的加锁情况(insert语句之前的位置)

  6. 表意向排他锁(IX)
  7. Record Lock, 记录行锁(id=9)
  8. Next-Key Lock， (b, c]， 闭区间表示(stage=c)
  9. Gap Lock, (c, d)

分析

  1. T1 Insert的时候申请在(b,c)位置的插入意向锁，此时T2持有(b, c]上的Next-Key Lock，进入等待
  2. T2 Insert的时候申请在(b,c)位置的插入意向锁，此时T1持有(b, c)上的Gap Lock， 进入等待

死锁发生，根据事务的优先级，T2会被回滚。

分享到： QQ空间 新浪微博 腾讯微博 人人网 微信