MySql事务及事务隔离级别(转)

一、ACID概念

ACID,是指数据库管理系统DBMS)在写入或更新资料的过程中,为保证事务(transaction)是正确可靠的,所必须具备的四个特性:原子性(atomicity,或称不可分割性)、一致性(consistency)、隔离性(isolation,又称独立性)、持久性(durability)。

在数据库系统中,一个事务是指:由一系列数据库操作组成的一个完整的逻辑过程。例如银行转帐,从原账户扣除金额,以及向目标账户添加金额,这两个数据库操作的总和,构成一个完整的逻辑过程,不可拆分。这个过程被称为一个事务,具有ACID特性。

二、四大特性

  • Atomicity(原子性):一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。

    事务在执行过程中发生错误,会被恢复(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。即,事务不可分割、不可约简

    也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位。

  • Consistency(一致性):在事务开始之前和事务结束以后,数据库的完整性没有被破坏。

    这表示写入的资料必须完全符合所有的预设约束触发器级联回滚等。比如A向B转账,不可能A扣了钱,B却没收到。

  • Isolation(隔离性):数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

    事务隔离分为不同级别,包括:

    • 读未提交(Read uncommitted
    • 读提交(read committed
    • 可重复读(repeatable read
    • 串行化(Serializable

    Mysql的默认隔离级别为 repeatable-read

  • Durability(持久性):事务处理结束后,不能再对事务进行回滚,对数据的修改就是永久的,即便系统故障也不会丢失。

三、事务隔离级别

简介

事务隔离(英语:Transaction Isolation)定义了数据库系统中一个操作的结果在何时以何种方式对其他并发操作可见。

# mysql默认的事务隔离级别为repeatable-read
SELECT @@tx_isolation; # 输出结果: REPEATABLE-READ

# 查看全局的隔离级别和当前session的隔离级别
SELECT @@GLOBAL.tx_isolation, @@tx_isolation

# 修改当前session的事务隔离级别
# 语法 SET SESSION TRANSACTION ISOLATION LEVEL <事务级别>;
SET SESSION TRANSACTION ISOLATION LEVEL read committed;

详解

  • **可串行化 **Serializable

    可串行化是最高的隔离级别。

    在基于锁机制并发控制的DBMS实现可串行化,要求在选定对象上的读锁和写锁保持直到事务结束后才能释放。

    SELECT 的查询中使用一个“WHERE”子句来描述一个范围时应该获得一个“范围锁”(range-locks)。

    这种机制可以避免“幻读”(phantom reads)现象(详见下文)。

    当采用不基于锁的并发控制时不用获取锁。但当系统探测到几个并发事务有“写冲突”的时候,只有其中一个是允许提交的。这种机制的详细描述见“快照隔离

  • **可重复读 repeatable read

    在可重复读(REPEATABLE READS)隔离级别中,基于锁机制并发控制的DBMS需要对选定对象的读锁(read locks)和写锁(write locks)一直保持到事务结束,但不要求“范围锁”,因此可能会发生“幻读”

事务1 事务2
步骤1) 开启事务
步骤2) 开启事务
步骤3) select * from 表A where id = 1,得到结果集A
步骤4) update 表A set name = 'dasds' where id = 1
步骤5) 提交事务
步骤6) select * from 表A where id = 1,得到结果集B, 结果集B跟第3步的A是相同的结果,未受步骤4影响
步骤7) 提交事务

总结: 在当前事务未提交前,哪怕其他事务已经修改了当前事务中的部分数据,我们多次运行相同SQL也是相同的结果

  • **提交读 ** read committed

    在提交读(READ COMMITTED)级别中,基于锁机制并发控制的DBMS需要对选定对象的写锁一直保持到事务结束,但是读锁在[SELECT](https://zh.wikipedia.org/wiki/Select_(SQL))操作完成后马上释放(因此“不可重复读”现象可能会发生,见下面描述)。和前一种隔离级别一样,也不要求“范围锁”。

事务1 事务2
步骤1) 开启事务
步骤2) 开启事务
步骤3) select * from 表A where id = 1,得到结果集A
步骤4) update 表A set name = 'dasds' where id = 1
步骤5) 提交事务
步骤6) select * from 表A where id = 1,得到结果集B, 结果集B跟第3步的A是不同相同的结果, 受步骤4影响
步骤7) 提交事务

总结: 在当前事务未提交前,多次运行相同SQL可能是不同结果, 因为当前事务可以读取到其他事务已经提交的数据,虽然解决了幻读的问题,但是可能产生不可重复读的问题

  • 未提交读 Read uncommitted

    未提交读(READ UNCOMMITTED)是最低的隔离级别。允许“脏读”(dirty reads),在事务中可以看到其他事务“尚未提交”的修改。

事务1 事务2
步骤1) 开启事务
步骤2) 开启事务
步骤3) 插入一条数据到表A
步骤4) select * from 表A返回结果包含事务2新插入的数据,注意这时候事务2还未提交
步骤5) 提交事务

读现象解析

ANSI/ISO SQL 92标准涉及三种不同的一个事务读取另外一个事务可能修改的数据的“读现象”。

查询使用如下的数据表。

id name age
1 Joe 20
2 Jill 25
  • 脏读

    当一个事务允许读取另外一个事务修改但未提交的数据时,就可能发生脏读。

    在我们的例子中,事务2修改了一行,但是没有提交,事务1读了这个没有提交的数据。现在如果事务2回滚了刚才的修改或者做了另外的修改的话,事务1中查到的数据就是不正确的了。

事务 1 事务 2
/* Query 1 */ SELECT age FROM users WHERE id = 1; /* will read 20 */
/* Query 2 */ UPDATE users SET age = 21 WHERE id = 1; /* No commit here */
/* Query 1 */ SELECT age FROM users WHERE id = 1; /* will read 21 */
ROLLBACK; /* lock-based DIRTY READ */

在这个例子中,事务2回滚后就没有id是1,age是21的数据行了,但是事务1仍然在使用读到的脏数据进行逻辑处理,称之为脏读。

  • 不可重复读

    理解: 当再次执行相同select sql语句的时候得到的是不同的结果

    在一次事务中,当一行数据获取两遍得到不同的结果表示发生了“不可重复读”.

    在基于锁的并发控制中“不可重复读”现象发生在当执行SELECT 操作时没有获得读锁或者SELECT操作执行完后马上释放了读锁; 多版本并发控制中当没有要求一个提交冲突的事务回滚也会发生“不可重复读”现象。

事务 1 事务 2
/* Query 1 */ SELECT * FROM users WHERE id = 1;
/* Query 2 */ UPDATE users SET age = 21 WHERE id = 1; COMMIT; /* in multiversion concurrency control, or lock-based READ COMMITTED */
/* Query 1 */ SELECT * FROM users WHERE id = 1; COMMIT; /* lock-based REPEATABLE READ */

在这个例子中,事务2提交成功,因此他对id为1的行的修改就对其他事务可见了。但是事务1在此前已经从这行读到了另外一个“age”的值。在可序列化(SERIALIZABLE)和可重复读的【隔离级别】,数据库在第二次SELECT请求的时候应该返回事务2更新之前的值。在提交读和未提交读,返回的是更新之后的值,这个现象就是不可重复读。

  • 幻读

    在事务执行过程中,当两个完全相同的查询语句执行得到不同的结果集。这种现象称为“幻影读(phantom read)”

    当事务没有获取范围锁的情况下执行SELECT … WHERE操作可能会发生“幻影读”。

    “幻影读”是不可重复读的一种特殊场景:当事务1两次执行SELECT … WHERE检索一定范围内数据的操作中间,事务2在这个表中创建了(如INSERT)了一行新数据,这条新数据正好满足事务1的“WHERE”子句。

事务 1 事务 2
/* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30;
/* Query 2 */ INSERT INTO users VALUES ( 3, 'Bob', 27 ); COMMIT;
/* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30;

需要指出的是事务1执行了两遍同样的查询语句。

如果设了最高的隔离级别,两次会得到同样的结果集,这也正是数据库在可序列化(SERIALIZABLE)隔离级别上需要满足的。

但是在较低的隔离级别上,第二次查询可能会得到不同的结果集。

在可序列化隔离级别,查询语句1在age从10到30的记录上加锁,事务2只能阻塞直至事务1提交。

在可重复读级别,这个范围不会被锁定,允许记录插入,因此第二次执行语句1的结果中会包括新插入的行。

隔离级别vs读现象

隔离级别 脏读 不可重复读 幻影读
未提交读 可能发生 可能发生 可能发生
提交读 可能发生 可能发生
可重复读 可能发生
可序列化

隔离级别vs 锁持续时间

在基于锁的并发控制中,隔离级别决定了锁的持有时间。“C”-表示锁会持续到事务提交。 “S” –表示锁持续到当前语句执行完毕。如果锁在语句执行完毕就释放则另外一个事务就可以在这个事务提交前修改锁定的数据,从而造成混乱。

隔离级别 写操作 读操作 范围操作 (…where…)
未提交读 S S S
提交读 C S S
可重复读 C C S
可序列化 C C C

参考

维基百科ACID

MySQL的四种隔离级别

事务隔离

发表评论