mysql 已提交事务列表 mysql事务提交失败

转载

mob64ca141a2a87 2024-05-29 06:03:38

文章标签 mysql 已提交事务列表 @transaction 提交事务数据隔离级别数据库 文章分类 MySQL 数据库

事务可以用来保证数据库的完整性： 要么都做，要么不做。在 MySQL 中，事务支持是在引擎层实现的。你现在知道，MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。 事务的特性(ACID)

原子性，是指整个数据库的每个事务都是不可分割的单位。只有事务中的所有 SQL 语句都执行成功，才算整个事务成功，事务才会被提交。如果事务中任何一个 SQL 语句执行失败，整个事务都应该被回滚。
一致性，是指将数据库从一种一致性状态转换为下一种一致性状态。不允许数据库中的数据出现新老数据都有的情况，要么都是老数据，要么都是新数据。用更书面化的表达就是：数据的完整性约束没有被破坏。
隔离性，是指一个事务的影响在该事务提交前对其他事务都不可见，它通过锁机制来实现。
持久性，是指事务一旦被提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。

事务的语法

在 MySQL 命令行的默认设置下，事务是自动提交的，即执行了SQL 语句之后会马上执行 commit 操作，我们可以设置 set autocommit=0 来禁用当前回话的自动提交。
还可以用 begin 、start transaction 来显式的开始一个事务。
commit 在默认设置下是等价于 commit work 的，表示提交事务。
rollback 在默认设置下等价于 rollback work，表示事务回滚。
savepoint xxx 表示定义一个保存点，在一个事务中可以有多个保存点。
release savepoint xxx 表示删除一个保存点，当没有该保存点的时候执行该语句，会抛出一个异常。
rollback to [savepoint] xxx 表示回滚到某个保存点。

--查询事务自动提交状态show variables like '%commit%';+-----------------------------------------+-------+| Variable_name                           | Value |+-----------------------------------------+-------+| autocommit                              | ON    || binlog_group_commit_sync_delay          | 0     || binlog_group_commit_sync_no_delay_count | 0     || binlog_order_commits                    | ON    || innodb_api_bk_commit_interval           | 5     || innodb_commit_concurrency               | 0     || innodb_flush_log_at_trx_commit          | 1     || slave_preserve_commit_order             | OFF   |+-----------------------------------------+-------+--全局修改set global autocommit=0;--局部修改set session autocommit=0;--查看修改情况show global variables like 'autocommit';

事务隔离级别类型以及序列化介绍事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。在并发下事务会容易出现一些问题：

脏读：一个事务开始读取了某行数据，另外一个事务已经更新了此数据但没有能够及时提交。这是相当危险的，因为很可能所有的操作都被回滚。
不可重复读：一个事务对同一行数据重复读取两次，但是却得到了不同的结果。例如，在两次读取的中途，有另外一个事务对该型数据进行了修改，并提交。
幻读：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据(MySQL8已解决该问题)。

这是因为在两次查询过程中有另外一个事务插入数据在MySQL中存在(InnoDB)事务存在着4中隔离级别，不同的隔离级别对事务的处理不同。

读未提交( Read Uncommitted)：
READ-UNCOMMITTED | 0：
存在脏读，不可重复读，幻读的问题。
如果一个事务已经开始写数据，则另外一个数据则不会允许同时进行写操作，但允许其他事务读此行数据。
隔离级别可以通过“排他写锁”实现。
读已提交( Read committed)：READ-COMMITTED | 1：解决脏读的问题，存在不可重复读，幻读的问题。这个可以通过“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。
可重复读取(Repeatable Read)：REPEATABLE-READ | 2：解决脏读，不可重复读的问题，存在幻读的问题，默认隔离级别。可通过“共享锁”，“排他锁”实现。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。
序列化(Serializable)：SERIALIZABLE | 3：解决脏读，不可重复读，幻读，可保证事务安全，但完全串行执行，性能最低。提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须要通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

隔离级别	读数据一致性	脏读	不可重复读	幻读
读未提交 Read Uncommitted	最低级别，只能保证不读取物理上损坏的数据	是	是	是
读已提交 Read committed	语句级	否	是	是
可重复读取 Repeatable Read	事务隔离级别	否	否	是
序列化 Serializable	最高级别，事务级	否	否	否

--查看当前隔离级别SHOW VARIABLES LIKE '%transaction_isolation%';+-----------------------+------------------+| Variable_name         |       Value     |+-----------------------+------------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+------------------+-- 设定全局的隔离级别 设定会话 global 替换为 session 即可 把set语法温习一下-- SET [GLOABL] config_name = 'foobar';-- SET @@[session|global].config_name = 'foobar';-- SELECT @@[global.]config_name;--全局修改SET @@gloabl.transaction_isolation = 0;SET @@gloabl.transaction_isolation = 'READ-UNCOMMITTED';SET @@gloabl.transaction_isolation = 1;SET @@gloabl.transaction_isolation = 'READ-COMMITTED';SET @@gloabl.transaction_isolation = 2;SET @@gloabl.transaction_isolation = 'REPEATABLE-READ';SET @@gloabl.transaction_isolation = 3;SET @@gloabl.transaction_isolation = 'SERIALIZABLE';--局部修改SET @@session.transaction_isolation = 0;SET @@session.transaction_isolation = 'READ-UNCOMMITTED';SET @@session.transaction_isolation = 1;SET @@session.transaction_isolation = 'READ-COMMITTED';SET @@session.transaction_isolation = 2;SET @@session.transaction_isolation = 'REPEATABLE-READ';SET @@session.transaction_isolation = 3;SET @@session.transaction_isolation = 'SERIALIZABLE';
--查看当前隔离级别
SHOW VARIABLES LIKE '%transaction_isolation%';
+-----------------------+------------------+
| Variable_name         |       Value     |
+-----------------------+------------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+------------------+

-- 设定全局的隔离级别 设定会话 global 替换为 session 即可 把set语法温习一下
-- SET [GLOABL] config_name = 'foobar';
-- SET @@[session|global].config_name = 'foobar';
-- SELECT @@[global.]config_name;
--全局修改
SET @@gloabl.transaction_isolation = 0;
SET @@gloabl.transaction_isolation = 'READ-UNCOMMITTED';
SET @@gloabl.transaction_isolation = 1;
SET @@gloabl.transaction_isolation = 'READ-COMMITTED';
SET @@gloabl.transaction_isolation = 2;
SET @@gloabl.transaction_isolation = 'REPEATABLE-READ';
SET @@gloabl.transaction_isolation = 3;
SET @@gloabl.transaction_isolation = 'SERIALIZABLE';
--局部修改
SET @@session.transaction_isolation = 0;
SET @@session.transaction_isolation = 'READ-UNCOMMITTED';
SET @@session.transaction_isolation = 1;
SET @@session.transaction_isolation = 'READ-COMMITTED';
SET @@session.transaction_isolation = 2;
SET @@session.transaction_isolation = 'REPEATABLE-READ';
SET @@session.transaction_isolation = 3;
SET @@session.transaction_isolation = 'SERIALIZABLE';

实例分析

假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为。

mysql 已提交事务列表 mysql事务提交失败_mysql 已提交事务列表

若隔离级别是“读未提交”，则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。
若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。

Innodb的一条事务日志共经历4个阶段：