1、事务

事务

事务是以一种可靠、一致的方式，访问和操作数据库中数据的程序单元
事务有四大特性，原子性，一致性，隔离性，持久性
原子性，事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行
一致性，事务在完成时，必须使所有的数据都保持一致状态
隔离性，由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据
持久性，事务完成之后，它对于系统的影响是永久性的

事务实现

1. SQL实现

begin transaction;
update tb_member_account set amount = amount - 100 where username = 'Tom';
update tb_member_account set amount = amount + 100 where username = 'Jerry';
commit
-- rollback

1. JDBC事务管理

    

   

   JDBC事务管理.png

    

   代码实现

Connection conn = getConnection();
conn.setAutoCommit(false);
Statement stmt1 = conn.prepareStatement(updateUserSQL1);
stmt1.executeUpdate(); 
Statement stmt2 = conn.prepareStatement(updateUserSQL2);
stmt2.executeUpdate();
conn.commit();
// conn.rollback(); 

实例

SQL事务管理

DROP TABLE IF EXISTS `tb_member_account`;
CREATE TABLE `tb_member_account`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `amount` decimal(10, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

INSERT INTO `tb_member_account` VALUES (1, 'Tom', 200.00);
INSERT INTO `tb_member_account` VALUES (2, 'Jerry', 300.00);

START TRANSACTION;
update tb_member_account set amount = amount + 100 where username = 'Tom';
update tb_member_account set amount = amount - 100 where username = 'Jerry';
COMMIT;

在COMMIT操作前，查询数据库是不会看到金额增加和减少的，只有commit后，查询，才会看到金额的增加和减少。

JDBC事务管理

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.25</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.1.11</version>
        </dependency>
    </dependencies>

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCTransactionApplication {

    private static final Logger LOG = LoggerFactory.getLogger(JDBCTransactionApplication.class);

    public static void main(String[] args) throws SQLException {
        Connection connection = getConnection();
        connection.setAutoCommit(false);
        String sql1 = "update tb_member_account set amount = amount + 100 where username = 'Tom'";
        PreparedStatement ps1 = connection.prepareStatement(sql1);
        ps1.executeUpdate();
        String sql2 = "update tb_member_account set amount = amount - 100 where username = 'Jerry'";
        PreparedStatement ps2 = connection.prepareStatement(sql2);
        ps2.executeUpdate();

        connection.commit();
        ps1.close();
        ps2.close();
        connection.close();

    }

    private static Connection getConnection() throws SQLException {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/account";
        String username = "root";
        String password = "root";
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            LOG.error(e.getLocalizedMessage());
        }
        return DriverManager.getConnection(url, username, password);
    }
}