事务

2020-11-18

START TRANSACTION或 BEGIN开始事务。
COMMIT 提交当前事务，使其更改永久生效。
ROLLBACK 回滚当前事务，取消其更改。
SET autocommit 禁用或启用当前会话的默认自动提交模式。 0 禁用 1 启用

语法

开启事务

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}


WITH CONSISTENT SNAPSHOT:仅适用于innodb;它提供了一个一致的快照仅在当前隔离级别是一个允许连续读取。允许一致读取的唯一隔离级别是REPEATABLE READ。对于所有其他隔离级别,WITH CONSISTENT SNAPSHOT子句被忽略
READ WRITE:允许更改事务中使用的表
READ ONLY:禁止更改事务中使用的表；可防止事务修改或锁定其他事务可见的事务表和非事务表；事务仍然可以修改或锁定临时表；InnoDB当已知事务为只读时， MySQL可以对表查询进行额外的优化；仍然可以对TEMPORARY表使用DML语句更改表。与永久表一样，不允许使用DDL语句进行更改

不允许在同一语句中同时指定READ WRITE 和READ ONLY

提交和回退

COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]


AND CHAIN：使新事务在当前事务结束时立即开始，并且新事务具有与刚刚终止的事务相同的隔离级别，新事务还使用与刚刚终止的事务相同的访问模式（READ WRITE或READ ONLY）
RELEASE：使服务器在终止当前事务之后断开当前客户端会话的连接

设置保存点

在commit之前可以回退到指定保存点

SAVEPOINT identifier  --创建保存点
ROLLBACK [WORK] TO [SAVEPOINT] identifier --回退到指定保存点
RELEASE SAVEPOINT identifier --清除指定保存点

注意

  • 5.7事务不能嵌套
  • DLL（建表、删除表等操作）语句无法回滚

隐式提交

定义或修改数​​据库对象的数据定义语言（DDL）语句、 LOAD DATA语句、复制控制语句会隐式提交事务

事务隔离级别

事务隔离是数据库处理的基础之一。隔离是缩写ACID中的I ; 隔离级别是一种设置，用于在多个事务同时进行更改和执行查询时微调性能与结果的可靠性，一致性和可重复性之间的平衡。

REPEATABLE READ

默认隔离级别。同一事务中的一致读取将读取第一次读取建立的快照。这意味着，如果在同一事务中发出多个普通（非锁定）SELECT语句，则这些SELECT语句彼此之间也是一致的。
对于锁定读取（SELECT……FOR UPDATE或LOCK IN SHARE MODE）， UPDATE和 DELETE语句，锁定取决于该语句使用的是具有唯一搜索条件的唯一索引还是范围类型搜索条件。
对于具有唯一搜索条件的唯一索引， InnoDB仅锁定找到的索引记录，而不锁定其前的间隙。
对于其他搜索条件，InnoDB使用间隙锁定或下一键锁来锁定扫描的索引范围，以阻止其他会话插入该范围所覆盖的间隙

READ COMMITTED

即使在同一事务中，每个一致的读取都将设置并读取自己的新快照。
对于锁定读取（SELECT……FOR UPDATE或LOCK IN SHARE MODE），UPDATE 语句和DELETE 语句，InnoDB仅锁定索引记录，而不锁定它们之间的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁定仅用于外键约束检查和重复键检查。
由于禁用了间隙锁定，因此可能会产生幻读问题，因为其他会话可以在间隙中插入新行
仅支持基于行的二进制日志记录 。如果READ COMMITTED与binlog_format=MIXED配合使用，服务器将自动使用基于行的日志记录
对于UPDATE或 DELETE语句，InnoDB仅对其更新或删除的行持有锁。MySQL评估WHERE条件后，将释放不匹配行的记录锁 。这大大降低了死锁的可能性，但是仍然可以发生。
对于UPDATE语句，如果某行已被锁定，则InnoDB执行“半一致”读取，将最新的提交版本返回给MySQL，以便MySQL可以确定该行是否符合WHERE条件。如果该行匹配（必须更新），则MySQL会再次读取该行，这一次将其锁定或等待对其进行锁定
使用READ COMMITTED隔离级别的效果与启用innodb_locks_unsafe_for_binlog配置选项相同，但以下情况除外：

  • 启用innodb_locks_unsafe_for_binlog是全局设置，会影响所有会话，而隔离级别可以针对所有会话全局设置，也可以针对每个会话单独设置。
  • innodb_locks_unsafe_for_binlog只能在服务器启动时设置，而隔离级别可以在启动时设置或在运行时更改。
  • READ COMMITTED因此提供比innodb_locks_unsafe_for_binlog更好更灵活的控制。

示例

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
在这种情况下，表没有索引，因此搜索和索引扫描使用隐藏的聚集索引进行记录锁定，而不是使用索引列。

假设一个会话使用以下UPDATE语句执行 ：
# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

还假设第二个会话在第一个会话的语句通过之后执行以下UPDATE语句：
# Session B
UPDATE t SET b = 4 WHERE b = 2;
在InnoDB执行UPDATE时，它首先为其读取的每一行获取一个排他锁，然后确定是否对其进行修改。如果InnoDB不修改该行，则释放该锁。否则，InnoDB保留该锁直到事务结束。这会影响事务处理。

使用默认REPEATABLE READ隔离级别

第一个UPDATE将在读取的每一行上获取一个x锁，并且不会释放其中的任何一个
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

第二个UPDATE尝试获取任何块的锁（因为第一个更新在所有行上都保留了锁），并且直到第一个UPDATE提交或回滚时才继续执行：
x-lock(1,2); block and wait for first UPDATE to commit or roll back

使用READ COMMITTED隔离级别

第一个UPDATE将在读取的每一行上获取一个x锁，并为未修改的行释放x锁：
x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

第二个UPDATE，InnoDB执行 “半一致”读取，将读取的每一行的最新提交版本返回给MySQL，以便MySQL可以确定该行是否符合以下WHERE条件：
x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

如果WHERE条件包括索引列并使用索引，则在获取和保留记录锁定时仅考虑索引列。第一个UPDATE在b = 2的每一行上获取并保留一个x锁，当第二次UPDATE在尝试获取同一记录上的x锁时，它阻塞了，因为它也使用了在列b上定义的索引

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

READ UNCOMMITTED

SELECT语句以非锁定方式执行，但可能会使用行的早期版本。因此，使用此隔离级别，此类读取不一致。这也称为脏读。此隔离级别的工作方式类似于READ COMMITTED

SERIALIZABLE

此级别类似于REPEATABLE READ。
如果禁用autocommit，InnoDB将所有普通SELECT语句隐式转换为SELECT ... LOCK IN SHARE MODE。如果启用autocommit，则SELECT是其自身的事务。
它被认为是只读的，并且如果以一致的（非锁定）读取方式执行并且不需要阻塞其他事务就可以序列化。（如果其他事务已修改所选行，则强制普通SELECT阻塞，请禁用autocommit。）

锁定读取

提交或回滚事务时，将释放 所有由LOCK IN SHARE MODE和FOR UPDATE设置的锁
除非在子查询中也指定了锁定读取子句，否则外部语句中的锁定读取子句不会锁定嵌套子查询中表的行

在读取的所有行上设置共享模式锁定。其他会话可以读取行，但是在事务提交之前不能修改它们。如果这些行中的任何一个被尚未提交的另一个事务更改，则查询将等待该事务结束，然后使用最新值。

SELECT ... LOCK IN SHARE MODE

对于索引记录搜索到的行，锁定行和任何关联的索引条目，就像您为这些行发出UPDATE语句一样。其他事务被阻止更新这些行。
执行SELECT ... LOCK IN SHARE MODE，或从某些事务隔离级别读取数据，一致的读取将忽略读取视图中存在的记录上设置的任何锁定。（记录的旧版本无法锁定；可以通过在记录的内存副本上应用撤消日志来重构它们 。）

SELECT ... FOR UPDATE

不同SQL语句设置的锁

一致读取，读取数据库的快照并且不设置锁定，除非将事务隔离级别设置为 SERIALIZABLE。对于SERIALIZABLE级别，搜索会在遇到的索引记录上设置共享的下一键锁定。但是，对于使用唯一索引来搜索唯一行的行来锁定的语句，仅需要索引记录锁定

SELECT ... FROM

为扫描的行获取锁，并预期将不符合资格包含在结果集中的行释放（例如，如果它们不符合该WHERE子句中给出的条件）。但是，在某些情况下，行可能不会立即被解锁，因为结果行与其原始源之间的关系在查询执行期间会丢失。例如，UNION从表中扫描（并锁定）的行可能会在评估它们是否符合结果集之前插入到临时表中。在这种情况下，临时表中的行与原始表中的行之间的关系将丢失，并且直到查询执行结束后，行才被解锁。

SELECT ... LOCK IN SHARE MODE在搜索遇到的所有索引记录上设置共享的下一键锁定。但是，对于使用唯一索引来搜索唯一行的行来锁定的语句，仅需要索引记录锁定。
SELECT ... FOR UPDATE在搜索到的每条记录上设置排他的下一键锁定。但是，对于使用唯一索引来搜索唯一行的行来锁定的语句，仅需要索引记录锁定

SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE

在搜索到的每条记录上设置排他的下一键锁定。但是，对于使用唯一索引来搜索唯一行的行来锁定的语句，仅需要锁索引记录定

UPDATE ... WHERE ...

在插入的行上设置排他锁。该锁是索引记录锁，不是下一个键锁（即没有间隙锁），并且不会阻止其他会话插入到插入行之前的间隙中

INSERT

如果有多个会话试图插入同一行，则使用共享锁可能会导致死锁。如果另一个会话删除该行，页会发生这种情况

示例1

会话1获取该行的排他锁。会话2和3的操作都导致重复键错误，并且都请求该行的共享锁。
会话1回滚时，将释放该行的排他锁，并为排队等待的会话2和3授予共享锁。此时，会话2和3死锁：因为两个会话都持有共享锁，因此两个都无法获得该行的排他锁

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

#session1
START TRANSACTION;
INSERT INTO t1 VALUES(1);

#session2
START TRANSACTION;
INSERT INTO t1 VALUES(1);

#session3
START TRANSACTION;
INSERT INTO t1 VALUES(1);

#session1
ROLLBACK;

示例2

会话1获取该行的排他锁。会话2和3的操作都导致重复键错误，并且都请求该行的共享锁。会话1提交后，将释放该行上的排他锁，并授予排队的会话2和3共享锁。此时，会话2和3死锁：由于两个都持有共享锁，因此两个都无法获得该行的排他锁

#session1
START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

#session2
START TRANSACTION;
INSERT INTO t1 VALUES(1);

#session3
START TRANSACTION;
INSERT INTO t1 VALUES(1);

#session1
COMMIT;

在发生重复键错误时，在要更新的行上设置排他锁而不是共享锁放。对重复的主键值采用独占索引记录锁。对重复的唯一密钥值采取独占的下一个密钥锁

INSERT ... ON DUPLICATE KEY UPDATE

如果唯一键上没有冲突，就像INSERT一样执行REPLACE操作。否则，将在要替换的行上放置一个独占的下一个键锁。

REPLACE

幻读

当同一查询在不同时间产生不同的行集时，在事务内就会发生所谓的幻读问题。例如，如果执行两次SELECT，但是第二次返回的行却不是第一次返回，则该行是“幻象”行。
假设child表的列id上有一个索引，并且您想要读取和锁定该表中所有id值大于100的行，以期稍后更新所选行中的某些列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

该查询从id大于100的第一条记录开始扫描索引。假设表中包含id值为90和102的行。如果在扫描范围内对索引记录设置的锁定不锁定在间隙中插入的内容（在这种情况下，即90和102之间的间隔），另一个会话可以在表中插入一个新行，其行号id为101。如果要在同一事务中执行相同SELECT的操作，则会看到一个新行，其行号id为101（一个 “幻影”）。如果我们将一组行视为数据项，则新的幻影子级将违反事务应能够运行的事务隔离原则，以使已读取的数据在事务期间不会更改。
为了防止产生幻影，请InnoDB使用称为“下一键锁定”的算法，该算法将索引行锁定与间隙锁定结合在一起。 InnoDB执行行级锁定，以使其在搜索或扫描表索引时对遇到的索引记录设置共享或排他锁。因此，行级锁实际上是索引记录锁。另外，索引记录上的下一键锁定也会影响该索引记录之前的 “间隙”。即，下一键锁定是索引记录锁定加上索引记录之前的间隙上的间隙锁定。如果一个会话记录了共享或独占锁在索引中，另一个会话不能在索引顺序之前的间隙中插入新的索引记录

设置事务隔离级别

SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
    ISOLATION LEVEL level
  | access_mode
}

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

access_mode: {
     READ WRITE
   | READ ONLY
}

-- 不允许在同SET TRANSACTION一条语句中指定多个访问模式子句

 

CATALOG
标签
mysql redis sqlserver
{/if}