带你了解什么是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>