探秘数据库中的事务

2016-11-13 本文已影响178人廖少少

本文包括：

1、事务概念

2、MySQL管理事务

3、JDBC控制事务进程

4、事务的特性(ACID)

5、事务的隔离级别

6、事务的丢失更新问题(lost update)

1、事务的概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。
例如：A——B转帐，对应于如下两条sql语句
```
 update from account set money=money-100 where name=‘a’; 
 update from account set money=money+100 where name=‘b’;
```
自动提交
- MySQL 数据库默认情况下一条SQL就是一个单独事务，事务是自动提交的
- Oracle 数据库默认情况下事务不是自动提交，所有SQL都将处于一个事务中，你需要手动进行commit提交/rollback回滚

2、MySQL管理事务

在事务管理中执行sql，使用数据库内临时表保存，在没有进行事务提交或者回滚，其它用户无法看到事务操作结果的
SQL语言中只有DML才能被事务管理，如insert update delete
SQL语句：
- start transaction 开启事务（所有对数据表增加、修改、删除操作临时表进行）
- rollback 回滚事务 (取消刚刚操作)
- commit 提交事务（确认刚才操作）
- savepoint my_savepoint 保存点（回滚点，像打游戏时的存档）
- rollback to savepoint my_savepoint 回滚到指定的保存点

3、JDBC控制事务进程

当Jdbc程序向数据库获得一个Connection对象时，默认情况下这个Connection对象会自动向数据库提交在它上面发送的SQL语句。若想关闭这种默认提交方式，让多条SQL在一个事务中执行。

JDBC控制事务语句：

 Connection.setAutoCommit(false); //  相当于SQL语句的start transaction
 
 Connection.rollback();  // 相当于SQL语句的rollback
 
 Connection.commit();  // 相当于SQL语句的commit

 Savepoint sp = conn.setSavepoint(); // 相当于SQL语句的savepoint my_savepoint

 Conn.rollback(sp); // 相当于SQL语句的rollback to savepoint my_savepoint

注意：
- 使用保存点进行事务控制时，回滚到指定保存点之后，必须要调用commit。
- 如果直接使用没有保存点的rollback命令，那么事务中的所有保存点都将被忽略，并且撤销整个事务（这就是普通rollback之后不需要调用commit的原因）

demo：

其中JDBCUtils文件源码在另外一篇文章《Java与数据库的桥梁——JDBC》

 package cn.itcast.test;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.SQLException;
 import java.sql.Savepoint;
 
 import org.junit.Test;
 
 import cn.itcast.utils.JDBCUtils;
 
 public class TransferTest {
     @Test
     public void demo3() {
         // 创建person表，向表中插入20000条数据 ------ 如果插入过程中发生错误，则回滚到插入数据条数为1000的整数倍的时候
         // PreparedStatement 批处理
         Connection conn = null;
         PreparedStatement stmt = null;
         Savepoint savepoint = null;
 
         try {
             conn = JDBCUtils.getConnection();
             // 开启事务
             conn.setAutoCommit(false);
             // 保存一次
             savepoint = conn.setSavepoint();
 
             String sql = "insert into person values(?,?)";
             // 预编译SQL
             stmt = conn.prepareStatement(sql);
 
             for (int i = 1; i <= 20000; i++) {
                 stmt.setInt(1, i);
                 stmt.setString(2, "name" + i);
 
                 // 添加到批处理
                 stmt.addBatch();
 
                 if (i == 4699) {
                     int d = 1 / 0;
                 }
 
                 // 每隔200向数据库发送一次
                 if (i % 200 == 0) {
                     stmt.executeBatch();
                     stmt.clearBatch();
                 }
 
                 if (i % 1000 == 0) {// 1000整数倍
                     // 保存 回滚点
                     savepoint = conn.setSavepoint();
                 }
             }
 
             stmt.executeBatch();// 为了确保缓存sql都提交了
 
             // 没有错误
             conn.commit();
 
         } catch (Exception e) {
             // 回滚事务，回滚存储点
             try {
                 conn.rollback(savepoint);
                 conn.commit();
             } catch (SQLException e1) {
                 e1.printStackTrace();
             }
             e.printStackTrace();
         } finally {
             JDBCUtils.release(stmt, conn);
         }
     }
 
     @Test
     public void demo2() {
         // 模拟转账操作，使用事务管理
         Connection conn = null;
         PreparedStatement stmt = null;
 
         try {
             conn = JDBCUtils.getConnection();
             // 1、在连接获得后，开启事务 start transaction
             conn.setAutoCommit(false);// 关闭自动提交
 
             // 没有用事务管理，两句sql 就是两个事务
             String sql1 = "update account set money = money - 100 where name='aaa'";
             String sql2 = "update account set money = money +100 where name ='bbb'";
 
             stmt = conn.prepareStatement(sql1);
             stmt.executeUpdate();// 执行 update操作
 
             // int d = 1 / 0;
 
             stmt = conn.prepareStatement(sql2);
             stmt.executeUpdate();// 执行 update操作
 
             // 2、两句SQL 都执行成功，事务commit
             System.out.println("事务提交！");
             conn.commit();
 
         } catch (Exception e) {
             // 3、在执行转账过程中 发生错误，将两句sql 进行回滚
             System.out.println("事务回滚！");
             try {
                 conn.rollback();
             } catch (SQLException e1) {
                 e1.printStackTrace();
             }
 
             e.printStackTrace();
         } finally {
             JDBCUtils.release(stmt, conn);
         }
     }
 
     @Test
     public void demo1() {
         // 模拟转账操作，先不使用事务管理
         Connection conn = null;
         PreparedStatement stmt = null;
 
         try {
             conn = JDBCUtils.getConnection();
             // 没有用事务管理，两句sql 就是两个事务
             String sql1 = "update account set money = money - 100 where name='aaa'";
             String sql2 = "update account set money = money +100 where name ='bbb'";
 
             stmt = conn.prepareStatement(sql1);
             stmt.executeUpdate();// 执行 update操作
 
             int d = 1 / 0;
 
             stmt = conn.prepareStatement(sql2);
             stmt.executeUpdate();// 执行 update操作
 
         } catch (Exception e) {
             e.printStackTrace();
         } finally {
             JDBCUtils.release(stmt, conn);
         }
     }
 }

4、事务的特性(ACID)

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）事务前后数据的完整性必须保持一致。
隔离性（Isolation）事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

企业开发中一定要保证事务原子性，事务最复杂问题都是由事务隔离性引起的

5、事务的隔离级别

不考虑事务隔离将引发哪些问题：脏读、不可重复读、虚读
- 脏读：一个事务读取另一个事务未提交数据（数据库隔离中最重要的问题）
  
  这是非常危险的，假设A要在B的网店购物，Ａ向Ｂ转帐１００元，对应sql语句如下所示
```
   1.update account set money=money+100 while name=‘b’;    
   2.update account set money=money-100 while name=‘a’;
```
  当第1条sql执行完，第2条还没执行(A未提交时)，如果此时Ｂ查询自己的帐户，就会发现自己多了100元钱，立即发货，如果A等B发货后再回滚，B就会损失货物。
- 不可重复读：一个事务读取另一个事务未提交和已提交数据，两次读取结果不同(有时并不一定是坏问题）。
  1. 例如银行想查询A帐户余额，第一次查询A帐户为200元，此时A向帐户存了100元并提交了，银行接着又进行了一次查询，此时A帐户为300元了。银行两次查询不一致，可能就会很困惑，不知道哪次查询是准的。
  1. 不可重复读与脏读的区别是：脏读是读取前一事务未提交的脏数据，不可重复读是重新读取了前一事务已提交的数据。
- 虚读：一个事务读取另一个事务插入数据，造成在一个事务中两次读取记录条数不同（比不可重复读危害性更低，很多数据库不认为虚读是问题）。
  1. 如丙存款100元未提交，这时银行做报表统计account表中所有用户的总额为500元，然后丙提交了，这时银行再统计发现帐户为600元了，造成虚读同样会使银行不知所措，到底以哪个为准。
  1. 虚读与不可重复读的区别：不可重复读读取update的数据，虚读读取insert的数据。
数据库共定义了四种隔离级别：
- Serializable ：可解决脏读、不可重复读、虚读情况的发生。串行处理 ---解决三类问题
- Repeatable read ：可以解决不可重复读、脏读，会发生虚读 ------MySQL 默认级别
- read committed : 可以解决脏读，会发生不可重复读、虚读（读已提交） -------Oracle默认级别
- read uncommitted : 最低级别，会导致三类问题发生 (读未提交)
Serializable > Repeatable read > read committed > read uncommitted

数据库隔离问题危害：脏读> 不可重复读 > 虚读，

安全级别越高，处理效率越低；安全级别越低，效率越高。所以一般数据库把默认级别定位中间的两级。
有关SQL语句：
- set transaction isolation level XXX; 设置事务隔离级别
- select @@tx_isolation; 查询当前事务隔离级别
JDBC控制数据库隔离级别：Connection类
```
 Connection setTransactionIsolation(int level);
```
level字段摘要：
- static int TRANSACTION_NONE 指示事务不受支持的常量。
- static int TRANSACTION_READ_COMMITTED 指示不可以发生脏读的常量；不可重复读和虚读可以发生。
- static int TRANSACTION_READ_UNCOMMITTED 指示可以发生脏读 (dirty read)、不可重复读和虚读 (phantom read) 的常量。
- static int TRANSACTION_REPEATABLE_READ 指示不可以发生脏读和不可重复读的常量；虚读可以发生。
- static int TRANSACTION_SERIALIZABLE 指示不可以发生脏读、不可重复读和虚读的常量。

6、事务的丢失更新问题(lost update)

丢失更新问题描述：两个事务查询同一行，再先后更新这一行，但这些事务彼此之间都不知道其它事务进行的修改，所以因此第二个更改覆盖了第一个修改。
首先介绍：在MySQL内部有两种常用锁：读锁和写锁

在mysql中默认情况下，在事务中当你修改数据，自动为数据加锁，防止两个事务同时修改数据 ---- 即读锁

事务和锁和不可分开的，锁一定是在事务中使用，当事务关闭锁自动释放。
- 读锁(共享锁) 一张表可以添加多个读锁，如果表已经添加了读锁（该读锁不是当前事务添加的），则该表不可以修改
  - select * from account lock in share mode;
  - 共享锁非常容易发生死锁（两个事务都为同一张表添加了共享锁，则该表在两个事务中都不能修改）
- 写锁(排它锁) 一张表只能加一个排它锁，如果表已经添加了写锁，则该表不可以查询，也不可以修改
  - select * from account for update ;
  - 如果一张表想添加排它锁，则之前表一定没有加过共享锁和排他锁，否则无法添加排它锁（排他锁和其它共享锁、排它锁都具有互斥效果）

丢失更新问题的解决有两种方法：
- 悲观锁（ Pessimistic Locking ）
```
  select * from table lock in share mode（读锁、共享锁）
  select * from table for update （写锁、排它锁）
```
- 乐观锁（ Optimistic Locking ）
  通过时间戳字段
悲观锁原理：使用数据库内部锁机制，进行table的锁定，在A修改数据时，A就将数据锁定，B此时无法进行修改 ----- 无法发生两个事务同时修改

由1、2点可知，悲观锁可以使用排它锁实现。

为什么叫悲观锁？

因为假设丢失更新会发生，所以是悲观的。

探秘数据库中的事务

1、事务的概念

2、MySQL管理事务

3、JDBC控制事务进程

4、事务的特性(ACID)

5、事务的隔离级别

6、事务的丢失更新问题(lost update)

猜你喜欢

热点阅读