11. 数据库事务

前言

上一章节，我们学习了数据插入的批量操作，那么下面再来认识一下数据库事务。

数据库事务

1. 数据库事务介绍

事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务**回滚(rollback)**到最初状态。
为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

2. JDBC事务处理

数据一旦提交，就不可回滚。
数据什么时候意味着提交？

当一个连接对象被创建时，默认情况下是自动提交事务：每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
**关闭数据库连接，数据就会自动的提交。**如果多个操作，每个操作使用的是自己单独的连接，则无法保证事务。即同一个事务的多个操作必须在同一个连接下。

JDBC程序中为了让多个 SQL 语句作为一个事务执行：

“

若此时 Connection 没有被关闭，还可能被重复使用，则需要恢复其自动提交状态 setAutoCommit(true)。尤其是在使用数据库连接池技术时，执行close()方法前，建议恢复自动提交状态。

”

调用 Connection 对象的 setAutoCommit(false); 以取消自动提交事务
在所有的 SQL 语句都成功执行后，调用 commit(); 方法提交事务
在出现异常时，调用 rollback(); 方法回滚事务

2.1 数据库事务的问题引入

下面我们来看一个转账的示例。假设：用户AA向用户BB转账100，如下表：

当用户AA向用户BB转账，就需要执行两个SQL语句，一个是用户AA减少100，另一个是用户BB增加100，SQL如下：

-- 用户AA减少100
MySQL> update user_table set balance = balance - 100 where user = 'AA';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 用户BB增加100
mysql> update user_table set balance = balance + 100 where user = 'BB';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user_table;
+------+----------+---------+
| user | password | balance |
+------+----------+---------+
| AA   | 123456   |     900 |
| BB   | 654321   |    1100 |
| CC   | abcd     |    2000 |
| DD   | abcder   |    3000 |
+------+----------+---------+
4 rows in set (0.00 sec)

mysql>

下面我们用代码来实现一下这个过程。

2.1.1 不考虑事务的转账操作

用户AA 向用户 BB 转账

// 通用的增、删、改操作
public void update(String sql, Object... args) {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        //1. 使用JDBCUtils获取连接
        conn = JDBCUtils.getConnection();

        System.out.println(conn);

        //2.预编译sql语句，返回PreparedStatement的实例
        ps = conn.prepareStatement(sql);

        //3.填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }

        //4.执行
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //5.使用JDBCUtils关闭资源
        JDBCUtils.closeResource(conn, ps);
    }
}

// 用户AA向用户BB转账
@Test
public void test01() {
    String sql1 = "update user_table set balance = balance - 100 where user = ?";
    update(sql1, "AA");

    String sql2 = "update user_table set balance = balance + 100 where user = ?";
    update(sql2, "BB");

    System.out.println("转账成功");
}

执行如下：

查看数据库的数据：

成功转账。

考虑增加一些模拟的网络异常，查看导致转账失败的情况

我们首先将两个用户的金额bablance 恢复到1000，然后增加模拟异常，执行如下：

查看数据库如下：

那么有没有什么办法来解决这个问题呢？

当然有，这时候就要引入数据库的事务操作了。

2.1.2 考虑事务的转账操作

如果要使用事务，那么在操作数据库的时候，就要保持一个连接，在执行完毕之前，不能关闭资源。

但是上面的 update() 通用更新方法却有关闭资源的操作，所以这个地方要修改一下。

修改 update() 通用更新方法，去除关闭数据库连接的操作

//修改 update() 通用更新方法，去除 关闭数据库连接 的操作
public void updateNotClosed(Connection conn, String sql, Object... args) {
    PreparedStatement ps = null;
    try {
        //2.预编译sql语句，返回PreparedStatement的实例
        ps = conn.prepareStatement(sql);
        //3.填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        //4.执行
        ps.execute();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        //5.使用关闭资源
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

增加事务处理的转账操作

//增加事务处理的 转账操作
@Test
public void testTransaction() {
    Connection connection = null;
    try {
        //1.获取数据库连接
        connection = JDBCUtils.getConnection();

        //2.设置取消数据的自动提交
        connection.setAutoCommit(false);

        //3.执行转账操作
        String sql1 = "update user_table set balance = balance - 100 where user = ?";
        updateNotClosed(connection, sql1, "AA");

        // 模拟网络异常
        System.out.println(10 / 0);

        String sql2 = "update user_table set balance = balance + 100 where user = ?";
        updateNotClosed(connection, sql2, "BB");

        System.out.println("转账成功");

        //4.提交数据库数据
        connection.commit();
    } catch (Exception e) {
        e.printStackTrace();
        //5.执行出现异常，执行回滚事务
        try {
            connection.rollback();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    } finally {
        //6.关闭数据库连接
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

关闭模拟的网络异常操作

首先执行一次，转账成功，此时，可以查看mysql的执行日志，如下:

执行完毕之后， mysql 的数据如下：

设置模拟的网络异常操作，确认转账是否数据回滚

执行操作之后，mysql的日志如下：

可以看到执行了更新 AA 用户的账单，但是后续又执行了 rollback 回滚操作，也就是数据没有变化。

此时，mysql的数据如下：

通过上面的例子，只要有事务的控制，就算多条SQL变更数据，通过数据的回滚，就算出现了异常，也可以保证数据的原子性。

在结束数据操作之后，将数据库的连接设置恢复默认提交的方式

//7.恢复默认自动提交的方式
try {
    connection.setAutoCommit(true);
} catch (SQLException e) {
    e.printStackTrace();
}

因为我们不知道后续的数据库操作要不要使用事务，那么最好在最后的时候，将数据库恢复回默认的自动提交方式。

6.3 事务的ACID属性

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation）事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

6.3.1 数据库的并发问题

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:

脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

6.3.2 四种隔离级别

数据库提供的4种事务隔离级别：

Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。Oracle 默认的事务隔离级别为: READ COMMITED 。
Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为: REPEATABLE READ。

6.3.3 在MySql中设置隔离级别

每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。
查看当前的隔离级别:
```
SELECT @@tx_isolation;
```

设置当前 mySQL 连接的隔离级别:

set  transaction isolation level read committed;

设置数据库系统的全局的隔离级别:

set global transaction isolation level read committed;

补充操作：

创建mysql数据库用户：

create user tom identified by 'abc123!#@A';

授予权限

#授予通过网络方式登录的tom用户，对所有库所有表的全部权限，密码设为abc123!#@A.
grant all privileges on *.* to tom@'%'  identified by 'abc123!#@A'; 

#给tom用户使用本地命令行方式，授予test这个库下的所有表的插删改查的权限。
grant select,insert,delete,update on test.* to tom@localhost identified by 'abc123!#@A';

6.4 命令行验证MySQL的隔离级别

按照上面的命令，下面我们来执行操作演示一遍，单纯看命令行还是没那么直观。

1.使用root用户来创建 mysql 数据库用户，后续用两个用户相互操作来演示隔离级别：

[root@server01 ~]# mysql -uroot -p
..
mysql> create user tom identified by 'abc123!#@A';
Query OK, 0 rows affected (0.01 sec)

好了，此时我们已经在 mysql 钟创建好了一个叫做 tom 的用户，下面使用这个用户来访问 mysql。

2.使用 root 用户赋予 tom 用户可以才做 test数据库下所有表的权限

mysql> grant select,insert,delete,update on test.* to tom@localhost identified by 'abc123!#@A'; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

配置之后，使用 tom 用户登录 mysql，可以查看到 test 数据库，如下：

下面我们首先来演示一下mysql默认的隔离级别。

3.mysql 默认的隔离级别：REPEATABLE-READ

3.1 首先用两个用户分别查看隔离级别

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql>

3.2 两个用户的连接都设置不自动 commit 提交，然后分别查询 user_table 表

root 用户与 tom 用户两者操作：

-- 禁止自动commit提交
mysql> set autocommit=false;
-- 切换使用test数据库
mysql> use test
-- 查询user_table表的数据
mysql> select * from user_table;

3.3 下面其中一个用户 update 用户 CC 的 balance，然后commit 提交变更。同时，另一个用户还未提交 commit，此时查询用户 CC 的 balance 不会发生改变

root用户操作：

mysql> update user_table set balance = 2500 where user = 'CC';
mysql> commit;

那么问题来了，Tom用户要怎么样才可以查询到更新后的数据呢？

其实只需要 Tom 用户也执行一个 commit，结束本次操作的事务，再次查询就可以看到更新后的数据了。

3.4 Tom用户执行 commit 结束事务，再次查询得到更新后的数据

其实也就是说，mysql的隔离级别限制了每个事务只能查询到在开始之前的更新数据，如果事务没有结束，则不会查询到其他并发导致的数据更新。

4.mysql的隔离级别：READ-COMMITED

上面我们可以看到在事务的过程中，并不能查询其他事务的更新数据，那么我们来修改一下隔离级别，演示一下在事务过程中可以查询到其他事务更新数据的情况。

4.1. 设置事务的隔离级别，同时两个用户需要重新建立mysql连接才能生效

-- 配置全局修改隔离级别为 read committed
mysql> set global transaction isolation level read committed;
-- 查看当前的隔离级别
mysql> SELECT @@tx_isolation;

4.2 两个用户重新 mysql 连接之后，重新设置不自动 commit 提交代码。并且演示其中一个用户更新数据，另一个用户的查询情况。

-- 禁止自动commit
mysql> set autocommit=false;
-- 切换使用test数据库
mysql> use test;
-- 查询CC用户数据
mysql> select * from user_table where user = 'CC';
-- 更新CC用户的balance
mysql> update user_table set balance = 3000 where user = 'CC';
-- 提交执行
mysql> commit;

从上面的执行过程，我们可以发现 READ COMMITED 的隔离级别，不需要当前事务结束，直接就可以查看其他并发事务更新后的数据。

5.mysql 的隔离级别：READ-UNCOMMITED

这种应该是问题最多的事务隔离级别了，因为其他事务只要更新，不需要commit。其他事务直接就查询到了。

5.1. 设置事务的隔离级别，同时两个用户需要重新建立mysql连接才能生效

-- 配置全局修改隔离级别为 read uncommitted
mysql> set global transaction isolation level read uncommitted;
-- 查看当前的隔离级别
mysql> SELECT @@tx_isolation;

5.2 两个用户重新 mysql 连接之后，重新设置不自动 commit 提交代码。并且演示其中一个用户更新数据，另一个用户的查询情况。

-- 禁止自动commit
mysql> set autocommit=false;
-- 切换使用test数据库
mysql> use test;
-- 查询CC用户数据
mysql> select * from user_table where user = 'CC';
-- 更新CC用户的balance
mysql> update user_table set balance = 4000 where user = 'CC';

6.5 Java代码演示并设置数据库的隔离级别

上面我们在mysql的命令行演示了隔离级别的操作，下面我们在JAVA代理来演示一下操作。

首先准备下面的代码，然后后面我们来测试一下。

1.针对于不同的表的通用的查询操作，返回表中的一条记录

    //使用 泛型 编写不同表的通用查询操作
    //针对于不同的表的通用的查询操作，返回表中的一条记录
    public static <T> T getInstance(Connection conn,Class<T> clazz, String sql, Object... args) throws Exception {
        //1. 使用JDBCUtils获取连接
//        Connection conn = jdbc.test.JDBCUtils.getConnection();

        //2.预编译sql语句，返回PreparedStatement的实例
        PreparedStatement ps = conn.prepareStatement(sql);
        //  设置占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }

        //3.执行,并返回结果集
        ResultSet resultSet = ps.executeQuery();

        //4.获取返回结果的列数，后续可以用来遍历获取/设置字段值
        //4.1 获取结果集的元数据 :ResultSetMetaData
        ResultSetMetaData metaData = resultSet.getMetaData();

        //4.2 通过ResultSetMetaData获取结果集中的列数
        int columnCount = metaData.getColumnCount();

        //5.处理结果集
        //next():判断结果集的下一条是否有数据，
        // 如果有数据返回true,并指针下移；
        // 如果返回false,指针不会下移。
        if (resultSet.next()) {

            //创建泛型对应类的对象
            T t = clazz.newInstance();

            //获取当前这条数据的各个字段值
            for (int i = 0; i < columnCount; i++) {
                //获取列值
                Object columValue = resultSet.getObject(i + 1);

                //获取每个列的列名
                String columnName = metaData.getColumnName(i + 1);
                //获取每个列的别名
                String columnLabel = metaData.getColumnLabel(i + 1);

                //给order对象指定的columnName属性，赋值为columValue：通过反射
                Field field = clazz.getDeclaredField(columnLabel); // 根据字段别名 获取 Order 的属性
                field.setAccessible(true); // 设置允许操作所有权限的数据
                field.set(t, columValue); // 设置对象的字段值

            }

            // 返回 t
            return t;
        }

        //5.关闭资源
        ps.close();
        resultSet.close();
        return null;
    }

2.编写UserTable类，用于接收处理 user_table 表的数据

/**
 * @author Aron.li
 * @date 2020/10/24 13:54
 */
public class UserTable {

    //成员属性
    private String user;
    private String password;
    private Integer balance;

    //构造器
    public UserTable() {
    }

    public UserTable(String user, String password, Integer balance) {
        this.user = user;
        this.password = password;
        this.balance = balance;
    }

    //getter setter
    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getBalance() {
        return balance;
    }

    public void setBalance(Integer balance) {
        this.balance = balance;
    }

    //toString

    @Override
    public String toString() {
        return "UserTable{" +
                "user='" + user + ''' +
                ", password='" + password + ''' +
                ", balance=" + balance +
                '}';
    }
}

3.编写设置数据库隔离级别为 read commited 的查询

//设置数据库的隔离级别，并且查询 user_table 数据
@Test
public void testTransitionSelect() throws Exception {
    //1.获取数据连接
    Connection connection = JDBCUtils.getConnection();
    //2.获取当前连接的隔离级别
    System.out.println("当前连接的隔离级别：" + connection.getTransactionIsolation());
    //3.设置数据库的隔离级别: read commited (不受mysql的隔离级别，只是本次java连接的隔离级别)：
    connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    //4.取消自动commit
    connection.setAutoCommit(false);
    //5.查询 user_table 的数据
    String sql = "select user,password,balance from user_table where user = ?";
    UserTable userTable = getInstance(connection, UserTable.class, sql, "CC");
    System.out.println(userTable);
    //注意：不关闭mysql的连接进行测试。
}

4.编写更新数据库 user_table 表

//设置更新CC用户的balance数据，并设置休眠15秒，避免立即断开连接。
//可以在此时查询数据，确认在commit之前的数据查询情况
@Test
public void testTransitionUpdate() throws Exception {
    //1.获取数据库连接
    Connection connection = JDBCUtils.getConnection();
    //2.取消自动提交数据
    connection.setAutoCommit(false);
    //3.执行更新CC用户的balance数据
    String sql1 = "update user_table set balance = balance - 100 where user = ?";
    updateNotClosed(connection,sql1, "CC");
    //4.休眠15秒，再次期间可以查询CC用户数据，确认balance是否变更
    Thread.sleep(15000);
    //5.15秒之后，提交数据
    connection.commit();
    System.out.println("修改结束");
}