Spring transactions (JDBC) for transactions in java

Characteristics of transactions:

1) Atomicity: A transaction is a logical unit of work of the database, and it must be an atomic unit of work. For its data modifications, either all of them are executed or none of them are executed. .

2) Consistency: When the transaction is completed, all data must be consistent. In the relevant database, all rules must be applied to transaction modifications to maintain the integrity of all data.

3) Isolation: The execution of a transaction cannot be affected by other transactions.

4) Durability: Once a transaction is submitted, the operation of the transaction is permanently stored in the DB. Even if you perform a rollback operation at this time, the changes will not be undone.

Transaction: It is a unit of concurrency control and a user-defined sequence of operations. Either all of these operations are done, or none of them are done, and they are an integral unit of work. Through transactions, SQL Server can bind a set of logically related operations together so that the server maintains data integrity. A transaction usually starts with begin transaction and ends with commit or rollback. Commint means submission, that is, all operations of committing a transaction. Specifically, all updates to the data in the transaction are written back to the physical database on the disk, and the transaction ends normally. Rollback means rollback, that is, some kind of failure occurs while the transaction is running and the transaction cannot continue. The system undoes all completed operations on the database in the transaction and rolls back to the state where the transaction started.

Auto-commit transactions: Each separate statement is a transaction. There is an implicit commit after each statement. (Default)

Explicit transaction: starts with begin transaction display and ends with commit or rollback.

Implicit transaction: When the connection operates in implicit transaction mode, the SQL Server database engine instance will automatically start a new transaction after committing or rolling back the current transaction. There is no need to describe the beginning of things, just commit or rollback each transaction. But each transaction still ends explicitly with commit or rollback. After the connection sets the implicit transaction mode to open, an implicit transaction will be automatically started when the database engine instance executes any of the following statements for the first time: alter table, insert, create, open, delete, revoke, drop, select, fetch, truncate table, grant, update transaction will remain valid until a commit or rollback statement is issued. After the first transaction is committed or rolled back, the database engine instance will automatically start a new transaction the next time the connection executes any of the above statements. This instance will continue to generate implicit transaction chains until implicit transaction mode is turned off.

Java JDBC transaction mechanism

First, let’s take a look at what major problems the existing JDBC operations will cause us. For example, there is a business: when we modify a After querying the information, it turns out that this is a simple business and it is very easy to implement. However, when this business is placed on a multi-threaded high-concurrency platform, problems naturally arise. For example, when we execute a modification Finally, before executing the query, a thread also executed the modification statement. If we execute the query again, the information we see may be different from what we modified. In order to solve this problem, we must introduce the JDBC transaction mechanism. In fact, the code The implementation is very simple. Here is a principle implementation example for your reference:

private Connection conn = null;  
private PreparedStatement ps = null;  
 
try {  
    conn.setAutoCommit(false);  //将自动提交设置为false  
              
    ps.executeUpdate("修改SQL"); //执行修改操作  
    ps.executeQuery("查询SQL");  //执行查询操作                 
    conn.commit();      //当两个操作成功后手动提交  
              
} catch (Exception e) {  
    conn.rollback();    //一旦其中一个操作出错都将回滚，使两个操作都不成功  
    e.printStackTrace();  
}

Theories related to transactions
1. The four attributes (ACID) of transactions (Transaction)
Atomicity ( Atomic) Either all modifications to the data are performed or none are performed.
Consistency (Consistent) The data state remains consistent before and after transaction execution.
Isolated (Isolated) The processing of one transaction cannot affect the processing of another transaction.
Durable (Durable) The transaction ends and its effects are persisted in the database.

2. Possible problems caused by concurrent transaction processing
Dirty read (dirty read) A transaction reads data that has not been submitted by another transaction,
Non-repeatable read (non-repeatable read) A The operation of a transaction causes another transaction to read different data twice before and after
Phantom read (phantom read) The operation of a transaction causes another transaction to read different data amounts twice before and after the query.
Example:
When transactions A and B are executed concurrently,
After transaction A is updated, transaction B selects to read the uncommitted data of A. At this time, transaction A rollbacks, and the data read by B is Invalid "dirty" data.
When B transaction select reads the data, A transaction update operation changes the data selected by B transaction. At this time, B transaction reads the data again and finds that the two data are different.
After transaction B selects to read the data, transaction A inserts or deletes a record that satisfies the select conditions of transaction A. At this time, transaction B selects again and finds that a record that did not exist last time ("phantom") was queried. Or a certain record from the previous time is missing.

JDBC’s transaction support
JDBC’s support for transactions is reflected in three aspects:
1. Auto-commit mode (Auto-commit mode)
Connection provides an auto-commit attribute to specify the transaction When will it end.
a. When auto-commit is true, when the execution of each independent SQL operation is completed, the transaction is automatically submitted immediately, which means that each SQL operation is a transaction.
When an independent SQL operation is completed, the JDBC specification stipulates this:
For data operation language (DML, such as insert, update, delete) and data definition language (such as create, drop), statement one Once executed, it is deemed completed.
For a select statement, execution is deemed completed when the ResultSet object associated with it is closed.
For stored procedures or other statements that return multiple results, when all ResultSet objects associated with it are closed, all update count (the number of rows affected by update, delete and other statement operations) and output parameters (output parameters of the stored procedure) ) have been obtained, the execution is deemed to be completed.
b. When auto-commit is false, each transaction must explicitly call the commit method to commit, or explicitly call the rollback method to roll back. auto-commit defaults to true.
JDBC provides 5 different transaction isolation levels, which are defined in Connection.

2. Transaction Isolation Levels
JDBC defines five transaction isolation levels:
TRANSACTION_NONE JDBC driver does not support transactions
TRANSACTION_READ_UNCOMMITTED allows dirty reads, non-repeatable reads and phantom reads read.
TRANSACTION_READ_COMMITTED prohibits dirty reads, but allows non-repeatable reads and phantom reads.
TRANSACTION_REPEATABLE_READ prohibits dirty reads and non-repeatable reads, and only runs phantom reads.
TRANSACTION_SERIALIZABLE prohibits dirty reads, non-repeatable reads and phantom reads.

3. Save Point (SavePoint)
JDBC defines the SavePoint interface to provide a more fine-grained transaction control mechanism. When a savepoint is set, you can rollback to the state at the savepoint instead of rolling back the entire transaction.
The setSavepoint and releaseSavepoint methods of the Connection interface can set and release savepoints.

Although the JDBC specification defines the above support behaviors for transactions, the degree of support for transactions by various JDBC drivers and database manufacturers may vary. If you set it arbitrarily in the program, you may not get the desired effect. To this end, JDBC provides the DatabaseMetaData interface, which provides a series of methods for obtaining JDBC feature support. For example, the support for transaction isolation levels can be determined through the DatabaseMetaData.supportsTransactionIsolationLevel method, and the support for savepoints can be determined through the DatabaseMetaData.supportsSavepoints method.

The above is the detailed content of Spring transactions (JDBC) for transactions in java. For more information, please follow other related articles on the PHP Chinese website!