Mysql事务

 

Mysql事务

1、概述 

事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。事务具有四个特征ACID

1. 1. 原子性（Atomicity）
      1. 整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
   2. 一致性（Consistency）
      1. 在事务开始之前与结束之后，数据库都保持一致状态。
   3. 隔离性(Isolation)
      1. 一个事务不会影响其他事务的运行。
   4. 持久性(Durability)
      1. 在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。

事务中存在一些概念：

1. 事务（Transaction）：一批操作（一组DML）
2. 开启事务（Start Transaction）
3. 回滚事务（rollback）
4. 提交事务（commit）
5. SET AUTOCOMMIT：禁用或启用事务的自动提交模式

 

当执行DML语句是其实就是开启一个事务

关于事务的回滚需要注意：只能回滚insert、delete和update语句，不能回滚select（回滚select没有任何意义），对于create、drop、alter这些无法回滚.

事务只对DML有效果。

 

注意：rollback，或者commit后事务就结束了。

16.2、事务的提交与回滚演示 

创建表

 

create table user( id int (11) primary key not null auto_increment ,      username varchar(30), password varchar(30) )  ENGINE=InnoDB DEFAULT CHARSET=utf8

 

1. 查询表中数据
2. 开启事务START TRANSACTION;
3. 插入数据 insert into user (username,password) values ('zhangsan','123')
4. 查看数据  一条记录已经插入到表中
5. 修改数据  表中数据已经被修改
6. 查看数据  之前所做的操作已经“生效”
7. 回滚事务  事务进行回滚，撤销之前的操作
8. 查看数据  回复操作之前的状态

 

3、自动提交模式

• 自动提交模式用于决定新事务如何及何时启动。 
• 启用自动提交模式：
  • 如果自动提交模式被启用，则单条DML语句将缺省地开始一个新的事务。 
  • 如果该语句执行成功，事务将自动提交，并永久地保存该语句的执行结果。 
  • 如果语句执行失败，事务将自动回滚，并取消该语句的结果。 
  • 在自动提交模式下，仍可使用START TRANSACTION语句来显式地启动事务。这时，一个事务仍可包含多条语句，直到这些语句被统一提交或回滚。 
• 禁用自动提交模式： 
  • 如果禁用自动提交，事务可以跨越多条语句。 
  • 在这种情况下，事务可以用COMMIT和ROLLBACK语句来显式地提交或回滚。 
• 自动提交模式可以通过服务器变量AUTOCOMMIT来控制。 
• 例如：

mysql> SET AUTOCOMMIT = OFF； 

mysql> SET AUTOCOMMIT = ON； 

或

mysql> SET SESSION AUTOCOMMIT = OFF； 

mysql> SET SESSION AUTOCOMMIT = ON； 

show variables like '%auto%'; -- 查看变量状态

4、事务的隔离级别

4.1、隔离级别

• 事务的隔离级别决定了事务之间可见的级别。
• 当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：
• 脏读取（Dirty Read） 一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取。
• 不可重复读（Non-repeatable Read） 在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。
• 幻像读（Phantom Read） 幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。

4.2、四个隔离级别

• InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：
• 读未提交（READ UMCOMMITTED） 允许一个事务可以看到其他事务未提交的修改。
• 读已提交（READ COMMITTED） 允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。
• 可重复读（REPEATABLE READ） 确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。 （银行总账）该隔离级别为InnoDB的缺省设置。
• 串行化（SERIALIZABLE） 【序列化】将一个事务与其他事务完全地隔离。 （不常用）

例:A可以开启事物,B也可以开启事物

A在事物中执行DML语句时,未提交

B不以执行DML,DQL语句

4.3、隔离级别与一致性问题的关系

 

4.4、设置服务器缺省隔离级别

通过修改配置文件设置

• 可以在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。 
• 该选项值可以是： 
  • READ-UNCOMMITTED
  • READ-COMMITTED
  • REPEATABLE-READ
  • SERIALIZABLE
• 例如：

[mysqld]

transaction-isolation = READ-COMMITTED

通过命令动态设置隔离级别

• 隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。 
• 其语法模式为：

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>

其中的<isolation-level>可以是：

• • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE
• 例如： 

  SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

   

4.5、隔离级别的作用范围

• 事务隔离级别的作用范围分为两种： 
  • 全局级：对所有的会话有效 
  • 会话级：只对当前的会话有效 
• 例如，设置会话级隔离级别为READ COMMITTED ：

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；

或：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；

• 设置全局级隔离级别为READ COMMITTED ：

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；

4.6、查看隔离级别

• 服务器变量tx_isolation（包括会话级和全局级两个变量）中保存着当前的会话隔离级别。 
• 为了查看当前隔离级别，可访问tx_isolation变量： 
• 查看会话级的当前隔离级别：

mysql> SELECT @@tx_isolation;

或： 

mysql> SELECT @@session.tx_isolation;

• 查看全局级的当前隔离级别： 

mysql> SELECT @@global.tx_isolation;

4.7、并发事务与隔离级别示例

read uncommitted(未提交读) --脏读(Drity Read)：

会话一	会话二
mysql> prompt s1>	mysql> use bjpowernode
s1>use bjpowernode	mysql> prompt s2>
s1>create table tx ( id int(11), num int (10) );
s1>set global transaction isolation level read uncommitted;
s1>start transaction;
	s2>start transaction;
s1>insert into tx values (1,10);
	s2>select * from tx;
s1>rollback;
	s2>select * from tx;

 

read committed(已提交读)

会话一	会话二
s1> set global transaction isolation level read committed;
s1>start transaction;
	s2>start transaction;
s1>insert into tx values (1,10);
s1>select * from tx;
	s2>select * from tx;
s1>commit;
	s2>select * from tx;

repeatable read(可重复读)

会话一	会话二
s1> set global transaction isolation level repeatable read;
s1>start transaction;	s2>start transaction;
s1>select * from tx;
s1>insert into tx values (1,10);
	s2>select * from tx;
s1>commit;
	s2>select * from tx;