11. 数据库事务

11. 数据库事务

前言

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

数据库事务

1. 数据库事务介绍

  • 事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。

  • 事务处理(事务操作):保证所有事务都作为一个工作单元来执行,即使出现了故障,都不能改变这种执行方式。当在一个事务中执行多个操作时,要么所有的事务都被提交(commit),那么这些修改就永久地保存下来;要么数据库管理系统将放弃所作的所有修改,整个事务**回滚(rollback)**到最初状态。

  • 为确保数据库中数据的一致性,数据的操纵应当是离散的成组的逻辑单元:当它全部完成时,数据的一致性可以保持,而当这个单元中的一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状态。

2. JDBC事务处理

  • 数据一旦提交,就不可回滚。

  • 数据什么时候意味着提交?

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

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

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

2.1 数据库事务的问题引入

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


image-20201022081346315

当用户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("转账成功");
}

执行如下:

image-20201022082409134

查看数据库的数据:


image-20201022082434039

成功转账。

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

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


image-20201022082824409

查看数据库如下:


image-20201022082916924

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

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

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();
            }
        }
    }

}
关闭模拟的网络异常操作

image-20201022233730382

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


image-20201022233859040

执行完毕之后, mysql 的数据如下:


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

image-20201022234409894

执行操作之后,mysql的日志如下:


image-20201022234439013

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

此时,mysql的数据如下:


image-20201022234552017

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

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

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

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

6.3 事务的ACID属性

  1. 原子性(Atomicity)原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

  2. 一致性(Consistency)事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

  3. 隔离性(Isolation)事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。

  4. 持久性(Durability)持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。

6.3.1 数据库的并发问题

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

    • 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
    • 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
    • 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。
  • 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。

  • 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

6.3.2 四种隔离级别

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

1555586275271
  • 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 数据库,如下:

image-20201024100039121

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

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

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

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

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

image-20201024102418983

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 不会发生改变

image-20201024103046362

root用户操作:

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

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

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

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

image-20201024103333521

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

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

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

4.1. 设置事务的隔离级别,同时两个用户需要重新建立mysql连接才能
image-20201024104237584
-- 配置全局修改隔离级别为 read committed
mysql> set global transaction isolation level read committed;
-- 查看当前的隔离级别
mysql> SELECT @@tx_isolation;
4.2 两个用户重新 mysql 连接之后,重新设置不自动 commit 提交代码。并且演示其中一个用户更新数据,另一个用户的查询情况。

image-20201024105021428
-- 禁止自动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连接才能生效

image-20201024105825105
-- 配置全局修改隔离级别为 read uncommitted
mysql> set global transaction isolation level read uncommitted;
-- 查看当前的隔离级别
mysql> SELECT @@tx_isolation;
5.2 两个用户重新 mysql 连接之后,重新设置不自动 commit 提交代码。并且演示其中一个用户更新数据,另一个用户的查询情况。

image-20201024110435001
-- 禁止自动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("修改结束");
}

5.执行测试:首先执行更新数据库,然后执行查询数据,确认查询的数据

5.1 首先在更新之前,查询当前的数据

image-20201024142306624
5.2 执行更新数据,同时查询数据,确认是否能够查询 commit 之前的数据

image-20201024142421979image-20201024142447234

 

上一篇:mysql中主外键关系


下一篇:❤️【c++查缺补漏系列】多维数组与函数传值❤️