JDBC Study ( Four )—MySQL Business /MySQL Transaction control for /JDBC Operational transaction

01 Business

• MySQL Business ： A set of SQL Statements are executed in the same batch , If one SQL Statement error , Then all of the SQL Will be cancelled .
• MySQL Transactions only support InnoDB and BDB Data table type .
• The principle of affairs ：
  • Atomicity （Atomic）： All operations in the whole transaction , Or it’s all done , Or not at all , It’s impossible to stop in the middle .
  • Uniformity （consist）： A transaction can encapsulate state changes （ Unless it’s a read-only ）. Transactions must always keep the system in a consistent state , No matter how many concurrent transactions there are at any given time .（ give an example ： Suppose you have five accounts , The balance of each account is 100 element , So the total amount of the five accounts is 500 element , If it’s here 5 There are multiple transfers between accounts at the same time , No matter how many concurrent , For example A And B Transfer between accounts 5 element , stay C And D Transfer between accounts 10 element , stay B And E Transfer between 15 element , The total amount of five accounts should still be 500 element , That’s protection and invariance .）
  • Isolation, （Isolated）： Isolated state execution transactions , Make them seem like the only operations that the system performs at a given time . If there are two things , Run at the same time , Perform the same function , The isolation of transactions ensures that each transaction is considered to be the only one using the system . This property is sometimes called serialization , To prevent confusion between transaction operations , The request must be serialized or serialized , Make only one request for the same data at the same time .
  • persistence （Durable）： After the transaction is completed , The changes made by the transaction to the database are persisted in the database , It will not be rolled back .

matters needing attention ：MySQL The default transaction in is auto commit , Once committed, it cannot be rolled back . If you want to use transaction rollback , Transaction control must be turned on

02 Transaction control

Transaction control ： After turning off auto commit transactions （ Turn on transaction control ）, If an error occurs during the execution of a transaction , Before submitting , You can use rollback （ROLLBACK） Bring the transaction back to the state it was before it started , It’s like this transaction has never been executed .

• MySQL Transaction control steps of

# Turn off automatic transaction commit , Turn on transaction control
SET autocommit = 0;
# Mark the starting point of a transaction
START TRANSACTION;
# Mark save point
SAVEPOINT;
# Commit the transaction to the database
COMMIT;
# Roll back the transaction , Data back to the starting point of this transaction
ROLLBACK;
# Roll back the transaction , The data is returned to the save point of this transaction
ROLLBACK TO SAVEPOINT;
# Delete savepoint
RELEASE SAVEPOINT;
# Restore MySQL Automatic submission of databases , Turn off transaction control
SET autocommit =1;

• Example

# Roll back of transfer transaction
# background ：account In the database A Yes 2000 element ,B Yes 2000 element Business ：A towards B Transfer accounts 500 element
SET autocommit = 0;
START TRANSACTION;
UPDATE account SET balance=balance-500 WHERE `name`='A';
UPDATE account SET balance=balance+500 WHERE `name`='B';
rollback;
COMMIT;
SET autocommit = 1;

03 JDBC Operational transaction

JDBC To operate a transaction, you need to use connection adopt setAutoCommit(false) Turn on transaction control ,rollback() Roll back the transaction ,commit() Commit transaction

public class TestDemo1 {
public static void main(String[] args) throws SQLException {
// Here we continue to use the previous explanation of JDBC The tool class establishes database connection and releases resources
Connection connection = JDBCUtils.getConnection();
// Turn off the transaction auto commit of the database , namely , Turn on transaction control ;
connection.setAutoCommit(false);
// establish sql sentence
String sql1 = "update account set balance = balance - 100 where name='A'";
String sql2 = "update account set balance = balance + 100 where name='B'";
// perform sql sentence
Statement statement = connection.createStatement();
statement.executeUpdate(sql1);
statement.executeUpdate(sql2);
// Transaction rollback
connection.rollback();
// Transaction submission
connection.commit();
// Release resources
JDBCUtils.closeAll(null,statement,connection);
}
}

matters needing attention ： After the transaction control is turned on , If you don’t commit the transaction , All the operations are in mysql After disconnection , It’s going to be back to where it started