数据库之事务

事务时逻辑上的一组操作，要么全部执行，要么全部不执行

 PPT下载

-- 创建数据表
create table account(
    id int(11) primary key auto_increment,
    name varchar(50) not null,
    money float default 0
);
-- 插入三条记录
insert into account(name,money) values('aaa', 1000), ('bbb', 1000), ('ccc',1000);

比如：A转账100给B

update account set money=money-100 where name='aaa'

update account set money=money+100 where name='bbb'

这2条SQL是不可分割的，少任一条都不符合业务逻辑。

 

数据库默认事务是自动提交的，也就是发一条sql它就执行一条。如果想多条sql放在一个事务中执行，则需要使用如下语句:

• start transaction  开启事务
• rollback  回滚事务
• commit   提交事务

 

使用事务演示银行转账操作：

mysql> start transaction;
mysql> update account set money=money-100 where name='aaa';
mysql> update account set money=money+100 where name='bbb';
mysql> commit;

 

 事务的特性

1. 原子性（Atomicity）

　　指事务是一个不可分割的工作单位，事务中操作要么都发生，要么都不发生

2. 一致性（Consistency）

　　事务前数据的完整性必须保持一致

3. 隔离性（Isolation）

　　指多个用户并发访问数据库时，数据库为每个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离

4. 持久性（Durability）

　　指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

 

隔离级别

多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程获取数据的准确性。不同的隔离级别会造成不同的结果：

脏读

指一个事务读取了另一个事务未提交的数据

比如：A转账100给B，对应sql语句

(1) update account set money=money-100 where name='aaa'

(2) update account set money=money+100 where name='bbb'

当第（1）条sql执行完，第（2）条还没执行时，如果此时B查询自己的账户，就会发现多了100。如果A在B查看过自己账户后，执行回滚，那么B账户就会减少100

不可重复读

指在在一个事务内读取表中的某一行数据，多次读取结果不同。

比如：银行第一次查询了A账户，发现有1000元，就在这时A向自己的账户存了100，银行系统第二次查询，发现这时A账户存款变成了1100。银行在2次查询中结果不一致，就会困惑，不知道哪次查询时准的。（注意：前提是银行的这2次查询在一个事务中）

和脏读的区别是，脏读读取其他事务未提交的数据，不可重复读是重新读取了其他事务已提交的数据。

虚读（幻读）

指在一个事务内读取了其他事务插入的数据，导致前后读取不一致

比如：查询account表发现有3条记录，这时新增一个账户'ddd'，然后再次查询发现account有4条记录，这2次查询在一个事务内，但前后查询结果不一致，这就是虚读（幻读）

 

设置事务隔离级别

数据库共定义了4种隔离级别

• serializable（串行化），可避免脏读、不可重复读、虚读，但性能最低，锁表，相当于单线程，操作过程中其他线程不能操作该表
• repeatable（可重复读），可避免脏读、不可重复读
• read committed（读已提交），可避免脏读
• read uncommitted（读未提交），级别最低，上述3种情况都避免不了

查询当前事务隔离级别 select @@tx_isolation

设置事务隔离级别 set transaction isolation level <事务隔离级别>

 

MySQL 5.5默认使用innodb存储引擎（表类型），innodb是支持事务处理的，设置innodb的事务级别方法是：

set <作用域（global/session）> transaction isolation level <事务隔离级别>

set transaction isolation level read committed;   -- 据我观察，只对当前会话的下一次事务有效
set global transaction isolation level read committed;  -- 全局的
set session transaction isolation level read committed; -- 当前会话

 

实验说明

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | aaa  |  1000 |
|  2 | bbb  |  1000 |
|  3 | ccc  |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

 

第一次实验

事务T1：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set money=money-100 where name='aaa';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

事务T2：read uncommitted隔离级别发生脏读，read committed隔离级别避免了脏读

mysql> set transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | aaa  |   900 | --> 发生脏读，读取了事务T1未提交的数据
|  2 | bbb  |  1000 |
|  3 | ccc  |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | aaa  |  1000 |
|  2 | bbb  |  1000 |
|  3 | ccc  |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

结论：read uncommitted 不能避免脏读，read committed 能。

 

第二次实验

前提：3个事务同时进行，即一个事务不能在另一个事务回滚或提交后开启；在事务T1提交前后，T2和T3分别执行2次查询。

事务T1：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set money=money+100 where name='bbb';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.08 sec)

事务T2：

mysql> set transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;  --- 事务T1提交前
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | aaa  |  1000 |
|  2 | bbb  |  1000 |
|  3 | ccc  |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql> select * from account;  --- 事务T1提交后，结果发生不可重复读
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | aaa  |  1000 |
|  2 | bbb  |  1100 |
|  3 | ccc  |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

事务T3：

mysql> set transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;　　　　--- 事务T1提交前
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | aaa  |  1000 |
|  2 | bbb  |  1000 |
|  3 | ccc  |  1000 |
+----+------+-------+
3 rows in set (0.02 sec)

mysql> select * from account;  --- 事务T1提交后，可重复读
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | aaa  |  1000 |
|  2 | bbb  |  1000 |
|  3 | ccc  |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql> 

结论： 事务T1提交前，T2、T2的查询说明隔离级别read committed和repeatable read可以避免脏读；

　　事务T1提交后，T2、T2的查询说明隔离级别read committed不可重复读，repeatable read 可重复读。

 

第三次实验

前提：其他2个事务不能在事务T1提交后开启。

事务T1

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account(name,money) values('ddd',1000);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.67 sec)

事务T2

mysql> set transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account; -- 事务T1提交前
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | aaa  |  1000 |
|  2 | bbb  |  1100 |
|  3 | ccc  |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql> select * from account; -- 事务T1提交后
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | aaa  |  1000 |
|  2 | bbb  |  1100 |
|  3 | ccc  |  1000 |
+----+------+-------+
3 rows in set (0.00 sec)

事务T3

mysql> set transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account; -- 在操作数据表account后，其他事务只能对account表进行读操作。
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | aaa  |  1000 |
|  2 | bbb  |  1100 |
|  3 | ccc  |  1000 |
3 rows in set (0.00 sec)

mysql> rollback;  -- 为了让事务T1顺利insert，所以事务T3早于T1结束事务
Query OK, 0 rows affected (0.00 sec)

结论：事务T3在操作数据库表时，其他事务只能对该表读操作，避免了虚读；隔离级别repeatable也避免了虚读。

总结：！！！第三次实验得出的结论貌似和前面所说的有些不符啊 —— "repeatable隔离级别也避免了虚读"？？？！！！

（事实如此，可以猜测，但不能歪曲）

在MySQL5.1文档中如是说

REPEATABLE READ

这是InnoDB的默认隔离级别。带唯一搜索条件使用唯一索引的SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE 和DELETE语句只锁定找到的索引记录，而不锁定记录前的间隙。用其它搜索条件，这些操作采用next-key锁定，用next-key锁定或者间隙锁定锁住搜索的索引范围，并且阻止其它用户的新插入。

在持续读中，有一个与之前隔离级别重要的差别：在这个级别，在同一事务内所有持续读读取由第一次读所确定的同一快照。这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句，这些SELECT语句对相互之间也是持续的，请参阅15.2.10.4节，“持续非锁定读”。  

15.2.10.4节，“持续非锁定读” 如是说

15.2.10.4. 持续非锁定读

持续读意味着InnoDB使用它的多版本化来给一个查询展示某个时间点处数据库的快照。查询看到在那个时间点之前被提交的那些确切事务做的更改，并且没有其后的事务或未提交事务做的改变。这个规则的例外是，查询看到发布该查询的事务本身所做的改变。

如果你运行在默认的REPEATABLE READ隔离级别，则在同一事务内的所有持续读读取由该事务中第一个这样的读所确立的快照。你可以通过提交当前事务并在发布新查询的事务之后，为你的查询获得一个更新鲜的快照。

持续读是默认模式，在其中InnoDBzai在READ COMMITTED和REPEATABLE READ隔离级别处理SELECT语句。持续读不在任何它访问的表上设置锁定，因此，其它用户可自由地在持续读在一个表上执行的同一时间修改这些表。

注意，持续读不在DROP TABLE和ALTER TABLE上作用。持续读不在DROP TABLE上作用，因为MySQL不能使用已经被移除的表，并且InnoDB 破坏了该表。持续读不在ALTER TABLE上作用，因为它在某事务内执行，该事务创建一个新表，并且从旧表往新表中插入行。现在，当你重新发出持续读之时，它不能在新表中看见任何行，因为它们被插入到一个在持续读读取的快照中不可见的事务里。