MySQL事务与锁的知识点总结

共计 7695 个字符，预计需要花费 20 分钟才能阅读完成。

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

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、
和 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 该数据，则会发生死锁。在两个或更多事务之间发生循环依赖性时。例如，
T2 Lock 的资源，而
T3 Lock 的资源，同时
T1 Lock 的资源。InnoDB
会检测并中止（回滚）其中一个事务，并允许另一个事务完成。

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

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

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

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

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

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

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

事务示例：死锁

会话
1

会话
2

s1 START   TRANSACTION;
  s1 UPDATE Country
  – SET Name = Sakila
  – WHERE Code = SWE

s2 START   TRANSACTION;
  s2 UPDATE Country
  – SET Name = World Cup Winner
  – WHERE Code = ITA

s1 DELETE   FROM Country
  – WHERE Code = ITA

s2 UPDATE   Country
  – SET population=1
  – WHERE Code = SWE
  ERROR 1213 (40001): Deadlock
  found when trying to get lock;
  try restarting transaction

Query OK, 1 row   affected (0.0 sec)

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

1.3.3 
隐式锁

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

操作

InnoDB

MyISAM

SELECT

无锁 *

表级别共享锁

UPDATE/DELETE

行级别互斥锁

表级别互斥锁

ALTER TABLE

表级别共享锁

表级别共享锁

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

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

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

到此，关于“MySQL 事务与锁的知识点总结”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！