带你了解什么是MySQL数据库（八）数据库锁机制

前言：

本章节即将了解到，数据库锁的机制。锁这个概念在很多地方都会出现，如Python、Java等等编程语言内，而锁的目的也很简单，保证数据的安全性，但是也随之降低了效率，我们必须根据情况而定，如果追求安全性的情况下，就不能盲目追求效率，而MySQL作为数据库，存入在里面的必定是很重要的数据，所以了解锁机制是很有必要的。

数据库的锁机制

---

什么是锁？为何要加入锁机制？

锁是计算机协调多个进程或线程并发访问某一资源的机制，那为何要加入锁机制呢？
因为在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。
当并发事务同时访问一个共享的资源时，有可能导致数据不一致、数据无效等问题，
例如我们在数据库的读现象中介绍过，在并发访问情况下，可能会出现脏读、不可重复读和幻读等读现象
为了应对这些问题，主流数据库都提供了锁机制，以及事务隔离级别的概念，
而锁机制可以将并发的数据访问顺序化，以保证数据库中数据的一致性与有效性
此外，锁冲突也是影响数据库并发访问性能的一个重要因素，锁对数据库而言显得尤其重要，也更加复杂。

锁：主要是为了解决并发时候的问题，避免多个事务同时操作导致数据错乱或丢失。

封锁、时间戳、乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

锁的分类

---

锁的分类(oracle):

• 按锁的粒度划分，可分为行级锁、表级锁、页级锁。（mysql支持）

• 按锁级别划分，可分为共享锁、排他锁

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

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

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

MySQL中的行级锁,表级锁,页级锁（粒度）

---

在DBMS(数据库管理系统)中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)、页级锁(BDB引擎 )。

行级锁

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

• 特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 支持引擎：InnoDB
• 行级锁定分为行共享读锁（共享锁）与行独占写锁（排他锁）

共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE

其详细内容，及实现方式我们留到底下说明。

表级锁（偏向于读）

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

• 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
• 支持引擎：MyISAM、MEMORY、InNoDB
• 分类：表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）如下所示

mysql> lock table bank write; # 给bank表加锁，只能当前会话访问以及操作该表，其它会话访问会进行阻塞。
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables where in_use>=1; # 查询当前数据库被锁的表
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| lll      | bank  |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> unlock tables; # 释放当前会话持有的任何锁
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables where in_use>=1;
Empty set (0.00 sec)

页级锁

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

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

行级锁之共享锁与排他锁（级别）

---

行级锁分为：共享锁、排他锁两种

与行处理相关的SQL有：insert、delete、update、select，这四类操作在操作记录行的时候，都可以为其加上锁，但需要知道的是：

1. 对于insert、update、delete、select语句，innodb会自动给涉及的数据加锁，而且是排他锁(X)
2. 对于普通的select语句，innodb不会加上任何锁，需要我们手动加，可以加两种类型的锁。

对上序提到的两种锁进行详细说明：

排他锁（eXclusive Lock）：

排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再对该行加任何类型的其他他锁（共享锁和排他锁），但是获取排他锁的事务是可以对数据就行读取和修改。

增加排他锁的方式：

select * from bank where id = 1 for update; # 给id为1这一条记录增加排他锁，其它使用不能增、删、改这一条记录
select * from bank for update; # 给增张表的所有记录增加排他锁，其它事务都不能操作这个表的数据

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

或者在我们对数据进行insert、delete、update时，也会将对应的记录加上排他锁

特例：加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通select查询没有任何锁机制。

实例演示排他锁，开启两个终端并开启事务

终端1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update bank set money = money + 20 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

终端2

mysql> select * from bank where id = 1;
+----+------+------+-------+
| id | name | age  | money |
+----+------+------+-------+
|  1 | jack |   18 |   800 |
+----+------+------+-------+
1 row in set (0.00 sec)


mysql> update bank set money = money - 20 where id = 1; # 阻塞

终端2进行阻塞状态，因为id = 1这条记录已经被锁住，如果想成功执行这条update命令，只能等待事务1将锁释放掉，也就是rollback或者commit;

排他锁的好处：避免多个事务同时操作同一条记录，这样可能会导致数据错乱或不准确。

补充说明：当没有命中索引时增加排他锁

终端1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update bank set age = 25 where name = 'jams';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

终端2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update bank set age = 22 where name = 'rouse'; # 阻塞状态
.....

注意：因为锁的是索引的键值对应的行记录，而没有命中索引查询的话则默认锁全部行（相当于表级锁）

共享锁(Share Lock)

共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，获准共享锁的事务只能读数据，不能修改数据直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁或不加锁（在其他事务里一定不能再加排他锁，但是在事务T自己里面是可以加的），反之亦然。

增加共享锁的方式：

select * from bank where id = 1 lock in share mode; # 给id = 1字段加上共享锁
select * from bank lock in share mode; # 给所有字段加上共享锁

关于共享锁，笔者自身的一些见解：

如：事务1给id = 1的记录增加了共享锁，事务2给id = 1的记录也增加了共享锁。
那么此时事务1和事务2都不能对这个记录增、删、改的操作，也就是不能增加排他锁。

那如果某个记录只有一个共享锁，那么这个共享锁的持有者就可以对这个记录进行增、删、改。但是对这个记录进行操作后，就会加上排他锁

共享锁的使用场景：

• 多个事务对某记录增加共享锁之后，它们都只能对这些记录进行查询操作。
• 共享锁确保了记录只能查询

演示共享锁的效果：打开两个终端并开启事务

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from bank where id = 1 lock in share mode; # 第一步
+----+------+------+-------+
| id | name | age  | money |
+----+------+------+-------+
|  1 | jack |   18 |   800 |
+----+------+------+-------+
1 row in set (0.00 sec)

mysql> update bank set age = 17 where id = 1; # 第三步：阻塞，因为该记录的共享锁被其它事务使用

终端2

mysql> select * from bank where id = 1 lock in share mode; # 第二步
+----+------+------+-------+
| id | name | age  | money |
+----+------+------+-------+
|  1 | jack |   18 |   800 |
+----+------+------+-------+
1 row in set (0.00 sec)

mysql> update bank set age = 17 where id = 1; # 第四步：阻塞，因为该记录的共享锁被其它事务使用

如果需要记录不能够被任何事务更改，那么就应该给这些记录增加共享锁。

innodb存储引擎的锁机制

---

mysql常用存储引擎的锁机制

MyISAM和MEMORY采用表级锁(table-level locking)

BDB采用页面锁(page-level locking)或表级锁，默认为页面锁

InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁（偏向于写）

InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：
如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

什么是意向锁：

• InnoDB所用的表级锁，其设计目的主要是为了在一个事务中揭示下一步将要被请求的锁的类型。

InnoDB中的两个表锁：

• 意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁

• 意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需要用户干预。

行级锁与表级锁区分

MyISAM 操作数据都是使用表级锁，MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。所以不会产生死锁，但是由于每操作一条记录就要锁定整个表，导致性能较低，并发不高。

InnoDB 与 MyISAM 的最大不同有两点：一是 InnoDB 支持事务；二是 InnoDB 采用了行级锁。也就是你需要修改哪行，就可以只锁定哪行。

在Mysql中，行级锁并不是直接锁记录，而是锁索引。InnoDB 行锁是通过给索引项加锁实现的，而索引分为主键索引和非主键索引两种

1. 如果一条sql 语句操作了主键索引，Mysql 就会锁定这条语句命中的主键索引（或称聚簇索引）；

2. 如果一条语句操作了非主键索引（或称辅助索引），MySQL会先锁定该非主键索引，再锁定相关的主键索引。

3. 如果没有索引，InnoDB 会通过隐藏的聚簇索引来对记录加锁。也就是说：如果不通过索引条件检索数据，那么InnoDB将对表中所有数据加锁，实际效果跟表级锁一样。

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

1. 在不通过索引条件查询的时候,InnoDB 的效果就相当于表锁（上面演示过）

2. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。

3. 由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以即便你的sql语句访问的是不同的记录行,但如果命中的是相同的被锁住的索引，也还是会出现锁冲突的。

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

三种行锁算法

InnoDB有三种行锁的算法，都属于排他锁：

1. Record Lock：单个行记录上的锁。

2. Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

3. Next-Key Lock：等于Record Lock结合Gap Lock，也就说Next-Key Lock既锁定记录本身也锁定一个范围，特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock。

对于行查询，innodb采用的都是Next-Key Lock，主要目的是解决幻读的问题，以满足相关隔离级别以及恢复和复制的需要。

实例演示间隙锁：建立实验表

create table demo(
    id int primary key,
    name varchar(20));

insert demo values(1,'jack1'),(5,'jack5'),(2,'jack2'),(6,'jack6'); 

create index ix_name on demo(name); # 建立索引

事务1

begin;

select * from demo where name='jack5' for update; # 第一步：造成间隙锁，导致这条数据相邻的两条数据jack2 - jack6之间被锁住

此时无法插入name值为：jack2、jack3、jack4、jack5、jack6

事务2

begin;

insert demo values(4,'jack4'); # 第二步：进入阻塞，只能等待事务1释放间隙锁。

注意，此时name是加了索引，不然执行第一步时就会造成表锁，无法完成实验。

死锁问题

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

拿两个事务举例：事务1锁住了事务2想要的记录、事务2锁住了事务1想要的记录，这个就是死锁问题

如何避免死锁问题：

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

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

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

4. 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

什么时候使用表锁

绝大部分情况使用行锁，但在个别特殊事务中,也可以考虑使用表锁

1. 事务需要更新大部分数据，表又较大
   若使用默认的行锁,不仅该事务执行效率低(因为需要对较多行加锁,加锁是需要耗时的); 而且可能造成其他事务长时间锁等待和锁冲突; 这种情况下可以考虑使用表锁来提高该事务的执行速度
2. 事务涉及多个表，较复杂，很可能引起死锁，造成大量事务回滚
   这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ。

悲观锁

当我们要对一个数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。

这种借助数据库锁机制在修改数据之前先锁定，再修改的方式被称之为悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）。

悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制 （也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据），现在互联网高并发的架构中，受到fail-fast思路的影响，悲观锁已经非常少见了。

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

在对任意记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。

如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或者抛出异常。 具体响应方式由开发者根据实际需要决定。

如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。

其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常。

在数据表中的实现

在MySQL中使用悲观锁，必须关闭MySQL的自动提交，set autocommit=0；因为每执行一个SQL语句都是一个事务，而执行完毕之后MySQL会自动帮助我们commit;设置为0之后，MySQL就不会自动帮我们提交事务

set autocommit=0;

在事务内操作表的数据会增加排他锁，而我们手动开启的事务就必须手动commit;

假设商品表中有一个字段quantity表示当前该商品的库存量。假设有一本Book，其id为100，book=8个；如果不使用锁，那么操作方法

//step1: 查出商品剩余量
 select book from items where id=100;
//step2: 如果剩余量大于0，则根据商品信息生成订单
 insert into orders(id,item_id) values(null,100);
 //step3: 修改商品的库存
 update items set book=book-1 where id=100;

那么这种写法就会出现问题：

其实在step1或者step2环节，已经有人下单并且减完库存了，这个时候仍然去执行step3，就造成了超卖。

但是使用悲观锁，就可以解决这个问题，在上面的场景中，商品信息从查询出来到修改，中间有一个生成订单的过程，使用悲观锁的原理就是，当我们在查询出items信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为数据被锁定了，就不会出现有第三者来对其进行修改了。而这样做的前提是需要将要执行的SQL语句放在同一个事物中，否则达不到锁定数据行的目的。

//step1: 查出商品状态
select book from items where id=100 for update;
//step2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);
//step3: 修改商品的库存
update items set book=book-2 where id=100;

select…for update是MySQL提供的实现悲观锁的方式。此时在items表中，id为100的那条数据就被我们锁定了，其它的要执行select book from items where id=100 for update的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

总结

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

优点：

• 悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。

缺点：

• 在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；
• 在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

---

乐观锁

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

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

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

在数据库中，乐观锁的实现有两种方式

1. 使用版本号实现：

   每一行数据多一个字段version，每次更新数据对应版本号+1，
   原理：读出数据，将版本号一同读出，之后更新，提交数据版本号于读出的版本号，则予以更新，否则认为是过期数据，重新读取数据

2. 使用时间戳实现

   每一行数据多一个字段time
   原理：读出数据，将时间戳一同读出，之后更新，提交数据时间戳等于读出的时间戳，则予以更新，否则认为是过期数据，重新读取数据

笔者这里就演示版本号实现乐观锁，创建如下实验表：

CREATE TABLE `user_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `version` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)

insert user_info(null,'tom',18,20);
insert user_info(null,'tom',19,32);
insert user_info(null,'jams',23,56);

因为数据库执行很快，如果是用户修改数据也是顷刻间执行：

事务1：

mysql> select * from user_info;
+----+------+------+---------+
| id | name | age  | version |
+----+------+------+---------+
|  1 | jack |   18 |      20 |
|  2 | tom  |   19 |      32 |
|  3 | jams |   23 |      56 |
+----+------+------+---------+
3 rows in set (0.00 sec)

mysql> update user_info set age = 23,version = version+1 where name='jack' and version=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事务1修改成功了，版本号也对上了，然后立马提交了数据。

事务2：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_info; # 先查询一下用户信息
+----+------+------+---------+
| id | name | age  | version |
+----+------+------+---------+
|  1 | jack |   18 |      20 |
|  2 | tom  |   19 |      32 |
|  3 | jams |   23 |      56 |
+----+------+------+---------+
3 rows in set (0.00 sec)

mysql> update user_info set age=15,version=version+1 where name='jack' and version=20;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

commit;

可以发现，事务2并没有执行成功，也就是说条件判断没有通过，说明版本号不对，被其它事务给改了，这种情况下我们就应该返回一个错误信息给用户。

这种数据处理机制靠得就是我们给出的条件判断，以这样一个操作来达到只有一个用户能够成功修改这条记录。

优点与不足

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

如何选择

在乐观锁与悲观锁的选择上面，主要看下两者的区别以及适用场景就可以了。

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

• 2、悲观锁依赖数据库锁，效率低。更新失败的概率比较低。

随着互联网三高架构（高并发、高性能、高可用）的提出，悲观锁已经越来越少的被使用到生产环境中了，尤其是并发量比较大的业务场景。

如果本文对您有帮助，别忘一键3连，感谢支持！

技术小白记录学习过程，有错误或不解的地方请指出，如果这篇文章对你有所帮助请点赞 收藏+关注 子夜期待您的关注，谢谢支持！