2020重新出发，MySql基础，MySql事务

2022-07-13 10:06:35

@

目录

MySQL事务和字符集

当多个用户访问同一数据时，一个用户在更改数据的过程中可能有其它用户同时发起更改请求，为保证数据的一致性状态，MySQL 引入了事务。

MySQL事务的作用

在银行业务中，有一条记账原则，即有借有贷，借贷相等。为了保证这种原则，每发生一笔银行业务，就必须确保会计账目上借方科目和贷方科目至少各记一笔，并且这两笔账要么同时成功，要么同时失败。如果出现只记录了借方科目，或者只记录了贷方科目的情况，就违反了记账原则。会出现记错账的情况。

在银行的日常业务中，只要是同一银行（如都是中国农业银行，简称农行），一般都支持账户间的直接转账。因此，银行转账操作往往会涉及两个或两个以上的账户。在转出账户的存款减少一定金额的同时，转入账户的存款就要增加相应的金额。

下面，在 MySQL 数据库中模拟一下上述提及的转账问题。

假如要从张三的账户直接转账 500 元到李四的账户。首先需要创建账户表，存放用户张三和李四的账户信息。创建账户表和插入数据的 SQL 语句和运行结果如下所示：

mysql> CREATE DATABASE mybank;
Query OK, 1 row affected (0.02 sec)
mysql> USE mybank;
Database changed
mysql> CREATE TABLE bank(
    -> customerName VARCHAR(20),   #用户名
    -> currentMoney DECIMAL(10,2)    #当前余额
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO bank (customerName,currentMoney) VALUES(‘张三‘,1000);;
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO bank (customerName,currentMoney) VALUES(‘李四‘,1);
Query OK, 1 row affected (0.08 sec)

查询 bank 数据表的 SQL 语句和运行结果如下：

mysql> SELECT * FROM bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 张三         |      1000.00 |
| 李四         |         1.00 |
+--------------+--------------+
2 rows in set (0.02 sec)

结果显示，张三和李四两个账户的余额总和为 1000+1=1001 元。

下面开始模拟实现转账功能。从张三的账户直接转账 500 元到李四的账户，可以使用 UPDATE 语句分别修改张三的账户和李四的账户。张三的账户减少 500 元，李四的账户增加 500 元， SQL 语句如下所示：

/*转账测试：张三转账给李四 500 元*/
#张三的账户少 500 元，李四的账户多 500 元
UPDATE bank SET currentMoney = currentMoney-500 WHERE customerName = ‘张三‘;
UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName = ‘李四‘;

正常情况下，执行以上的转账操作后，余额总和应保持不变，仍为 1001 元。但是，如果在这个过程的其中一个环节出现差错，如在张三的账户减少 500 元之后，这时发生了服务器故障，李四的账户没有立即增加 500 元，此时，第三方读取到两个账户的余额总和变为 500+1=501 元，即账户总额间少了 500 元。

MySQL 为了解决此类问题，提供了事务。事务可以将一系列的数据操作捆绑成一个整体进行统一管理，如果某一事务执行成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务执行时遇到错误，则就必须取消或回滚。取消或回滚后，数据将全部恢复到操作前的状态，所有数据的更改均被清除。

MySQL 通过事务保证了数据的一致性。上述提到的转账过程就是一个事务，它需要两条 UPDATE 语句来完成。这两条语句是一个整体，如果其中任何一个环节出现问题，则整个转账业务也应取消，两个账户中的余额应恢复为原来的数据，从而确保转账前和转账后的余额总和不变，即都是 1001 元。

事务的概念和特性

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。

在数据库系统上执行并发操作时，事务是作为最小的控制单元来使用的，特别适用于多用户同时操作的数据库系统。例如，航空公司的订票系统、银行、保险公司以及证券交易系统等。

事务具有 4 个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这 4 个特性通常简称为 ACID。

原子性

事务是一个完整的操作。事务的各元素是不可分的（原子的）。事务中所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。

  • 以银行转账事务为例，如果该事务提交了，则这两个账户的数据将会更新。如果由于某种原因，事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改，事务不能部分提交。

一致性

当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中. 数据可能处于不一致的状态，如数据可能有部分被修改。然而，当事务成功完成时，数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。

  • 以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态。

隔离性

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

另外，当事务修改数据时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交之后，对数据的修改才能生效。张三和李四之间的转账与王五和赵二之间的转账，永远是相互独立的。

持久性

事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。

一个事务成功完成之后，它对数据库所作的改变是永久性的，即使系统出现故障也是如此。也就是说，一旦事务被提交，事务对数据所做的任何变动都会被永久地保留在数据库中。

事务的 ACID 原则保证了一个事务或者成功提交，或者失败回滚，二者必居其一。因此，它对事务的修改具有可恢复性。即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。

MySQL事务的语法和流程

MySQL 提供了多种存储引擎来支持事务。支持事务的存储引擎有 InnoDB 和 BDB，其中，InnoDB 存储引擎事务主要通过 UNDO 日志和 REDO 日志实现，MyISAM 存储引擎不支持事务。

拓展：任何一种数据库，都会拥有各种各样的日志，用来记录数据库的运行情况、日常操作、错误信息等，MySQL 也不例外。

  • 例如，当用户 root 登录到 MySQL 服务器，就会在日志文件里记录该用户的登录时间、执行操作等。

为了维护 MySQL 服务器，经常需要在 MySQL 数据库中进行日志操作：

  • UNDO 日志：复制事务执行前的数据，用于在事务发生异常时回滚数据。
  • REDO 日志：记录在事务执行中，每条对数据进行更新的操作，当事务提交时，该内容将被刷新到磁盘。

默认设置下，每条 SQL 语句就是一个事务，即执行 SQL 语句后自动提交。为了达到将几个操作做为一个整体的目的，需要使用 BEGIN 或 START TRANSACTION 开启一个事务，或者禁止当前会话的自动提交。

执行事务的语法和流程

SQL 使用下列语句来管理事务。

开始事务

BEGIN;

或

START TRANSACTION;

这个语句显式地标记一个事务的起始点。

提交事务

MySQL 使用下面的语句来提交事务：

COMMIT;

COMMIT 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。

提交事务，意味着将事务开始以来所执行的所有数据都修改成为数据库的永久部分，因此也标志着一个事务的结束。一旦执行了该命令，将不能回滚事务。只有在所有修改都准备好提交给数据库时，才执行这一操作。

回滚（撤销）事务

MySQL 使用以下语句回滚事务：

ROLLBACK;

ROLLBACK 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。这里的操作指对数据库的更新操作。

当事务执行过程中遇到错误时，使用 ROLLBACK 语句使事务回滚到起点或指定的保持点处。同时，系统将清除自事务起点或到某个保存点所做的所有的数据修改，并且释放由事务控制的资源。因此，这条语句也标志着事务的结束。

总结

BEGIN 或 START TRANSACTION 语句后面的 SQL 语句对数据库数据的更新操作都将记录在事务日志中，直至遇到 ROLLBACK 语句或 COMMIT 语句。如果事务中某一操作失败且执行了 ROLLBACK 语句，那么在开启事务语句之后所有更新的数据都能回滚到事务开始前的状态。如果事务中的所有操作都全部正确完成，并且使用了 COMMIT 语句向数据库提交更新数据，则此时的数据又处在新的一致状态。

在数据库操作中，为了有效保证并发读取数据的正确性，提出了事务的隔离级别。在 MySQL 中，事务的默认隔离级别是 REPEATABLE-READ （可重读）隔离级别，即事务未结束时（未执行 COMMIT 或 ROLLBACK），其它会话只能读取到未提交数据。

注意事项

MySQL 事务是一项非常消耗资源的功能，大家在使用过程中要注意以下几点。

  1. 事务尽可能简短：事务的开启到结束会在数据库管理系统中保留大量资源，以保证事务的原子性、一致性、隔离性和持久性。如果在多用户系统中，较大的事务将会占用系统的大量资源，使得系统不堪重负，会影响软件的运行性能，甚至导致系统崩溃。
  2. 事务中访问的数据量尽量最少：当并发执行事务处理时，事务操作的数据量越少，事务之间对相同数据的操作就越少。
  3. 查询数据时尽量不要使用事务：对数据进行浏览查询操作并不会更新数据库的数据，因此应尽量不使用事务查询数据，避免占用过量的系统资源。
  4. 在事务处理过程中尽量不要出现等待用户输入的操作：在处理事务的过程中，如需要等待用户输入数据，事务会长时间地占用资源，有可能造成系统阻塞。

MySQL事务自动提交

MySQL 默认开启事务自动提交模式，即除非显式的开启事务（BEGIN 或 START TRANSACTION），否则每条 SOL 语句都会被当做一个单独的事务自动执行。但有些情况下，我们需要关闭事务自动提交来保证数据的一致性。

在 MySQL 中，可以通过 SHOW VARIABLES 语句查看当前事务自动提交模式，如下所示：

mysql> SHOW VARIABLES LIKE ‘autocommit‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.04 sec)

结果显示，autocommit 的值是 ON，表示系统开启自动提交模式。

在 MySQL 中，可以使用 SET autocommit 语句设置事务的自动提交模式，语法格式如下：

SET autocommit = 0|1|ON|OFF;

对取值的说明：

  • 值为 0 和值为 OFF：关闭事务自动提交。如果关闭自动提交，用户将会一直处于某个事务中，只有提交或回滚后才会结束当前事务，重新开始一个新事务。
  • 值为 1 和值为 ON：开启事务自动提交。如果开启自动提交，则每执行一条 SQL 语句，事务都会提交一次。

关闭自动提交后，该位置会作为一个事务起点，直到执行 COMMIT 语句和 ROLLBACK 语句后，该事务才结束。结束之后，这就是下一个事务的起点。

关闭自动提交功能后，只用当执行 COMMIT 命令后，MySQL 才将数据表中的资料提交到数据库中。如果执行 ROLLBACK 命令，数据将会被回滚。如果不提交事务，而终止 MySQL 会话，数据库将会自动执行回滚操作。

使用 BEGIN 或 START TRANSACTION 开启一个事务之后，自动提交将保持禁用状态，直到使用 COMMIT 或 ROLLBACK 结束事务。之后，自动提交模式会恢复到之前的状态，即如果 BEGIN 前 autocommit = 1，则完成本次事务后 autocommit 还是 1。如果 BEGIN 前 autocommit = 0，则完成本次事务后 autocommit 还是 0。

MySQL事务隔离级别详解

MySQL 事务的四大特性，其中事务的隔离性就是指当多个事务同时运行时，各事务之间相互隔离，不可互相干扰。如果事务没有隔离性，就容易出现脏读、不可重复读和幻读等情况。为了保证并发时操作数据的正确性，数据库都会有事务隔离级别的概念。

  1. 脏读：是指一个事务正在访问数据，并且对数据进行了修改，这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
  2. 不可重复读：是指在一个事务内，多次读取同一个数据。在这个事务还没有结束时，另外一个事务也访问了该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
  3. 幻读：是指当事务不是独立执行时发生的一种现象，
    • 例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

为了解决以上这些问题，标准 SQL 定义了 4 类事务隔离级别，用来指定事务中的哪些数据改变是可见的，哪些数据改变是不可见的。

MySQL 包括的事务隔离级别如下：

  • 读未提交（READ UNCOMITTED）
  • 读提交（READ COMMITTED）
  • 可重复读（REPEATABLE READ）
  • 串行化（SERIALIZABLE）

MySQL 事务隔离级别可能产生的问题如下表所示：

隔离级别 脏读 不可重复读 幻读
READ UNCOMITTED 读未提交 √ √ √
READ COMMITTED 读提交 × √ √
REPEATABLE READ 可重复读 × × √
SERIALIZABLE 串行化 × × ×

MySQL 的事务的隔离级别由低到高分别为 READ UNCOMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少。

读未提交（READ UNCOMITTED，RU）

顾名思义，读未提交就是可以读到未提交的内容。

如果一个事务读取到了另一个未提交事务修改过的数据，那么这种隔离级别就称之为读未提交。

在该隔离级别下，所有事务都可以看到其它未提交事务的执行结果。因为它的性能与其他隔离级别相比没有高多少，所以一般情况下，该隔离级别在实际应用中很少使用。

  • 当 MySQL 的事务隔离级别为 READ UNCOMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新但未提交之前， A 窗口中的事务就已经读取到了更新后的数据。但由于 B 窗口中的事务回滚了，所以 A 事务出现了脏读现象。

使用读提交隔离级别可以解决实例中产生的脏读问题。

读提交（READ COMMITTED，RC）

顾名思义，读提交就是只能读到已经提交了的内容。

如果一个事务只能读取到另一个已提交事务修改过的数据，并且其它事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那么这种隔离级别就称之为读提交。

该隔离级别满足了隔离的简单定义：一个事务从开始到提交前所做的任何改变都是不可见的，事务只能读取到已经提交的事务所做的改变。

这是大多数数据库系统的默认事务隔离级别（例如 Oracle、SQL Server），但不是 MySQL 默认的。

  • 当 MySQL 的事务隔离级别为 READ COMMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新并提交后，A 窗口中的事务读取到了更新后的数据。在该过程中，A 窗口中的事务必须要等待 B 窗口中的事务提交后才能读取到更新后的数据，这样就解决了脏读问题。而处于 A 窗口中的事务出现了不同的查询结果，即不可重复读现象。

使用可重复读隔离级别可以解决实例中产生的不可重复读问题。

可重复读（REPEATABLE READ，RR）

顾名思义，可重复读是专门针对不可重复读这种情况而制定的隔离级别，可以有效的避免不可重复读。

在一些场景中，一个事务只能读取到另一个已提交事务修改过的数据，但是第一次读过某条记录后，即使其它事务修改了该记录的值并且提交，之后该事务再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读。

可重复读是 MySQL 的默认事务隔离级别，它能确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。在该隔离级别下，如果有事务正在读取数据，就不允许有其它事务进行修改操作，这样就解决了可重复读问题。

使用串行化隔离级别可以解决实例中产生的幻读问题。

串行化（SERIALIZABLE）

如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。那么这种隔离级别就称之为串行化。

SERIALIZABLE 是最高的事务隔离级别，主要通过强制事务排序来解决幻读问题。简单来说，就是在每个读取的数据行上加上共享锁实现，这样就避免了脏读、不可重复读和幻读等问题。但是该事务隔离级别执行效率低下，且性能开销也最大，所以一般情况下不推荐使用。

MySQL查看和修改事务隔离级别

查看事务隔离级别

在 MySQL 中，可以通过show variables like ‘%tx_isolation%‘或select @@tx_isolation;语句来查看当前事务隔离级别。

查看当前事务隔离级别的 SQL 语句和运行结果如下：

mysql> show variables like ‘%tx_isolation%‘;
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.17 sec）
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

结果显示，目前 MySQL 的事务隔离级别是 REPEATABLE-READ。

另外，还可以使用下列语句分别查询全局和会话的事务隔离级别：

SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;

提示：在MySQL 8.0.3 中，tx_isolation 变量被 transaction_isolation 变量替换了。在 MySQL 8.0.3 版本中查询事务隔离级别，只要把上述查询语句中的 tx_isolation 变量替换成 transaction_isolation 变量即可。

修改事务隔离级别

MySQL 提供了 SET TRANSACTION 语句，该语句可以改变单个会话或全局的事务隔离级别。语法格式如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围：

  • SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
  • GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响；
  • 如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。

任何用户都能改变会话的事务隔离级别，但是只有拥有 SUPER 权限的用户才能改变全局的事务隔离级别。

如果使用普通用户修改全局事务隔离级别，就会提示需要超级权限才能执行此操作的错误信息：

MySQL锁机制

为了保证数据并发访问时的一致性和有效性，任何一个数据库都存在锁机制。锁机制的优劣直接影响到数据库的并发处理能力和系统性能，所以锁机制也就成为了各种数据库的核心技术之一。

锁机制是为了解决数据库的并发控制问题而产生的。如在同一时刻，客户端对同一个表做更新或查询操作，为了保证数据的一致性，必须对并发操作进行控制。同时，锁机制也为实现 MySQL 的各个隔离级别提供了保证。

可以将锁机制理解为使各种资源在被并发访问时变得有序所设计的一种规则。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库显得尤其重要，也更加复杂。

按锁级别分类，可分为共享锁、排他锁和意向锁。也可以按锁粒度分类，可分为行级锁、表级锁和页级锁。

共享锁

共享锁的代号是 S，是 Share 的缩写，也可称为读锁。是一种可以查看但无法修改和删除的数据锁。

共享锁的锁粒度是行或者元组（多个行）。一个事务获取了共享锁之后，可以对锁定范围内的数据执行读操作。会阻止其它事务获得相同数据集的排他锁。

排他锁

排他锁的代号是 X，是 eXclusive 的缩写，也可称为写锁，是基本的锁类型。

排他锁的粒度与共享锁相同，也是行或者元组。一个事务获取了排他锁之后，可以对锁定范围内的数据执行写操作。允许获得排他锁的事务更新数据，阻止其它事务取得相同数据集的共享锁和排他锁。

  • 如有两个事务 A 和 B，如果事务 A 获取了一个元组的共享锁，事务 B 还可以立即获取这个元组的共享锁，但不能立即获取这个元组的排他锁，必须等到事务 A 释放共享锁之后才可以。
  • 如果事务 A 获取了一个元组的排他锁，事务 B 不能立即获取这个元组的共享锁，也不能立即获取这个元组的排他锁，必须等到 A 释放排他锁之后才可以。

意向锁

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁。

意向锁是一种表锁，锁定的粒度是整张表，分为意向共享锁（IS）和意向排他锁（IX）两类。

意向共享锁表示一个事务有意对数据上共享锁或者排他锁。“有意”表示事务想执行操作但还没有真正执行。

锁和锁之间的关系，要么是相容的，要么是互斥的。

  • 锁 a 和锁 b 相容是指：操作同样一组数据时，如果事务 t1 获取了锁 a，另一个事务 t2 还可以获取锁 b；
  • 锁 a 和锁 b 互斥是指：操作同样一组数据时，如果事务 t1 获取了锁 a，另一个事务 t2 在 t1 释放锁 a 之前无法释放锁 b。

锁模式的兼容情况

其*享锁、排他锁、意向共享锁、意向排他锁相互之间的兼容/互斥关系如下表所示，其中 Y 表示相容，N 表示互斥。

参数 X S IX IS
X（排他锁） N N N N
S（共享锁） N Y N Y
IX（意向排他锁） N N Y Y
IS（意向共享锁） N Y Y Y

如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

为了尽可能提高数据库的并发量，需每次锁定的数据范围越小越好，越小的锁其耗费的系统资源越多，系统性能下降。为在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度”的概念。

MySQL表锁、行锁和页锁

MySQL 按锁的粒度可以细分为行级锁、页级锁和表级锁。我们可以将锁粒度理解成锁范围。

表级锁（table lock）

表级锁为表级别的锁定，会锁定整张表，可以很好的避免死锁，是 MySQL 中最大颗粒度的锁定机制。

一个用户在对表进行写操作（插入、删除、更新等）时，需要先获得写锁，这会阻塞其它用户对该表的所有读写操作。没有写锁时，其它读取的用户才能获得读锁，读锁之间是不相互阻塞的。

表级锁最大的特点就是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。当然，锁定颗粒度大带来最大的负面影响就是出现锁定资源争用的概率会很高，致使并发度大打折扣。

不过在某些特定的场景中，表级锁也可以有良好的性能。

  • 例如，READ LOCAL 表级锁支持某些类型的并发写操作。另外，写锁也比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面（写锁可以插入到锁队列中读锁的前面，反之读锁则不能插入到写锁的前面）。

使用表级锁的主要是 MyISAM，MEMORY，CSV 等一些非事务性存储引擎。

尽管存储引擎可以管理自己的锁，MySQL 本身还是会使用各种有效的表级锁来实现不同的目的。

  • 例如，服务器会为诸如 ALTER TABLE 之类的语句使用表级锁，而忽略存储引擎的锁机制。

页级锁（page lock）

页级锁是 MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中并不常见。

页级锁的颗粒度介于行级锁与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力同样也是介于上面二者之间。另外，页级锁和行级锁一样，会发生死锁。

页级锁主要应用于 BDB 存储引擎。

行级锁（row lock）

行级锁的锁定颗粒度在 MySQL 中是最小的，只针对操作的当前行进行加锁，所以行级锁发生锁定资源争用的概率也最小。

行级锁能够给予应用程序尽可能大的并发处理能力，从而提高需要高并发应用系统的整体性能。虽然行级锁在并发处理能力上面有较大的优势，但也因此带来了不少弊端。

由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也就更多，带来的消耗自然也就更大。此外，行级锁也最容易发生死锁。所以说行级锁最大程度地支持并发处理的同时，也带来了最大的锁开销。

行级锁主要应用于 InnoDB 存储引擎。

随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量也越来越多，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也会随之提升。

MySQL 3 种锁的特性

表级锁 行级锁 页级锁
开销 小 大 介于表级锁和行级锁之间
加锁 快 慢 介于表级锁和行级锁之间
死锁 不会出现死锁 会出现死锁 会出现死锁
锁粒度 大 小 介于表级锁和行级锁之间
并发度 低 高 一般

从上述特点可见，很难笼统的说哪种锁更好，只能具体应用具体分析。

从锁的角度来说，表级锁适合以查询为主，只有少量按索引条件更新数据的应用，如 Web 应用。而行级锁更适合于有大量按索引条件，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

MySQL InnoDB行锁

在 MySQL 中，InnoDB 行锁通过给索引上的索引项加锁来实现，如果没有索引，InnoDB 将通过隐藏的聚簇索引来对记录加锁。

InnoDB 支持 3 种行锁定方式：

  • 行锁（Record Lock）：直接对索引项加锁。
  • 间隙锁（Gap Lock）：锁加在索引项之间的间隙，也可以是第一条记录前的“间隙”或最后一条记录后的“间隙”。
  • Next-Key Lock：行锁与间隙锁组合起来用就叫做 Next-Key Lock。 前两种的组合，对记录及其前面的间隙加锁。

默认情况下，InnoDB 工作在可重复读（默认隔离级别）下，并且以 Next-Key Lock 的方式对数据行进行加锁，这样可以有效防止幻读的发生。

Next-Key Lock 是行锁与间隙锁的组合，这样，当 InnoDB 扫描索引项的时候，会首先对选中的索引项加上行锁（Record Lock），再对索引项两边的间隙（向左扫描扫到第一个比给定参数小的值， 向右扫描扫到第一个比给定参数大的值， 然后以此为界，构建一个区间）加上间隙锁（Gap Lock）。如果一个间隙被事务 T1 加了锁，其它事务不能在这个间隙插入记录。

要禁止间隙锁的话，可以把隔离级别降为读已提交（READ COMMITTED），或者开启参数 innodb_locks_unsafe_for_binlog。

注意：以上语句描述的情况，与 MySQL 所设置的事务隔离级别有较大的关系。

开启一个事务时，InnoDB 存储引擎会在更新的记录上加行级锁，此时其它事务不可以更新被锁定的记录。

2020重新出发，MySql基础，MySql事务

上一篇：Oracle中序列（Sequence）详解


下一篇：MySQL基础—分组函数