11. 数据库事务

Devops海洋的渔夫

发布于 2022-01-17 14:54:31

2380

发布于 2022-01-17 14:54:31

文章被收录于专栏：Devops专栏Devops专栏

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';

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("修改结束");
}

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

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

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

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-06-16，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

云数据库 SQL Server

本文分享自海洋的渔夫微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

数据库

sql

云数据库 SQL Server

登录后参与评论

0 条评论

热度

11. 数据库事务

11. 数据库事务

11. 数据库事务

前言

数据库事务

1. 数据库事务介绍

2. JDBC事务处理

2.1 数据库事务的问题引入

2.1.1 不考虑事务的转账操作

用户AA 向 用户 BB 转账

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

2.1.2 考虑事务的转账操作

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

增加事务处理的 转账操作

关闭模拟的网络异常操作

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

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

6.3 事务的ACID属性

6.3.1 数据库的并发问题

6.3.2 四种隔离级别

6.3.3 在MySql中设置隔离级别

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4.编写更新数据库 user_table 表

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

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

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

用户AA 向用户 BB 转账

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

增加事务处理的转账操作

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

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

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

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