事务与隔离级别-优快云博客

事务是一组访问并可能更新数据库内容的SQL组成的执行单元，是数据库并发控制和事务回滚的基本单位。

一个事务可能包含多个SQL，要么都失败，要么都成功。

事务具备4个基本属性

Atomic，同一个事务里，要么都提交，要么都回滚。

Consistency，即在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏。

Isolation，并发事务间的数据时彼此隔离的。

Durablliy，事务提交后，所有的结果务必被持久化。

支持事务的引擎：InnoDB、NDBCluster、TokuDB。

不支持事务的引擎：MyISAM、MEMORY/HEAP

查看某个引擎是否支持事务：show engines\G;

显示开启事务：

START TRANSACTION

[READ WRITE] -- 默认

[WITH CONSISTENT SNAPSHOT] --发起一个一致性快照读，当前时刻提交的数据，都应该备份出来，从此刻起到之后的数据都不应该看得到。

[READ ONLYO]

或者BEGIN

开启/关闭自动提交

set autocommit | @@autocimmit =0|1

提交事务：

显式提交：commit

隐试提交：

BEGIN

START TRANSACTION

SET AUTOCOMMIT=1 / 其他非事务语句（DDL/DCL）。

回滚事务

显示回滚：ROLLBACK。

隐式回滚：连接端口/超时。

autocommit=0 必要吗？

好处：多语句提交时，不会每个SQL单独提交，提高TPS

麻烦：有个事务忘记提交，锁一直未释放；另一个事务长期锁等待，严重影响TPS。

如果没有事务控制的话，那么并发读写数据库会有什么隐患？

脏读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务更新后达到了满足其查询条件的旧数据（此时它还未被提交），这种现象就称为“脏读”。

不可重复读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务更新后达到了满足其查询条件的旧数据（此时它已被提交），这种现象就称为“不可重复读”。

幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据（此时它已被提交），这种现象就被称为“幻读”。

事务隔离级别

Read Uncommitted（读未提交）隔离级别最低

允许脏读，允许事务查看其它事务所进行的未提交更改，可能读取到其他会话中未提交事务修改的数据。

Read Commited（读已提交）

允许幻读，允许事务查看其它事务所进行的已提交更改，只能读取到已经提交的数据。

Repeatable Read（可重复读）

消除了脏读、不可重复读、幻读，保证事务一致性。

在同一个事务内的查询都是事务开始时刻一致的，确保每个事务的读取结果都是一样的，默认隔离级别。

Serializable（串行）隔离级别最高

串行化读，每次读都需要获得表级共享锁，读写间相互都会阻塞。

相当于MyISAM引擎，不支持并发了。

隔离级别	脏读	不可重复读	幻读
Read Uncommitted（读未提交）	可能	可能	可能
Read Commited（读已提交）	不可能	可能	可能
Repeatable read（可重复读）	不可能	不可能	不可能（InnoDB特定条件下可能）
Serializable（串行）	不可能	不可能	不可能

my.cnf配置

[mysqld]分段中，加入一行

transaction-isolation="READ-COMMITTED" #默认值是REPEATABLE-READ

在线（全局）修改

SET SESSION [GLOBAL] TRANSACTION ISOLATION LEVEL READ COMMITTED [Repeatable READ];

查看事务隔离级别

select @@tx_isolation;

或者

show variables like '%iso%';

查看当前隔离级别

SELECT @@GLOBAL.TX_ISOLATION,@@SESSION.TX_ISOLATION;

例子：

create table t1(

c1 int(11) not null,

c2 int(11) default null,

c3 int(11) default null,

primary key(c1),

key c2(c2)

)

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

| c1 | c2 | c3 |

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

| 0 | 0 | 0 |

| 1 | 1 | 1 |

| 2 | 2 | 2 |

| 3 | 3 | 3 |

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

读未提交，Read uncommitted，RU隔离级别
session1	session2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
begin	begin
select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
	select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
update t1 set c3=30 where c2=2
	select * from t1 where c2=2 \| 2 \| 2 \| 30 \| 读到了t1还未提交的数据

读已提交，Read committed，RC隔离级别
session1	session2
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
begin	begin
select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
	select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
update t1 set c3=30 where c2=2
	select * from t1 where c2=2 \| 2 \| 2 \| 2 \| t1未提交时，还是读取到旧数据
	select * from t1 where c2=2 for update 希望读取到最新版本，被阻塞，需等待
commit
	select * from t1 where c2=2 \| 2 \| 2 \| 30 \| t1提交后，读取到新数据

RC下的幻读
session1	session2
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
begin	begin
select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
	select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
insert into t1select 4,2,2
	select * from t1 where c2=2 \| 2 \| 2 \| 2 \| t1未提交时，还是读取到旧数据
	select * from t1 where c2=2 for update 行锁等待
commit
	select * from t1 where c2=2 \| 2 \| 2 \| 2 \| \| 4 \| 2 \| 2 \| ==>幻读 t1提交后，是否加了for update都可以读取到新数据

可重复读，Repeatable read，RR隔离级别
session1	session2
SET SESSION TRANSACTION ISOLATION LEVEL Repeatable READ
begin	begin
select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
	select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
update t1 set c3=30 where c2=2; commit;
	select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
	select * from t1 where c2=2 for update \| 2 \| 2 \| 30 \|
	select * from t1 where c2=2 \| 2 \| 2 \| 2 \|

RR隔离级别可以避免幻读
session1	session2
SET SESSION TRANSACTION ISOLATION LEVEL Repeatable READ
begin	begin
select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
	select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
insert into t1select 4,2,2 ERROR 1250(HY000):Lock wait timeout exceeded;try restarting transaction 锁住c2=2这个gap，不允许有新的写入

串行，serializable
session1	session2
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin	begin
select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
	select * from t1 where c2=2 \| 2 \| 2 \| 2 \|
update t1 set c3=20 where c2=20 ERROR 1250(HY000):Lock wait timeout exceeded;try restarting transaction