🇨🇳Ynthm
文章 标签 分类 关于
🇨🇳Ynthm

目录

MySQL InnoDB 七种锁

Ynthm Wang 收录于 Database
   约 5321 字   预计阅读 11 分钟 

锁的分类

  • 按操作划分，可分为DML锁、DDL锁

  • 按锁的粒度划分，可分为 表级锁、行级锁、页级锁

    • 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
    • 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
    • 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
    • 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
    • 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁
    • 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

  • 按锁级别划分，可分为 共享锁、排他锁 (行级锁分为共享锁和排他锁两种)

  • 按加锁方式划分，可分为 自动锁、显示锁

  • 按使用方式划分，可分为 乐观锁、悲观锁

    • 在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

DML锁（data locks，数据锁），用于保护数据的完整性，其中包括行级锁(Row Locks (TX锁))、表级锁(table lock(TM锁))。 DDL锁（dictionary locks，数据字典锁），用于保护数据库对象的结构，如表、索引等的结构定义。其中包排他DDL锁（Exclusive DDL lock）、共享DDL锁（Share DDL lock）、可中断解析锁（Breakable parse locks）

悲观锁

在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作都某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。

悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

在数据库中，悲观锁的流程如下：

  1. 在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。
  2. 如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。
  3. 如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。
  4. 其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

MySQL InnoDB 中使用悲观锁

要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。set autocommit=0;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;

优点与不足

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

乐观锁

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。乐观事务控制最早是由孔祥重（H.T.Kung）教授提出。

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

数据版本,为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

可以使用版本号、时间戳实现乐观锁

1
2
3
4
5
6
7

1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

以上SQL其实还是有一定的问题的，就是一旦发上高并发的时候，就只有一个线程可以修改成功，那么就会存在大量的失败。

有一条比较好的建议，可以减小乐观锁力度，最大程度的提升吞吐率，提高并发能力！如下：

1
2

//修改商品库存 
update item set quantity=quantity - 1 where id = 1 and quantity - 1 > 0

以上SQL语句中，如果用户下单数为1，则通过quantity – 1 > 0的方式进行乐观锁控制。

以上update语句，在执行过程中，会在一次原子操作中自己查询一遍quantity的值，并将其扣减掉1。

高并发环境下锁粒度把控是一门重要的学问，选择一个好的锁，在保证数据安全的情况下，可以大大提升吞吐率，进而提升性能。

优点与不足 乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。

  • 乐观锁并未真正加锁，效率高。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。
  • 悲观锁依赖数据库锁，效率低。更新失败的概率比较低。

MySQL 常用存储引擎的锁机制

  • MyISAM和MEMORY采用表级锁(table-level locking)
  • BDB采用页面锁(page-level locking)或表级锁，默认为页面锁
  • InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

InnoDB的细粒度锁，是实现在索引记录上的。

InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

InnoDB 七种锁

如无特殊说明，默认的事务隔离级别为可重复读(Repeated Read, RR)。

共享/排它锁 (Shared and Exclusive Locks)

在InnoDB里当然也实现了标准的行级锁(row-level locking)，共享/排它锁：

共享锁(Share Lock)
共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

用法 SELECT … LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁（eXclusive Lock）
排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用法 SELECT … FOR UPDATE;

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

(1)事务拿到某一行记录的共享S锁，才可以读取这一行； SELECT … LOCK IN SHARE MODE;

(2)事务拿到某一行记录的排它X锁，才可以修改或者删除这一行； SELECT … FOR UPDATE;

其兼容互斥表如下：

      S          X

S 兼容 互斥

X 互斥 互斥

即：

(1)多个事务可以拿到一把S锁，读读可以并行；

(2)而只有一个事务可以拿到X锁，写写/读写必须互斥；

共享/排它锁的潜在问题是，不能充分的并行，解决思路是数据多版本

意向锁 (Intention Locks)

InnoDB支持多粒度锁(multiple granularity locking)，它允许行级锁与表级锁共存，实际应用中，InnoDB使用的是意向锁。

一种表锁(也是一种锁模式)，表明有事务即将给对应表的记录加S或者X锁。SELECT … LOCK IN SHARE MODE会在给记录加S锁之前先给表加IS锁，SELECT … FOR UPDATE会在给记录加X锁之前给表加IX锁。

意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

意向锁有这样一些特点：

(1)首先，意向锁，是一个表级别的锁(table-level locking)；

(2)意向锁分为：

  • 意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁
  • 意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

举个例子：

select … lock in share mode，要设置IS锁；

select … for update，要设置IX锁；

意向锁协议(intention locking protocol)并不复杂：

  • 事务要获得某些行的S锁，必须先获得表的IS锁
  • 事务要获得某些行的X锁，必须先获得表的IX锁

由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，而是可以并行，其兼容互斥表如下：

      IS          IX

IS 兼容 兼容

IX 兼容 兼容

(5)额，既然意向锁之间都相互兼容，那其意义在哪里呢？它会与共享锁/排它锁互斥，其兼容互斥表如下：

      S          X

IS 兼容 互斥

IX 互斥 互斥

排它锁是很强的锁，不与其他类型的锁兼容。这也很好理解，修改和删除某一行的时候，必须获得强锁，禁止这一行上的其他并发，以保障数据的一致性。

记录锁 (Record Locks)

记录锁，它封锁索引记录，例如：

1

select * from t where id=1 for update;

它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。

需要说明的是：

select * from t where id=1;

则是快照读(SnapShot Read)，它并不加锁

间隙锁 (Gap Locks)

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。

如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

临键锁 (Next-Key Locks)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。

如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录。

临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

在RR隔离级别下，为了防止幻读现象，除了给记录本身，还需要为记录两边的间隙加上间隙锁。 比如列a上有一个普通索引，已经有了1、5、10三条记录，select * from t where a=5 for update除了会给5这条记录加行锁，还会给间隙(1,5)和(5,10)加上间隙锁，防止其他事务插入值为5的数据造成幻读。 当a上的普通索引变成唯一索引时，不需要间隙锁，因为值唯一，select * from t where a=5 for update不可能读出两条记录来。

间隙锁相互兼容，因为如果互斥，事务A持有左半段(1,5)，事务B持有右半段(1,10)，那么当前面那个例子中a=5的记录被删除时，理论上左右两个间隙锁得合并成一个新锁(1,10)，那么这个新的大范围锁属于谁呢？所以间隙锁相互兼容，不管是S间隙锁还是X间隙锁

插入意向锁 (Insert Intention Locks)

对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。

多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

自增锁 (Auto-inc Locks)

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。

最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

InnoDB提供了innodb_autoinc_lock_mode配置，可以调节与改变该锁的模式与行为。

思路总结

(1)InnoDB使用共享锁，可以提高读读并发；

(2)为了保证数据强一致，InnoDB使用强互斥锁，保证同一行记录修改与删除的串行性；

(3)InnoDB使用插入意向锁，可以提高插入并发；

总结

(3)记录锁锁定索引记录；

(4)间隙锁锁定间隔，防止间隔中被其他事务插入；

(5)临键锁锁定索引记录+间隔，防止幻读；

1
2

-- 查看死锁情况
show engine innodb status
更新于 2019-02-07
 数据库, MySQL
返回 | 主页