In depth resolution of JDBC transactions in Java
- 2020-04-01 04:06:51
- OfStack
The transaction
A transaction is a logical unit of execution consisting of one or more steps that constitute a sequence of operations that either execute in its entirety or abandon execution altogether. Four characteristics of transactions: Atomicity, Consistency, IsoIation, and persistence. Atomicity is the smallest unit of execution for a transaction and cannot be redivided. Is the smallest logical executable that cannot be subdivided in a transaction.
Consistency: the result of the execution of a transaction must change the Consistency of the database from one consistent state to another.
Isolation line (IsoIation) : the execution of each transaction does not interfere with each other, and the internal operation of any one transaction is isolated from other concurrent transactions. That is, concurrent transactions cannot see each other's intermediate state, and concurrent transactions cannot affect each other.
Durability: Persistence, also known as Persistence, means that any changes to data that a transaction commits are logged into permanent storage, usually in a physical database.
Generally, the transaction of the database involves a set of DML (Data Munipulation Language) statements, which will maintain a good consistency of the Data after modification. Operation table statements, such as insert, modify, delete, etc. A DDL (Data Definition Language) statement that operates on a Data object, such as create, alter, drop. A DCL (Data Control Language) statement, mainly has grant, revoke statements. There can be no more than one DDL and DCL statement, because they both result in immediate commit of the transaction. When all the database operations that the transaction contains are successfully executed, the transaction should be committed to make these changes permanent. There are two ways to commit a transaction: display commit and auto commit. Automatic commit: when a DLL or DCL is executed, or the program exits normally, when any database operation contained in the transaction fails, the transaction should be rolled back, invalidating all changes made in that transaction. There are two ways to rollback a transaction: display rollback and automatic rollback. Display rollback: automatic rollback using rollback: system error or forced exit.
Concurrent transactions handle possible problems
1. Dirty read: one transaction reads data that has not yet been committed by another transaction
Non-repeatable read: the operation of one transaction results in two different data reads before and after another transaction
3. Phantom read: the operation of one transaction results in different amount of result data of two queries before and after another transaction
For example:
Concurrent execution of transactions A and B:
After A transaction update, B transaction select reads the data that A has not yet committed, at this time A transaction rollback, the data that B reads is invalid dirty data When B transaction select reads the data, A transaction update operation changes the data from B transaction select. At this time, B transaction reads the data again and finds that the data before and after the two times are not the same When B transaction select reads data, A transaction inserts or deletes A record that satisfies the select condition of A transaction. At this time, B transaction selects again and finds that the previous record does not exist or that A previous record is missing
Java JDBC transaction mechanism
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCTransaction {
public static final String URL = "com.mysql.jdbc.Driver";
public static final String USER = "root";
public static final String PASSWD = "123456";
public static void jdbcTransaction(int id) {
Connection conn = null;
PreparedStatement pstmtupdate = null;
PreparedStatement pstmtquery = null;
String updatesql = " update sql";
String querysql = " The query sql";
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(URL, USER, PASSWD);
conn.setAutoCommit(false); //Auto commit is set to false
//Perform an update operation
pstmtupdate = conn.prepareStatement(updatesql);
pstmtupdate.executeUpdate();
//Perform a find operation
pstmtquery = conn.prepareStatement(querysql);
pstmtquery.executeQuery();
conn.commit();
conn.setAutoCommit(true);
pstmtupdate.close();
pstmtquery.close();
conn.close();
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {}
e.printStackTrace();
} finally {
try {
if (pstmtupdate != null) {
pstmtupdate.close();
}
if (pstmtquery != null) {
pstmtquery.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e2) {}
}
}
}
Transaction support for JDBC
JDBC's Connection also supports things, and Connection turns on auto-commit by default, which closes things. That is, each SQL statement is immediately committed to the database, permanently in effect, and cannot be manipulated. Turn off the auto-commit of Connection and turn on something. The setAutoCommit method of Connection is: connection.setautocommit (false); Get the pattern of things through connection.getautocommit (). When we open things, the database operations that are done in the current Connection are not immediately committed to the database, and the Connection's commit method needs to be called. If a statement fails, you can call rollback to rollback and forth. Note: if the Connection encounters an unhandled SQLException, the system exits abnormally and the system automatically rolls back the transaction. If the program catches the exception, it needs to display the rollback transaction in the exception handling. Connection provides a method to set the transaction intermediate Savepoint: setSavepoint, and there are two methods to set the intermediate point: Savepoint: Savepoint setSavepoint() : creates an unnamed intermediate point in the current transaction and returns the Savepoint object for that intermediate point. Savepoint setSavepoint(String name) : a Savepoint object with the specified name is created in the current transaction, and the Savepoint object of the specified name is returned. Setting the name is just a better way to distinguish the intermediate point object, which can be rolled back to the specified intermediate point using the Connection's rollback(Savepoint Savepoint) method.
JDBC supports transactions in three ways:
1. Auto-commit mode
The Connection provides an auto-commit property to specify when the transaction ends
2. When auto-commit is true, when each individual SQL operation is completed, the transaction is automatically committed immediately, which means that each SQL operation is a transaction
When a single SQL operation is completed, the JDBC specification defines it as follows:
For data manipulation language (DML) and data definition language (DDL), a statement is considered to be executed as soon as it is executed
3. When auto-commit is false, each transaction must be shown calling the commit method for commit or calling the rollback method for rollback. Auto-commit defaults to true
Transaction Isolation Levels
JDBC defines five transaction isolation levels:
The savepoint
JDBC defines the SavePoint interface to provide a more granular transaction control mechanism. When a savepoint is set, it can rollback to the state at that savepoint, rather than rollback the entire transaction
First of all, let's take a look at existing JDBC operations would bring us what major issues, such as a business: when we modify an information and then to query the information appears to be a simple business, are easier to implement, but when the business in multi-threaded platform of high concurrency, problems appeared, and naturally when we performed a modified, for example, before executing queries have a thread is also carried out to modify the statement, we execute the query again, see the information it is possible to modify different with us. To solve this problem, we must introduce the JDBC transaction mechanism. The implementation code is very simple.