为什么要引入数据库锁机制

数据库中多个线程并发执行事务存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性(脏读，不可重复读，幻读等)。

为了解决这个问题，加锁是一个非常重要的技术，对实现数据库并发控制是一个好的方案。简单说，当一个执行sql语句的事务想要操作表记录之前，先向数据库发出请求，对你访问的记录集加锁，在这个事务释放这个锁之前，其他事务不能对这些数据进行更新操作。

锁类型




mysql 多线程select mysql多线程并发读取数据_mysql 多线程select


加锁机制

乐观锁

读多写少的场景。在应用层面实现。

提交更新时检查版本号或者时间戳是否符合。读数据场景会读取对应数据版本，写入操作会升级对应数据版本。执行写操作时，会先读取数据，获取数据原始version1，然后执行写操作，将获取的version1与数据库中数据的当前version做比较，如果版本相同，则执行写操作(update语句数据库系统默认加写锁，此时其它事务无法执行写操作)。否则，重新查询结果，然后重试写操作，直到完成。

悲观锁

写并发高的场景。数据库本身实现。

表锁，行锁等。加锁后，其它事务不能查询也不能更新。比如select... from table for update;

锁粒度

我们使用Mysql一般是使用InnoDB存储引擎的。InnoDB和MyISAM有两个本质的区别：

  • InnoDB支持行锁
  • InnoDB支持事务

不同的存储引擎支持的锁粒度是不一样的：

  • InnoDB行锁和表锁都支持！
  • MyISAM只支持表锁！

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁(Intention Locks)，这两种意向锁都是表锁：

  • 意向共享锁(IS)：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁(IX)：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

行锁

开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。

InnoDB默认使用行锁，实现了两种标准的行锁——共享锁与排他锁；


mysql 多线程select mysql多线程并发读取数据_加锁_02


注意：

1、除了显式加锁的情况，其他情况下的加锁与解锁都无需人工干预。

2、InnoDB所有的行锁算法都是基于索引实现的，锁定的也都是索引或索引区间；

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大，并且可能导致大量的锁冲突，从而影响并发性能。

Innodb行锁实现方式-锁索引，而不是记录

InnoDB存储引擎的行锁是通过锁住索引实现的，而不是记录。这是理解很多数据库锁问题的关键。

由于InnoDB特殊的索引机制，数据库操作使用主键索引时，InnoDB会锁住主键索引；使用非主键索引时，InnoDB会先锁住非主键索引，再锁定主键索引。

如下图所示，当InnoDB锁定非主键索引b时，它也会锁住其对应的主键索引，所以锁住b值为2和3的非主键索引，那么与其相关的a值为6,5的主键索引也需要被锁住。


mysql 多线程select mysql多线程并发读取数据_mysql 多线程select_03


数据库加锁机制

例如一个索引有10,11,13,20这四个值。InnoDB可以根据需要使用Record Lock将10，11，13，20四个索引锁住，也可以使用Gap Lock将(-∞,10)，(10,11)，(11,13)，(13,20)，(20, +∞)五个范围区间锁住。Next-Key Locking类似于上述两种锁的结合，它可以锁住的区间有为(-∞,10]，(10,11]，(11,13]，(13,20]，(20, +∞)，可以看出它即锁定了一个范围，也会锁定记录本身(左开右闭)。


mysql 多线程select mysql多线程并发读取数据_加锁_04


  • InnoDB 自动给修改操作加锁，给查询操作不自动加锁。
  • 当使用唯一索引来搜索唯一行的语句时，不需要间隙锁定。如下面语句的id列有唯一索引，此时只会对id值为10的行使用记录锁。
  • InnoDB通过索引来实现行锁，而不是通过锁住记录。因此，当操作的两条不同记录拥有相同的索引时，也会因为行锁被锁而发生等待。
  • 由于InnoDB的索引机制，数据库操作使用了主键索引，InnoDB会锁住主键索引；使用非主键索引时，InnoDB会先锁住非主键索引，再锁定主键索引。
  • 当查询的索引是唯一索引(不存在两个数据行具有完全相同的键值)时，InnoDB存储引擎会将Next-Key Lock降级为Record Lock，即只锁住索引本身，而不是范围。
  • InnoDB对于辅助索引有特殊的处理，不仅会锁住辅助索引值所在的范围，还会将其下一键值加上Gap Lock。
  • InnoDB使用Next-Key Lock机制来避免Phantom Problem(当前读下的幻读问题)。

等值查询，且走索引

对主键或唯一索引，如果select查询时where条件全部精确命中(=或者in)，这种场景本身就不会出现幻读，所以只会加行记录锁。

范围查询，且走索引

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

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101(这些记录并不存在)的“间隙”加锁。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

没有走索引

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

兼容性

共享锁(读锁)

读锁(read lock)，也叫共享锁(shared lock) 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

事务A对某数据加读锁后，事务B如果对数据发起读操作，同样可以获得读锁，并读取数据。如果事务B对数据发起写操作，则无法获取该数据的排他锁，故无法进行写操作，需要等到所有读锁解除后才能获取写锁，执行写操作。

排他锁(写锁)

写锁(write lock)，也叫排他锁(exclusive lock) 允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁。

死锁

死锁，就是产生了循环等待链条，我等待你的资源，你却等待我的资源，我们都相互等待，谁也不释放自己占有的资源，导致无限等待下去。

原因一：加锁顺序

比如说，一种常见的死锁情况一般出现在如下图所示的操作场景中。


mysql 多线程select mysql多线程并发读取数据_mysql 并发_05


会话一的语句使用了b上的索引，因为它是非主键索引，所以会先在b索引上添加锁，再去a索引上加锁。而会话二的语句恰恰相反，会先在索引a上加锁，再去索引b加锁。这种情况下，就可能出现死锁。

原因二：加锁顺序

比如：

//Session ASTART TRANSACTION;UPDATE account SET p_money=p_money-100 WHERE p_name="tim";UPDATE account SET p_money=p_money+100 WHERE p_name="bill";COMMIT;//Thread BSTART TRANSACTION;UPDATE account SET p_money=p_money+100 WHERE p_name="bill";UPDATE account SET p_money=p_money-100 WHERE p_name="tim";COMMIT;

当线程A执行到第一条语句UPDATE account SET p_money=p_money-100 WHERE p_name=”tim”;锁定了p_name=”tim”的行数据；并且试图获取p_name=”bill”的数据；

，此时，恰好，线程B也执行到第一条语句：UPDATE account SET p_money=p_money+100 WHERE p_name=”bill”;

锁定了 p_name=”bill”的数据，同时试图获取p_name=”tim”的数据；

此时，两个线程就进入了死锁，谁也无法获取自己想要获取的资源，进入无线等待中，直到超时！

如何避免和处理

  • innodb_lock_wait_timeout 等待锁超时回滚事务：

直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。这种方法简单有效，在innodb中，参数innodb_lock_wait_timeout用来设置超时时间。

  • wait-for graph算法来主动进行死锁检测：

innodb还提供了wait-for graph算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发。innodb_deadlock_detect

  • 应用层面加锁建议

这个问题的结论取决于事务 A 在执行完两条 update 语句后，持有哪些锁，以及在什么时候释放。你可以验证一下：实际上事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行。

知道了这个答案，你一定知道了事务 A 持有的两个记录的行锁，都是在 commit 的时候才释放的。

也就是说，在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

知道了这个设定，对我们使用事务有什么帮助呢？那就是，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：

从顾客 A 账户余额中扣除电影票价；

给影院 B 的账户余额增加这张电影票价；

记录一条交易日志。

也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢？

试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

按照如上设定，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

如何上锁

  • 隐式加锁

innodb会给ddl语句隐式加锁。

  1. 显式加锁

select...from table for update;

select...from table lock in mode;

作者：五点半社