MySQL 的InnoDB事务隔离级别和事务操作语句

2019-01-15 0 By admin

在InnoDB事务模型中,目标是将多版本并发控制(MVCC)数据库的最佳属性与传统的两阶段锁定相结合。
默认情况下,InnoDB在行级别执行锁定并将查询作为非锁定一致读取运行。

一、事务隔离级别

事务隔离是数据库处理的基础之一。隔离级别是在多个事务进行更改并同时执行查询时,对性能和可靠性之间的平衡,结果的一致性和再现性进行微调的设置。

1.1、隔离级别列表

read uncommitted(读未提交) 在一个事务中,可以读到其他事务未提交的修改操纵。产生脏读。
read committed(读提交) 在一个事务中执行中;其他事务提交前和提交后产生的数据修改,
造成多次查询数据不一致的情况。在本事务中产生“不可重复读”。
repeatable read(可重读) 一个事务开始时,建立快照;事务执行时,不受其他会话修改数据影响。
事务执行中,真实数据可能已经被修改了,造成幻读。
serializable(可串行化) 事务串行化的读;本事务读写的数据,会锁定;拒绝其他事务读写。

1.2、不同隔离级别存在的问题

隔离级别 脏读
(Dirty Read)
不可重复读
(NonRepeatable Read)
幻读
(Phantom Read)
未提交读
(Read uncommitted)
可能 可能 可能
已提交读
(Read committed)
不可能 可能 可能
可重复读
(Repeatable read)
不可能 不可能 可能
可串行化
(Serializable)
不可能 不可能 不可能

1.3、名词解释

1、脏读(Dirty Read):一个事务处理过程里读取了另一个未提交的事务中的数据
2、不可重复读(NonRepeatable Read):一个事务范围内多次查询却返回了不同的数据值,这是由于在查询的间隔期间,另外一个事务修改并提交了该数据
3、幻读(Phantom Read):在一个事务内多次查询数据都是一致的,不过真实数据可能已经被其他事务更改。造成无法读取到真实数据(幻读),事务提交时,可能造成数据错误

1.4、设置隔离级别

InnoDB支持使用不同的锁定策略在每个事务隔离级别。
用户可以更改单个会话的隔离级别,也可以更改该SET TRANSACTION语句的所有后续连接。要为所有连接设置服务器的默认隔离级别,请使用 –transaction-isolation命令行或选项文件中的选项。
set [global | session] transaction isolation level ;用来设置事务的隔离级别。
REPEATABLE READ (default)

二、事务操作语句

2.1、自动提交

在中InnoDB存储引擎中,所有的用户活动都发生在一个事务中。
如果autocommit启用了模式,则每个SQL语句都会自行形成一个事务。
默认情况下,MySQL在autocommit 启用时为每个新连接启动会话,所以如果该语句没有返回错误,则MySQL会在每个SQL语句后执行提交。如果语句返回错误,则提交或回滚行为取决于错误。
如果要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。

2.2、事务控制语句

begin或start transaction;显式地开启一个事务;
commit 或 commit work二者是等价的。commit会提交事务,并使已对数据库进行的所有修改称为永久性的;
rollback 或 rollback work二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;

2.3、SavePoint 支持

savepoint identifier;savepoint允许在事务中创建一个保存点,一个事务中可以有多个savepoint;
rollback to identifier;把事务回滚到标记点;
release savepoint identifier;删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;

2.4、MYSQL 事务处理主要有两种方法

1、用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

三、InnoDB存储引擎中的死锁

死锁是由于每个事务持有另一个需要的锁而导致不同事务无法继续的情况。因为两个事务都在等待资源变为可用,所以既不释放它拥有的锁。