JDBC operation transaction

ACID principle

Atomicity: Either succeed or fail.
Consistency: The total number does not change before and after the transaction.
Isolation: When multiple processes are parallel, they do not interfere with each other.
Persistence: Once the commit is irreversible, it will be persisted to the database.

Isolation problem

Dirty read: A transaction reads another uncommitted transaction.
Non-repeatable read: In the same transaction, the data in the table is read repeatedly, and the table data has changed.
Virtual read (phantom read): In a transaction, read I got the data inserted by others, resulting in inconsistent results.

Simulation case:
Create an account table:
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(40),
    money FLOAT
);

/*插入测试数据*/
INSERT INTO account(`NAME`,`money`) VALUES('A',1000);
INSERT INTO account(`NAME`,`money`) VALUES('B',1000);
INSERT INTO account(`NAME`,`money`) VALUES('C',1000);
Insert picture description here


1. Simulate A transfer to B100¥

package com.jialidun.test02;

import com.jialidun.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
           connection = JdbcUtils.getConnection();

        //关闭数据库的自动提交,自动会开启事务
            connection.setAutoCommit(false);

            String sql1 = "update account set money=money-100 where `NAME` = 'A'";
            pst = connection.prepareStatement(sql1);
            pst.executeUpdate();


            String sql2 = "update account set money=money+100 where `NAME` = 'B'";
           pst = connection.prepareStatement(sql2);
           pst.executeUpdate();
            //业务完毕,提交事务
            connection.commit();
            System.out.println("成功!");

        } catch (SQLException throwables) {

            try {
                //失败则回滚
                connection.rollback();
                System.out.println("执行失败!");
            } catch (SQLException e) {
                e.printStackTrace();
            }

            throwables.printStackTrace();
        }finally {

            //释放连接
            JdbcUtils.close(connection,pst,rs);
        }


    }
}

Insert picture description here


Check the database and find that A is

Insert picture description here


missing by 100, and B has increased by 100. Let us restore A and B to 1000 first, and if the simulation fails, the money of A and B will change:

package com.jialidun.test02;

import com.jialidun.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransaction {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        try {
           connection = JdbcUtils.getConnection();

        //关闭数据库的自动提交,自动会开启事务
            connection.setAutoCommit(false);

            String sql1 = "update account set money=money-100 where `NAME` = 'A'";
            pst = connection.prepareStatement(sql1);
            pst.executeUpdate();

            //模拟失败
            int x = 1/0;



            String sql2 = "update account set money=money+100 where `NAME` = 'B'";
           pst = connection.prepareStatement(sql2);
           pst.executeUpdate();
            //业务完毕,提交事务
            connection.commit();
            System.out.println("转账成功!");

        } catch (Exception throwables) {
           

            try {
                //失败则回滚
                connection.rollback();

            } catch (Exception e) {


                e.printStackTrace();

            }
            System.out.println("转账失败!");
            throwables.printStackTrace();
        }finally {

            //释放连接
            JdbcUtils.close(connection,pst,rs);
        }


    }

}

Insert picture description here


1. Open the transaction connection.setAutoCommit(false);
2. After the lease service is executed, commit the transaction
3. The transaction can be rolled back by the definition displayed in the catch statement, but the default will be rolled back if it fails