煮酒听雨

关于MySQL事务

编辑时间：2017-08-29 编辑：忘尘阅读：614

简介：数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。(点击查看详情)

####示例操作流程：
设想网上购物的一次交易，其付款过程至少包括以下几步数据库操作：
一、更新客户所购商品的库存信息
二、保存客户付款信息--可能包括与银行系统的交互
三、生成订单并且保存到数据库中
四、更新用户相关信息，例如购物数量等等

正常的情况下，这些操作将顺利进行，最终交易成功，与交易相关的所有数据库信息也成功地更新。但是，如果在这一系列过程中任何一个环节出了差错，例如在更新商品库存信息时发生异常、该顾客银行帐户存款不足等，都将导致交易失败。一旦交易失败，数据库中所有信息都必须保持交易前的状态不变，比如最后一步更新用户信息时失败而导致交易失败，那么必须保证这笔失败的交易不影响数据库的状态--库存信息没有被更新、用户也没有付款，订单也没有生成。否则，数据库的信息将会一片混乱而不可预测。数据库事务正是用来保证这种情况下交易的平稳性和可预测性的技术。

####事务的特性:
 1、原子性(Atomicity)： 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
 2、一致性(Consistency)： 事务在完成时，必须使所有的数据都保持一致状态。在一个事务中，事务前后数据的完整性必须保持一致，如：银行转帐、购票
 3、隔离性(Isolation): 多个事务，事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干拢，多个并发事务之间的数据要相互隔离。
 4、持久性(Durability): 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，该修改即使数据库出现致命的系统故障也将一直保持

####事务的并发访问问题
如果我们在不考虑隔离性问题时，事先是存在三种并发访问问题的。
1、脏读：在一个事务中，当读取数据时，读到了另一个事务未提交的数据。
比如A帐户给B帐户转了1块钱，但是A没有提交事务，被B帐户能脏读看到了,这时，B就会以为A已经把钱转过来了，但是这时，A帐户回滚事务。其它钱就没给B转过去了，但是B自己本身以为A已经转过去了

代码示例
```sql
update account set money=money+1 where name=B 
update account set money=money-1 where name=A
```
2、不可重复读：在一个事务中，两次读取的数据内容不一致，这是因为在查询时，有时间间隔，数据被另一个事务已经修改提交了，那就会出现问题。
3、幻读/虚读：在一个事务中，两次读取的数据量不一致。（在一个事务里面的操作中发现了未被操作的数据）

####事务的隔离级别
上面介绍了3种事务并发问题！现在介绍一下数据库提供的解决方案:
1、read uncommitted : 读取尚未提交的数据。这个最低级，但是效率肯定最高，但是哪一个问题都不能解决。
2、read committed : 读取已经提交的数据。可以解决脏读。
3、repeatable read : 重读读取。可以解决脏读 和 不可重复读。
4、serializable : 串行化。可以解决脏读、不可重复读、幻读/虚读，效果最差，相当于锁表，开发中一般不用。

>oracle数据库默认read committed  , mysql数据库默认设置repeatable read

#####查看mysql数据库默认的隔离级别
```sql
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
```
#####设置mysql的隔离级别
set session transaction isolation level  事务的隔离级别
```sql
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
```

####事务的隔离级别演示
注意：
1、如果要自己模拟要开启两个mysql客户端，也就是模拟两个用户
2、测试时两个mysql客户端的隔离级别要一致
3、每次示例后重置表salary

#####示例一：read uncommitted
将数据库的事务隔离级别改为了read uncommitted(读取尚未提交的数据)
```sql
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
```

首先我有一张salary表(工资表)

如图：窗口一
```sql
mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  5000 |
| 2    | chan |  5000 |
+------+------+-------+
2 rows in set (0.00 sec)

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

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

mysql> update salary set money=money-1000 where name='chan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```
窗口二：
```sql
mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  6000 |
| 2    | chan |  4000 |
+------+------+-------+
2 rows in set (0.00 sec)
```
结论：数据库表原始数据money都是5000，当我启动事务后，在wang帐户增加+1000 和 在chan帐户减去-1000，但是(窗口一)的事务还未提交，但是再次在(窗口二）查询数据库存表时，数据已经发生变化，这就是 脏读 和 不可重复读

#####示例二：read committed

将数据库的事务隔离级别改为read committed (读取已经提交的数据)
```sql
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)
```
如图：窗口一
```sql
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update salary set money=money+1000 where name='wang'
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update salary set money=money-1000 where name='chan'
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into salary values (null,'li',5000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  6000 |
| 2    | chan |  4000 |
| NULL | li   |  5000 |
+------+------+-------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)
```
窗口二
```sql
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  5000 |
| 2    | chan |  5000 |
+------+------+-------+
2 rows in set (0.00 sec)

#当窗口一提交事务后查询
mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  6000 |
| 2    | chan |  4000 |
| 3    | li   |  5000 |
+------+------+-------+
3 rows in set (0.00 sec)
```
结论：窗口一数据表中wang帐号 和 chan帐号都发生了变化，但未提交事务，在窗口二中事务开启下查询，没有出现脏读，但是当窗口一提交事务后，在窗口二的事务下再次查询，出次了 不可重复读的情况，这样可以避免脏读，但是查询时出现 不可重复读和幻读/虚读

#####示例3：repeatable read

将数据库事务隔离性改为了repeatable read (重读读取)
```sql
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
```
窗口一：
```sql
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  5000 |
| 2    | chan |  5000 |
+------+------+-------+
2 rows in set (0.00 sec)

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

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

mysql> insert into salary values (3,'li',5000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  6000 |
| 2    | chan |  4000 |
| 3    | li   |  5000 |
+------+------+-------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)
```
窗口二：
```sql
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  5000 |
| 2    | chan |  5000 |
+------+------+-------+
2 rows in set (0.00 sec)

#当窗口一提交事务后查询
mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  5000 |
| 2    | chan |  5000 |
+------+------+-------+
2 rows in set (0.00 sec)
```
结论：两个窗口都开启了事务，当窗口一进行数据操作后，并进行事务提交，在窗口二的事务开启情况下再查询，没有查询到刚才窗口一的数据操作记录，这样避免了脏读和不可重复读。

有人说也避免了 虚读/幻读，其实没有！

继续在窗口二(事务未提交状态下)操作：
```sql
mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  5000 |
| 2    | chan |  5000 |
+------+------+-------+
2 rows in set (0.00 sec)

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

mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  5000 |
| 2    | chan |  3500 |
+------+------+-------+

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

mysql> select * from salary;
+------+------+-------+
| id   | name | money |
+------+------+-------+
| 1    | wang |  5000 |
| 2    | chan |  3500 |
| 3    | li   |  4900 |
+------+------+-------+
3 rows in set (0.00 sec)
```
结论：当操作chan帐号-500时，数据只是发生了变化，但是当修改li帐户时，再去查询出现了li帐号的数据，其实在未修改(updete -100)前，li帐户的数据是查询不出的。这就是幻读/虚读!

#####示例4：serializable

开发不建议用，效率慢，但是上述三种问题都能避免!

####总结：

#####事务隔离级别的性能:

read uncommitted > read committed > repeatable read > serialazable

#####事务隔离级别的安全性：

read uncommitted < read committed < repeatable read < serialazable

#####mysql事务控制：
start transaction   开启事务(begin)

commit     提交

rollback   回滚

来说两句吧