JDBC transaction,

JDBC transaction,

Transaction Overview
A transaction refers to a logical set of operations (Multiple SQL statements). Each unit that makes up this set of operations is either all successful or all failed.

Role of transactions
Ensure that multiple operations in a transaction either succeed or fail.

Mysql transaction operations
The Connection interface has transaction-related methods:
Void setAutoCommit (boolean autoCommit) sets the automatic submission mode of this connection to a given State.
AutoCommit-true indicates that automatic submission mode is enabled; false indicates that automatic submission mode is disabled.
Set autoCommit to false to enable the transaction manually
Void commit () if multiple SQL statements are successfully executed, submit the transaction
Void rollback () When an SQL Execution fails, the transaction is rolled back and the data is rolled back to the status before the transaction is started.

Code Implementation

1 public class Demo01Account {2 public static void main (String [] args) {3 Connection conn = null; 4 Statement stat = null; 5 try {6 // register driver 7 Class. forName ("com. mysql. jdbc. driver "); 8 // get the connection 9 conn = DriverManager. getConnection ("jdbc: mysql: // localhost: 3306/day06", "root", "root"); 10 // enable transaction 11 conn. setAutoCommit (false); 12 // get the performer object 13 stat = conn. createStatement (); 14 int I = stat.exe cuteUpdate ("upda Te account set money = money-100 where name = 'Tom '"); 15 int num = 0/0; // tom money is reduced, jerry didn't add 16 int j = stat.exe cuteUpdate ("update account set money = money + 100 where name = 'Jerry '"); 17 if (I> 0 & j> 0) {18 System. out. println ("transfer successful! "); 19} 20 // submit transaction 21 conn. commit (); 22} catch (Exception e) {23 e. printStackTrace (); 24 // roll back things 25 try {26 conn. rollback (); 27} catch (SQLException e1) {28 e1.printStackTrace (); 29} 30} finally {31 if (stat! = Null) {32 try {33 stat. close (); 34} catch (Exception e) {35 e. printStackTrace (); 36} 37} 38 if (conn! = Null) {39 try {40 conn. close (); 41} catch (Exception e) {42 e. printStackTrace (); 43} 44} 45} 46 47} 48}

Features of transactions

Atomicity: the transaction cannot be separated. Multiple statements either succeed or fail.
Consistency: data must be consistent before and after the transaction is executed.
Isolation: the execution of a transaction should not be affected by other transactions.
Persistence: Once the transaction ends (commit/rollback), the data is permanently stored in the database.

If the isolation of transactions is not considered, some security problems are caused.
Dirty read: one transaction reads data that has not been committed by another transaction.
Dirty reading, also known as reading invalid data, means that during database access, transaction T1 modifies a value and transaction T2 reads the value, after that, T1 revokes the modification to this value for some reason, which leads to the invalid data read by T2.
Repeatable read: one transaction reads the data submitted by another transaction, resulting in inconsistent query results for multiple times in the current transaction.
Virtual read/phantom read: one transaction reads the insert data committed by another transaction, resulting in inconsistent query results for multiple times in the current transaction.

Solve read Problems
Set the transaction isolation level: the higher the level, the more secure, the lower the efficiency.
1 read uncommitted: uncommitted read. Dirty read. Repeated read is not allowed. All virtual reads may occur.
2 read committed: committed read. Avoid dirty read. But repeated read and virtual read may occur. (Oracle default)
4 repeatable read)
8 serializable: serializable. Avoid dirty reads, repeated reads, and virtual reads.