MySQL锁相关问题学习

参考文章链接：MySQL常见的七种锁详细介绍

参考文章链接：MySQL的锁与事务隔离级别详解
个人博客文章链接：MySQL锁相关问题学习

MySQL中有几种锁？

常见的是7种锁，还有一种不常见的预测锁

• 行锁（Record Locks）属于行级锁，悲观锁

• 间隙锁（Gap Locks）属于行级锁，悲观锁

• 临键锁（Next-key Locks）属于行级锁，悲观锁

• (读)共享锁/(写)排他锁（Shared Locks/Exclusive Locks）属于行级锁，悲观锁

• 意向共享锁/意向排他锁（Intention Shared Locks/Intention Exclusive Locks）属于表级锁，悲观锁

• 插入意向锁（Insert Intention Locks）属于特殊的间隙锁，悲观锁

• 自增锁（Auto-inc Locks）属于表级锁

MySQL中如何划分锁？

• 按照对数据操作的锁粒度来分：（锁定粒度依次递增）
  • 1.行级锁
  • 2.间隙锁
  • 3.页级锁
  • 4.表级锁
• 按照锁的共享策略来分：
  • 1.共享锁
  • 2.排他锁
  • 3.意向共享锁
  • 4.意向排他锁
• 从加锁策略上分：
  • 乐观锁
  • 悲观锁
• 其他：
  • 自增锁

按照对数据操作的锁粒度来分

1. 不同存储引擎使用的锁的类型？

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

2. 行级锁Record Lock（偏写）

行级锁介绍

行级锁（记录锁）是MySQL中锁定粒度最细的一种锁。表示单个行记录上的锁，行锁一定是作用在索引上的。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。

行级锁可分为：

• 共享锁
• 排他锁

行锁的种类：

• 行级锁（Record Locks）：单个行记录上的锁。
• 间隙锁（Gap Locks）：间隙锁，锁定一个范围，但不包括记录本身。比如锁定a=5以及其前后2个范围内的数据，也就是将a=3,4,6,7这些行都锁了起来，不包括a=5本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
• 临键锁（Next-key Locks）：锁定一个范围，并且锁定记录本身。比如锁定a=5以及其前后2个范围内的数据，也就是将a=3,4,5,6,7这些行都锁了起来。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

行级锁特点

开销大，加锁慢，会出现死锁。发生锁冲突的概率最低，并发度也最高。

3. 间隙锁Gap Lock

间隙锁介绍

间隙锁，锁定一个范围，但不包括记录本身（它的锁粒度比记录锁的锁整行更大一些，他是锁住了某个范围内的多个行，包括根本不存在的数据），隙锁一定是开区间，比如（3，5）。

GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。该锁只会在隔离级别是RR(可重复读)或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据。

4. 临键锁Next-Key Lock

临键锁介绍

它是记录锁和间隙锁的结合，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。next-key锁是InnoDB默认的锁，临键锁是是一个左开右闭的区间，比如（3，5]。

next-key lock的效果相当于一个记录锁加一个间隙锁。当next-key lock加在某索引上，则该记录和它前面的区间都被锁定。假设有记录1, 3, 5, 7，现在记录5上加next-key lock，则会锁定区间(3, 5]，任何试图插入到这个区间的记录都会阻塞。

record lock、gap lock、next-key lock，都是加在索引上的。假设有记录1,3,5,7，则5上的记录锁会锁住5，5上的gap lock会锁住(3,5)，5上的next-key lock会锁住(3,5]。

注意，next-Key锁规定是左开右闭区间!

以这个图为例name是主键，id是普通索引，插入id=10，它加的next-key其实就是一个左开右闭，id=6本身没有加锁，所以是开区间，id=10本身加锁了，所以是闭区间。即(6，10]

5. 表级锁（偏读）

表级锁介绍

表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分mysql引擎支持。最常使用的MYISAM与InnoDB都支持表级锁定。

表级锁可分为：

• 表共享读锁（共享锁）
• 表独占写锁（排他锁）

表级锁特点

开销小，加锁快，不会出现死锁。发生锁冲突的概率最高，并发度也最低。

• LOCK TABLE my_table_name READ; 用读锁锁表，会阻塞其他事务修改表数据。
• LOCK TABLE my_table_name WRITE; 用写锁锁表，会阻塞其他事务读和写。

不同存储引擎中的表级锁

• 在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的 S锁或者X锁的，如果想加表级锁需要手动显式地声明。
• MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MYISAM表显式加锁。

SQL基本操作

• 建表SQL:

CREATE TABLE `mylock` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`NAME` VARCHAR (20) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;

  

 

  
1
  
2
  
3
  
4
  
5
 

• 插入数据：

INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');

  

 

  
1
  
2
  
3
  
4
 

• 手动增加表锁（读锁/写锁）

lock table 表名称 read/write,表名称2 read/write;

  

 

  
1
 

• 查看表上加过的锁

show open tables;

  

 

  
1
 

• 删除表锁

unlock tables;

  

 

  
1
 

• LOCK TABLES t1 READ：对表t1加表级别的S锁。
• LOCK TABLES t1 WRITE：对表t1加表级别的X锁。

尽量不用这两种方式去加锁，因为InnoDB的优点就是行锁，所以尽量使用行锁，性能更高。

5. 页级锁

页级锁介绍

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。BDB引擎默认 支持页级锁。

页级锁特点

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

按照锁的共享策略来分

共享锁和排他锁在MySQL中具体的实现就是读锁和写锁：

• 读锁（共享锁）：Shared Locks（S锁），针对同一份数据，多个读操作可以同时进行而不会互相影响
• 写锁（排它锁）：Exclusive Locks（X锁），当前写操作没有完成前，它会阻断其他写锁和读锁
• IS锁：意向共享锁Intention Shared Lock。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
• IX锁：意向排他锁Intention Exclusive Lock。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。就是说当对一个行加锁之后，如果有打算给行所在的表加一个表锁，必须先看看该表的行有没有被加锁，否则就会出现冲突。IS锁和IX锁就避免了判断表中行有没有加锁时对每一行的遍历。直接查看表有没有意向锁就可以知道表中有没有行锁。

注意：如果一个表中有多个行锁，他们都会给表加上意向锁，意向锁和意向锁之间是不会冲突的。

1. 共享锁/排他锁

共享锁/排他锁都只是行锁，与间隙锁无关。

• 共享锁是一个事务并发读取某一行记录所需要持有的锁。针对同一份数据，多个读操作可以同时进行而不会互相影响；
• 排他锁是一个事务并发更新或删除某一行记录所需要持有的锁。当前写操作没有完成前，它会阻断其他写锁和读锁；

读锁会阻塞写，但是不会阻塞读。而写锁则会把其他线程的读和写都阻塞。

2. 意向共享锁/意向排他锁

意向共享锁/意向排他锁属于表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

• (IS)意向共享锁 Intention Shared Lock：当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
• (IX)意向排他锁 Intention Exclusive Lock：当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。就是说当对一个行加锁之后，如果有打算给行所在的表加一个表锁，必须先看看该表的行有没有被加锁，否则就会出现冲突。IS锁和IX锁就避免了判断表中行有没有加锁时对每一行的遍历。直接查看表有没有意向锁就可以知道表中有没有行锁。

共享锁/排他锁与意向共享锁/意向排他锁的兼容性关系：

这四种锁都属于悲观锁，如果一个表中有多个行锁，他们都会给表加上意向锁，意向锁之间都不会发生冲突，排他锁跟谁都冲突。

3. 插入意向锁(IIX)

插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。

与间隙锁的另一个非常重要的差别是：尽管插入意向锁也属于间隙锁，但两个事务却不能在同一时间内一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。这里我们再回顾一下共享锁和排他锁：共享锁用于读取操作，而排他锁是用于更新或删除操作。也就是说插入意向锁、共享锁和排他锁涵盖了常用的增删改查四个动作。

从加锁策略上分：乐观锁和悲观锁

1. 悲观锁

悲观锁 认为对于同一个数据的并发操作，一定是会发生修改的（增删改多，查少），哪怕没有修改，也会认为修改。因此对于同一个数据的并发操作，悲观锁采取加锁的形式。悲观的认为，不加锁的并发操作一定会出问题。

悲观锁用的就是数据库的行锁，认为数据库会发生并发冲突，直接上来就把数据锁住，其他事务不能修改，直至提交了当前事务。

2. 乐观锁

乐观锁 则认为对于同一个数据的并发操作，是不会发生修改的（增删改少，查多）。在更新数据的时候，会采用不断尝试更新的方式来修改数据。也就是先不管资源有没有被别的线程占用，直接取申请操作，如果没有产生冲突，那就操作成功，如果产生冲突，有其他线程已经在使用了，那么就不断地轮询。乐观的认为，不加锁的并发操作是没有事情的。就是通过记录一个数据历史记录的多个版本，如果修改完之后发现有冲突再将版本返回到没修改的样子，乐观锁就是不加锁。好处就是减少上下文切换，坏处是浪费CPU时间。

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

乐观锁其实是一种思想，认为不会锁定的情况下去更新数据，如果发现不对劲，才不更新(回滚)。在数据库中往往添加一个version字段（版本号）来实现。乐观锁可以用来避免更新丢失。接下来我们看一下乐观锁在数据表和缓存中的实现。

乐观锁数据表中的实现

利用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。

例子：

-- step1: 查询出商品信息
select (quantity,version) from items where id=100;

-- step2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);

-- step3: 修改商品的库存
update items set quantity=quantity-1,version=version+1 where id=100 and version=#{version};

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
 

既然可以用version，那还可以使用时间戳字段，该方法同样是在表中增加一个时间戳字段，和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

需要注意的是，如果你的数据表是读写分离的表，当master表中写入的数据没有及时同步到slave表中时会造成更新一直失败的问题。此时，需要强制读取master表中的数据（将select语句放在事务中）。即：把select语句放在事务中，查询的就是master主库了！

乐观锁的锁粒度

乐观锁广泛用于状态同步，我们经常会遇到并发对一条物流订单修改状态的场景，所以此时乐观锁就发挥了巨大作用。但是乐观锁字段的选用也需要非常讲究，一个好的乐观锁字段可以缩小锁粒度。

商品库存扣减时，尤其是在秒杀、聚划算这种高并发的场景下，若采用version号作为乐观锁，则每次只有一个事务能更新成功，业务感知上就是大量操作失败。因为version的粒度太大，更新失败的概率也就会变大。

但是如果我们挑选库存字段作为乐观锁（通过比较库存数来判断数据版本），这样我们的锁粒度就会减小，更新失败的概率也会大大减小。

-- 以库存数作为乐观锁
-- step1: 查询出商品信息
select (inventory) from items where id=100;

-- step2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);

-- step3: 修改商品的库存
update items set inventory=inventory-1 where id=100 and inventory-1>0;

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
 

淘宝秒杀、聚划算，跑的就是这条SQL，通过挑选乐观锁，可以减小锁力度，从而提升吞吐。

其他：自增锁AUTO-INC

自增锁（AUTO-INC锁）

• 在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。这样一个事务在持有 AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。
• 采用一个轻量级的锁，在为插入语句生成AUTO_INCREMENT修饰的列的值时获取一下这个轻量级 锁，然后生成本次插入语句需要用到的AUTO_INCREMENT列的值之后，就把该轻量级锁释放掉， 并不需要等到整个插入语句执行完才释放锁。

系统变量innodb_autoinc_lock_mode：

• innodb_autoinc_lock_mode值为0：采用AUTO-INC锁。
• innodb_autoinc_lock_mode值为2：采用轻量级锁。
• 当innodb_autoinc_lock_mode值为1：当插入记录数不确定是采用AUTO-INC锁，当插入记录数确定时采用轻量级锁

自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。防止并发插入数据的时候自增id出现异常。

当一张表的某个字段是自增列时，innodb会在该索引的末位加一个排它锁。为了访问这个自增的数值，需要加一个表级锁，不过这个表级锁的持续时间只有当前sql，而不是整个事务，即当前sql执行完，该表级锁就释放了。其他线程无法在这个表级锁持有时插入任何记录。

文章来源: csp1999.blog.csdn.net，作者：兴趣使然の草帽路飞，版权归原作者所有，如需转载，请联系作者。

原文链接：csp1999.blog.csdn.net/article/details/113835727