不同范围的锁

不同范围的锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁，同样地，当 for update 的记录不存在会导致锁住全表。当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

mysql> select * from tab_no_index where id = 1 for update;

--- 添加索引
mysql> alter table tab_with_index add index id(id);

--- 在没有索引的情况下，InnoDB 只能使用表锁，会导致下述查询语句等待
--- 当我们给其增加一个索引后，InnoDB 就只锁定了符合条件的行，不会出现锁等待
mysql> select * from tab_no_index where id = 2 for update;

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。譬如当id字段有索引，name字段没有索引：

mysql> select * from tab_with_index where id = 1 and name = '1' for update;

--- 虽然 session_2 访问的是和 session_1 不同的记录，但是因为使用了相同的索引，所以需要等待锁
mysql> select * from tab_with_index where id = 1 and name = '4' for update;

即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。

  • 事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
  • 事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。
--- 写表t1并从表t读
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

案例：删除表的策略

如果你要删除一个表里面的前10000行数据，有以下三种方法可以做到：

  • 第一种，直接执行 delete from T limit 10000;，事务相对较长，则占用锁的时间较长，会导致其他客户端等待资源时间较长。
  • 第二种，在一个连接中循环执行20次 delete from T limit 500;，串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间相对较短，其他客户端在等待相应资源的时间也较短。这样的操作，同时也意味着将资源分片使用（每次执行使用不同片段的资源），可以提高并发性。
  • 第三种，在20个连接中同时执行 delete from T limit 500，为自己制造锁竞争，加剧并发量。

锁分类

根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。其中：

  • 全局锁：对整个数据库实例加锁，常用于全库逻辑备份。
  • 表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。
  • 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高；
  • 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

每个存储引擎都可以有自己的锁策略，例如MyISAM引擎仅支持表级锁，而InnoDB引擎除了支持表级锁外，也支持行级锁（默认）。

行锁 表锁 页锁
MyISAM √
BDB √ √
InnoDB √ √

MySQL 5.5中，information_schema库中新增了三个关于锁的表，亦即innodb_trx、innodb_locks和innodb_lock_waits。其中innodb_trx表记录当前运行的所有事务，innodb_locks表记录当前出现的锁，innodb_lock_waits表记录锁等待的对应关系。

全局锁

全局锁即对整个数据库实例加锁。MySQL提供加全局读锁的方法：Flush tables with read lock(FTWRL)。这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。风险是如果在主库备份，在备份期间不能更新，业务停摆。如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟。官方自带的逻辑备份工具mysqldump，当mysqldump使用参数–single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

MySQL里面表级锁有两种，一种是表锁，一种是元数据锁(meta data lock,MDL)。表锁的语法是: lock tables ... read/write，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

另一类表级的锁是MDL（metadata lock)。MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。MDL会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的，这也是MyISAM被InnoDB替代的重要原因之一。在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。譬如事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙（GAP)，InnoDB也会对这个间隙加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

举例来说，假如emp表中只有101条记录，其empid的值分别是1,2,…,100,101，mysql> select * from emp where empid > 100 for update;就是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101这些记录并不存在的间隙加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

--- 当前session对不存在的记录加for update的锁：
mysql> select * from emp where empid = 102 for update;

--- 这时，如果其他session插入empid为102的记录（注意：这条记录并不存在），也会出现锁等待：
mysql>insert into emp(empid,...) values(102,...);

--- session 1 执行 rollback：
mysql> rollback;

--- 由于其他session_1回退后释放了Next-Key锁，当前session可以获得锁并成功插入记录：
mysql>insert into emp(empid,...) values(102,...);

最近更新于0001-01-01