MySQL事务与锁的知识点总结

这篇文章主要介绍“事务与锁的知识点总结”，在日常操作中，相信很多人在MySQL事务与锁的知识点总结问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL事务与锁的知识点总结”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

MySQL事务与锁

1.         事务与锁

1.1.        事务

事务是一组数据操作执行步骤，这些步骤被视为一个工作单元，用于对多个语句进行分组，可以在多个客户机并发访问同一个表中的数据时使用；所有步骤都成功或都失败，如果所有步骤正常，则执行，如果步骤出现错误或不完整，则取消；事务遵从 ACID。

通过事务，您可以将一个或多个 SQL 语句作为一个工作单元来执行，这样，所有语句或者都成功，或者都失败。在与其他任何事务执行的工作隔离的情况下，可能会出现这种情况。如果所有语句都成功，则可以提交该事务，以便在数据库中永久记录其效果。如果在事务期间出现错误，则可以回滚以取消它。此前已在该事务中执行的任何语句将被撤消，从而使数据库保持开始该事务之前的状态。

注：在 MySQL 中，只有那些使用事务存储引擎（如 InnoDB）的表才支持事务。这些语句不会对非事务存储引擎所管理的表产生任何明显影响。

MySQL事务遵从ACID：

• Atomic（原子性）：所有语句作为一个单元全部成功执行或全部取消。

• Consistent（一致性）：如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。

• Isolated（隔离性）：事务之间不相互影响。

• Durable（持久性）：事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失

1.1.1         事务SQL 控制语句

• START TRANSACTION（或BEGIN）：显式开始一个新事务

• SAVEPOINT：分配事务过程中的一个位置，以供将来引用

• COMMIT：永久记录当前事务所做的更改

• ROLLBACK：取消当前事务所做的更改

• ROLLBACK TO SAVEPOINT：取消在savepoint 之后执行的更改

• RELEASE SAVEPOINT：删除savepoint 标识符

• SET AUTOCOMMIT：为当前连接禁用或启用默认autocommit 模式

1.1.2         AUTOCOMMIT 模式

如何设置 AUTOCOMMIT 模式决定了如何以及何时开始新事务。默认情况下，AUTOCOMMIT 处于全局启用状态，这意味着会强制每个 SQL 语句隐式开始一个新事务。可以通过一个配置文件全局禁用 AUTOCOMMIT，也可以通过设置 autocommit 变量为每个会话禁用它。启用 AUTOCOMMIT 会限制每个语句，并进而影响其自身事务中的事务表。这样可以有效地防止在一个事务中执行多个语句。这意味着，您将无法通过 COMMIT 或 ROLLBACK 作为一个单元提交或回滚多个语句。有时，会将这种情况误认为根本没有事务。但是，情况并非如此。启用 AUTOCOMMIT 后，每个语句仍会以原子方式执行。例如，通过在插入多个行时比较违反约束限制的效果，便可看出启用 AUTOCOMMIT 和根本不具有事务之间的差别。在非事务表（如 MyISAM）中，一旦发生错误，语句就会终止，已经插入的行会保留在该表中。而对于 InnoDB 表，已经插入的所有行都会从该表中删除，从而不会产生任何实际影响。

AUTOCOMMIT确定开始新事务的方式和时间；默认情况下， AUTOCOMMIT 模式处于启用状态：作为一个事务隐式提交每个语句；

在my.cnf中将 AUTOCOMMIT 模式设置为 0，或者SET GLOBAL AUTOCOMMIT=0;SET SESSION AUTOCOMMIT=0; SET @@AUTOCOMMIT :=0; 则禁用 AUTOCOMMIT，事务会跨越多个语句，需要使用 COMMIT 或 ROLLBACK 结束事务；

使用 SELECT 检查 AUTOCOMMIT 设置：

SELECT @@AUTOCOMMIT;

1.1.3         隐式提交

COMMIT 语句始终会显式提交当前事务。其他事务控制语句（例如，本幻灯片列出的语句）还具有隐式提交当前事务的作用。除了这些事务控制语句之外，其他类型的语句可能也具有隐式提交并进而终止）当前事务的作用。这些语句的行为就像在执行实际语句之前发出 COMMIT 一样。此外，这些语句本身并非事务语句，也就是说，如果成功，则无法回滚。通常，数据定义语句、据访问和用户管理语句以及Lock语句具有这种效果。

注：有很多例外情况，而且这些语句并非都能在所有版本的上导致隐式提交。但是，建议将所有非 DML 语句都视为可导致隐式提交。有关导致隐式提交的完整语句列表，请参阅《MySQL 参考手册》：http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

隐式提交会终止当前事务。用于隐式提交的 SQL 语句：

l  START TRANSACTION

l  SET AUTOCOMMIT = 1

导致提交的非事务语句：

l  数据定义语句（ALTER、 CREATE 和 DROP）

l  管理语句（GRANT、 REVOKE 和 SET PASSWORD）

l  Lock语句（LOCK TABLES 和 UNLOCK TABLES）

导致隐式提交的语句示例：

Mysql>TRUNCATE TABLE

Mysql>LOAD DATA INFILE

1.1.4         事务存储引擎

使用 SHOW ENGINES 列出引擎特征：

mysql> SHOW ENGINES\G

********************* 2. row *********************

Engine: InnoDB

Support: DEFAULT

Comment: Supports transactions, row-level locking,

and foreign keys

Transactions: YES

XA: YES

Savepoints: YES

********************* 1. row *********************

Engine: MyISAM

Support: YES

Comment: MyISAM storage engine

Transactions: NO

XA: NO

Savepoints: NO

...

要确保事务存储引擎已编译到 MySQL 服务器中，并且可以在运行时使用，可使用 SHOW ENGINES 语句。Support 列中的值为 YES 或 NO，用于指示该引擎是否可以使用。如果该值为DISABLED则表示该引擎存在，但已关闭。值 DEFAULT 用于指示服务器在默认情况下使用的存储引擎。指定为 DEFAULT 的引擎应视为可用。 Transactions、 XA 和Savepoints 列用于指示该存储引擎是否支持这些功能。

1.2.        事务隔离级别

1.2.1         隔离级别介绍

如果一个客户机的事务更改了数据，其他客户机的事务是应发现这些更改还是应与其隔离，事务隔离级别可以确定同时进行的事务在访问相同数据时彼此交互的方式。使用存储引擎可实现隔离级别。隔离级别选项在不同的数据库服务器之间是不一样的，因此， InnoDB 所实现的级别可能与其他数据库系统所实现的级别并不完全对应。InnoDB 可实现四种隔离级别，用于控制事务所做的更改在多大程度上可由其他同时进行的事务注意到。四种隔离级别如下：

l  READ UNCOMMITTED：允许事务查看其他事务所进行的未提交更改；允许发生“脏” 读、不可重复读和虚读。

l  READ COMMITTED：允许事务查看其他事务所进行的已提交更改；允许发生不可重复读和虚读。未提交的更改仍不可见。

l  REPEATABLE READ：确保每个事务的 SELECT 输出一致，InnoDB 的默认级别；无论其他事务所做的更改是否已提交，两次都会获得相同的结果。换句话说，也就是不同的事务会对相同的数据产生一致的结果。

l  SERIALIZABLE：将一个事务的结果与其他事务完全隔离；与 REPEATABLE READ 类似，但其限制性更强，即一个事务所选的行不能由其他事务更改，直到第一个事务完成为止。

1.2.2         设置隔离级别

系统默认事务级别为：repeatable-read

方法一、 服务器启动时设置级别。

–  在mysqld 命令中使用--transaction-isolation选项。

–  在配置文件中设置transaction-isolation：

[mysqld]

transaction-isolation = <isolation_level>

在配置文件中或在命令行上将<isolation_level> 值设置为：

l  READ-UNCOMMITTED

l  READ-COMMITTED

l  REPEATABLE-READ

l  SERIALIZABLE

方法二、使用SET TRANSACTION ISOLATION LEVEL 语句为正在运行的服务器设置。

– 语法示例：

SET GLOBAL TRANSACTION ISOLATION LEVEL <isolation_level>;

SET SESSION TRANSACTION ISOLATION LEVEL <isolation_level>;

SET TRANSACTION ISOLATION LEVEL <isolation_level>;

对于SET TRANSACTION ISOLATION LEVEL 语句，将<isolation_level> 值设置为：

l  READ UNCOMMITTED

l  READ COMMITTED

l  REPEATABLE READ

l  SERIALIZABLE。

此事务级别可以全局设置，也可以按会话设置。如果没有显式指定，则事务隔离级别将按会话进行设置。例如，以下语句会将当前mysql 会话的隔离级别设置为READ COMITTED：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

该语句相当于：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

要设置所有后续mysql 连接的默认级别，请使用GLOBAL 关键字，而不是SESSION：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

注：设置的全局默认事务隔离级别适用于从设置时起所有新建立的客户机连接。现有连接不受影响。

方法三、SET GLOBAL TX_ISOLATION

         需要 SUPER 权限

Mysql>set global tx_isolation=’ READ-COMMITTED’

         Mysql>select @@tx_isolation;

         Mysql>show variables like ‘tx_isolation’;

transaction_isolation MySQL 5.7.20引入，目的是替换即将弃用的tx_isolation（MySQL 8.0）；

 (root@localhost) [information_schema]> show variables like '%isolat%';

+-----------------------+-----------------+

| Variable_name         | Value           |

+-----------------------+-----------------+

| transaction_isolation | REPEATABLE-READ |

| tx_isolation          | REPEATABLE-READ |

+-----------------------+-----------------+

transaction_isolation was added in MySQL 5.7.20 as an alias for tx_isolation, which is now deprecated and is removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation in preference to tx_isolation.

1.3.        锁概念

MySQL 使用多线程体系结构，多个客户机访问一个表时会出现问题，有必要对客户机进行协调；Lock是一种防止出现并发问题的机制，由服务器管理，Lock供一个客户机访问，限制其他客户机访问，Lock类型：共享锁、互斥锁

Lock机制可以防止因多个客户机同时访问数据而出现的问题。该机制会以某个客户机的身份Lock数据，以限制其他客户机访问该数据，直到释放Lock为止。该Lock允许持有锁的客户机访问数据，而限制与之争用访问权限的其他客户机可以执行的操作。Lock机制的结果是，将对数据的访问序列化，这样，在多个客户机要执行相互冲突的操作时，每个客户机都必须轮流等待。并非所有类型的并发访问都会产生冲突，因此，允许客户机访问数据所需的Lock类型取决于该客户机是希望读取还是希望写入：

• 如果某个客户机希望读取数据，则希望读取相同数据的其他客户机不会产生冲突，它们可以同时进行读取。但是，如果另一个客户机希望写入（修改）数据，则它必须等待，直到读取完成为止。

• 如果某个客户机希望写入数据，则所有其他客户机都必须等待，直到写入完成，而无论这些客户机是想读取还是想写入。

读取器必须阻止写入器，但不能阻止其他读取器。写入器必须同时阻止读取器和写入器。通过读取锁和写入锁，可以强制实施这些限制。利用Lock，可以使客户机进入等待状态，直到能够安全地访问数据为止。借助这种方式，Lock可以禁止并发进行相互冲突的更改并禁止读取正在更改的数据，从而可以防止数据损坏。

1.3.1         显式行锁

InnoDB 支持两种类型的行Lock：

• LOCK IN SHARE MODE：使用共享锁Lock每一行

SELECT * FROM Country WHERE Code='AUS' LOCK IN SHARE MODE\G

• FOR UPDATE：使用互斥锁Lock每一行

SELECT counter_field INTO @@counter_field

FROM child_codes FOR UPDATE;

UPDATE child_codes SET counter_field =

@@counter_field + 1;

InnoDB 支持两种Lock修饰符，这两种修饰符可以添加到 SELECT 语句的末尾：

• LOCK IN SHARE MODE 子句： 共享锁，也就是说，虽然任何其他事务都无法获得互斥锁，但其他事务可以同时使用共享锁。由于正常读取不会Lock任何内容，因此它们不会受Lock的影响。

• FOR UPDATE 子句： 使用互斥锁来Lock选定的每一行，以防止其他对象获得这些行上的任何锁，但允许读取这些行。

在 REPEATABLE READ 隔离级别中，可以将 LOCK IN SHARE MODE 添加到 SELECT操作中，这样，如果其他事务想修改选定行，则它们必须等待当前事务完成。这一点与SERIALIZABLE 隔离级别的工作方式类似，对于该隔离级别， InnoDB 会隐式将 LOCK IN SHARE MODE 添加到 SELECT 语句中，而不会包含任何显式Lock修饰符。如果选择了在未提交的事务中修改的行，则会Lock SELECT，直到该事务提交为止。

1.3.2         死锁

如果多个事务都需要访问数据，而另一个事务已经以互斥方式Lock该数据，则会发生死锁。在两个或更多事务之间发生循环依赖性时。例如， T1 等待由 T2 Lock的资源，而 T2 等待由 T3 Lock的资源，同时 T3 又等待由 T1 Lock的资源。InnoDB 会检测并中止（回滚）其中一个事务，并允许另一个事务完成。

死锁是事务数据库中的一个经典问题，它们并不具有危害性，除非它们经常发生，从而使您根本无法运行某些事务。死锁发生的条件如下：

• 事务获得多个表上的Lock，但顺序相反。

• 诸如 UPDATE 或 SELECT ... FOR UPDATE 等语句Lock了一系列索引记录和间隙，其中，每个事务因计时问题而仅获取了部分Lock。

• 存在多个事务，其中每个事务都在等待另一个事务完成，从而构成一个循环。例如，T1 正在等待 T2， T2 正在等待 T3， T3 正在等待 T1。

如果 InnoDB 对某个事务执行完整回滚，则该事务所设置的所有Lock都会被释放。但是，如果因出现错误而仅回滚了一个 SQL 语句，则该语句所设置的某些Lock可能会保留。发生此问题的原因是， InnoDB 存储行锁的格式使它此后无法识别锁和语句之间的对应关系。如果 SELECT 语句在事务中调用一个存储函数，而该函数中的一个语句出现错误，则该语句将回滚。同时，如果此后执行 ROLLBACK，则整个事务将回滚。

有关 InnoDB 死锁的更多信息，请参阅《MySQL 参考手册》：

http://dev.mysql.com/doc/refman/5.6/en/innodb-deadlock-detection.html。

事务示例：死锁

第一条 DELETE 语句在等待锁时挂起。在执行 UPDATE 语句期间，由于两个会话出现冲突，因此，在会话 2 中检测到死锁。 UPDATE 将被中止，从而允许会话 1 中的 DELETE完成。

1.3.3         隐式锁

MySQL 服务器会根据所发出的命令以及所使用的存储引擎来Lock表（或行）：

* 无锁，除非使用了 SERIALIZABLE 级别、 LOCK IN SHARE MODE 或 FOR UPDATE

InnoDB 表会使用行级别Lock，以使多个会话和应用程序能够同时读取和写入同一个表，而不会相互等待，也不会产生不一致的结果。对于此存储引擎，请避免使用 LOCK TABLES 语句；它不会提供任何额外的保护，却会减少并发性。

利用自动行级别Lock，可以使这些表适用于存储最重要数据的最繁忙数据库，同时还能简化应用逻辑，因为您无需对表进行Lock和解锁。这样， InnoDB 存储引擎就成为MySQL 5.6 中的默认设置