让你薪资过万的MySQL进阶教程——锁机制

我们今天接着讲MySQL的进阶教程——锁机制：

首先对MySQL锁进行划分：

1.按照锁的粒度划分：行锁、表锁、页锁

2.按照锁的使用方式划分：共享锁、排它锁（悲观锁的一种实现）

3.还有两种思想上的锁：悲观锁、乐观锁。

4.InnoDB中有几种行级锁类型：Record Lock、Gap Lock、Next-key Lock

一、行级锁定

行级锁定在MySQL各个引擎中，锁定对象颗粒度最小的，所以锁定资源的竞争发生的可能性也是十分小的，因此可以在最大可能下提高数据库服务器处理高并发的能力；行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。有可能会出现死锁的情况。使用行级锁定的引擎主要是innoDB；

MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同 一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB是支持行锁的， 这也是MyISAM被InnoDB替代的重要原因之一。

二、页面锁定

页面锁定是介于行级锁定和标记锁定之间的锁机制，也有可能会发生死锁，在数据库服务器中不是很常见

三、表级锁定

表级锁定是锁定对象颗粒度最大的锁机制，所以其实现逻辑十分简单，会直接锁定整个表，因此，表级锁定的锁定和释放速度快，资源消耗也小，不会发生死锁；但出现锁定资源竞争发生的可能大，导致处理高并发的能力小；主要是一些非事务性的存储引擎，比如MyISAM，MEMORY，CSV等

表级锁定-MyISAM

MyISAM引擎在mysql中表级锁定有两种模式，分别为表共享读锁、表独占写锁；

1.表共享读锁

表共享读锁： 在MyISAM中，当一个表得到一个读锁时，其他进程事务也可以对该表进行读操作，但不能进行写操作；

2.表独占写锁

表独占写锁：在MyISAM中，当一个表得到写锁时，其他进程事务既不能对该表进行写操作，也不能进行读操作，直到该表释放写锁；那么在MyISAM读锁与写锁之间的优先级是怎么样的呢?

在MyISAM引擎中，如果读锁和写锁同时申请同一个表，写锁的优先级会更高，写锁会先于读锁对表进行加锁操作，即使读锁先于写锁申请，它们同时在等待队列中，写锁仍然会先于读锁对表进行加锁操作；

3.MyISAM-表锁的实现

MyISAM在执行查询语句前，会自动给所涉及到的表加读锁，在执行更新操作时，也会自动给所涉及到的表加写锁。

4.MyISAM中表锁的优化

在MyISAM中使用的表级锁定比页面锁定和行级锁定占用的资源都要少，但由于表级锁定的颗粒度比较大，所以导致了其并发性能较为不好，优化的关键为提到表级锁定的并发能力？那么应该怎么提高并发能力呢？在表级锁定的基础上，可以尽量使可以并发执行的进程尽可能的并发执行；

四、悲观锁

在关系数据库管理系统里，悲观并发控制（“悲观锁”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制 。

在mysql/InnoDB中使用悲观锁

首先我们得关闭mysql中的autocommit属性，因为mysql默认使用自动提交模式，也就是说当我们进行一个sql操作的时候，mysql会将这个操作当做一个事务并且自动提交这个操作。

1.手动加上排它锁，但是并没有关闭mysql中的autocommit。

2.正常流程

上面的例子说明了排它锁的原理：一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁或者进行数据的操作。

悲观锁的优点和不足：

悲观锁实际上是采取了“先取锁在访问”的策略，为数据的处理安全提供了保证，但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性；当一个事物所以一行数据的时候，其他事物必须等待该事务提交之后，才能操作这行数据。

总而言之就是一句话：mysql中悲观锁的实现是通过排他锁来实现的

五、乐观锁

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，可以采用版本号机制或者CAS机制实现。

乐观锁的版本号机制

在表中设计一个版本字段 version ，第一次读的时候，会获取 version 字段的取值。然后对数据进行更新或删除操作时，会执行

UPDATE ... SET version=version+1 WHERE version=version

此时如果已经有事务对这条数据进行了更改，修改就不会成功。

乐观锁的时间戳机制

时间戳和版本号机制一样，也是在更新提交的时候，将当前数据的时间戳和更新之前取得的时间戳进行
比较，如果两者一致则更新成功，否则就是版本冲突。

乐观锁的优点和不足：

乐观并发控制相信事务之间的数据竞争的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

六、死锁

为什么会发生死锁

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。（不过现在一般都是InnoDB引擎，关于MyISAM不做考虑）

在InnoDB中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

通过1个SQL死锁的例子来说明

1.两个session的两条语句

session1获得 id=1的锁 session2获得id=5的锁，然后session想要获取id=5的锁 等待，session2想要获取id=1的锁 ，也等待！

避免死锁，这里只介绍常见的三种

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

星辰大海，永不止步

END