A detailed explanation of JDBC transactions in Java

First, the understanding of the business:

1, the characteristics of the transaction:

1) atomicity (atomicity): A transaction is a logical unit of work for a database, and it must be an atomic unit of work, either fully executed or not executed for its data modification.

2) Consistency (consistency): When a transaction is complete, it must be that all data remains in a consistent state. In a related database, all rules must be applied to transaction modifications to maintain the integrity of all data.

3) Isolation (isolation): The execution of one transaction cannot be affected by other firms.

4) Persistence (durability): Once a transaction is committed, the operation of the thing is permanently stored in the DB. Even if you perform a rollback operation at this point, you cannot undo the changes.

2, the definition of the transaction:

Transaction (Transaction): is a unit of concurrency control and is a user-defined sequence of operations. These operations are either done or not, and are an inseparable unit of work. With transactions, SQL Server can bind a logically related set of operations so that the server maintains the integrity of the data.

A transaction is usually started with begin transaction and ends with commit or rollback:

Commint represents the commit, which commits all operations of the transaction. Specifically, all updates to the data in a transaction are written back to the physical database on disk, and the transaction ends normally.

Rollback represents a rollback, in which a transaction fails to proceed while the transaction is running, and the system undoes all completed operations on the database in the transaction, rolling back to the state in which the transaction started.

3, the classification of business:

Commit transactions automatically: Each individual statement is a transaction. A commit is implied after each statement. Default

Explicit transactions: Begins with the BEGIN transaction display, ending with commit or rollback.

Implicit transactions: When a connection operates in an implicit transaction mode, the SQL Server database Engine instance automatically starts a new transaction after committing or rolling back the current transaction. No need to describe the beginning of a thing, just commit or roll back each transaction. However, each transaction is still explicitly ended with commit or rollback. After the connection sets the implicit transaction mode to open, an implicit transaction is 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 The transaction will remain in effect until the commit or ROLLBACK statement is issued. After the first transaction is committed or rolled back, the next time the connection executes any of these statements, the database Engine instance will automatically start a new transaction. The instance will continue to generate the implicit transaction chain until the implicit transaction mode is closed.

Second, Java JDBC transaction mechanism:

First, let's look at some of the major problems that existing JDBC operations can bring to us, such as having a business: when we modify a message and then query the information, it is a simple business, it is very easy to implement, but when the business is placed in a multi-threaded high-concurrency platform, the problem naturally arises, For example, when we execute a modification, there is a thread that executes the modification statement before executing the query, which we can then execute the query, the information we see may be different from our modification, in order to solve this problem, we must introduce the JDBC transaction mechanism, in fact, the code implementation is very simple, Give an example of the principle of implementation for your reference:

PrivateConnection conn =NULL; PrivatePreparedStatement PS =NULL; Try{conn.setautocommit (false);//Set auto-commit to FalsePs.executeupdate ("Modify SQL");//To perform a modification operationPs.executequery ("Querying SQL");//perform a query operationConn.commit ();//manually submit after two successful operations}Catch(Exception e) {conn.rollback (); //once one of the operations fails, the two operations are not successfulE.printstacktrace (); }

Third, the theory related to business:
1. Four properties of the transaction (Transaction) (ACID)
Atomicity (Atomic) changes to the data are either all executed or not executed at all.
Consistency (consistent) the data state remains consistent before and after the transaction executes.
Isolation (Isolated) processing of one transaction cannot affect the processing of another transaction.
The persistence (durable) transaction ends and its effect is persisted in the database.

2. Issues that may arise from transaction concurrency processing

Dirty Read (dirty Read) One transaction reads data that has not yet been committed by another transaction.

Non-repeatable read (non-repeatable Read) operation of one transaction causes another transaction to read to different data two times before and after

Phantom Read (Phantom Read) The operation of one transaction results in a different amount of result data for two queries before and after another transaction.

Example: Transaction A, b concurrent execution:

When a transaction is updated, the B transaction Select reads a data that has not yet been committed, at which point a transaction rollback, the data read by B is invalid "dirty" data.

When the B transaction select reads the data, the a transaction update operation changes the data for the B transaction Select, at which point the B transaction reads the data again and finds that the data is different two times before.

When the B transaction select reads the data, a transaction inserts or deletes a record that satisfies the select condition of a transaction, at which point the B transaction again selects, finds the query to the previous nonexistent record ("phantom"), or a previous record is missing.

Iv. JDBC's transactional support:

The JDBC support for transactions is in three ways:

1. Auto-commit mode (Auto-commit modes):

Connection provides a Auto-commit property to specify when the transaction ends.

(1) When Auto-commit is true, transactions are committed automatically when each individual SQL operation completes, meaning that each SQL operation is a transaction.

When an independent SQL operation is performed, the JDBC specification specifies that:

For data manipulation languages (DML, such as insert,update,delete) and data definition languages (such as Create,drop), statements are considered complete when they are executed.

For a SELECT statement, when the ResultSet object associated with it is closed, the execution is considered complete.

For a stored procedure or other statement that returns multiple results when all the ResultSet objects associated with it are closed, all update count (the number of rows affected by a statement operation such as Update,delete) and the output parameter (the stored procedure outputs parameters) is considered executed after it has been acquired.

(2) When Auto-commit is false, each transaction must appear to be committed by calling the Commit method, or the call rollback method will be displayed for rollback. Auto-commit default is True.

JDBC provides 5 different levels of transaction isolation that are defined in connection.

2. Transaction ISOLATION LEVEL (Transaction isolation levels)
JDBC defines five levels of transaction isolation:
Transaction_none JDBC driver does not support transactions
Transaction_read_uncommitted allows dirty reads, non-repeatable reads, and Phantom reads.
transaction_read_committed suppresses dirty reads, but allows non-repeatable reads and Phantom reads.
Transaction_repeatable_read prohibit dirty read and non-repeatable read, single run Phantom read.
Transaction_serializable prohibit dirty reads, non-repeatable reads, and Phantom reads.

3, Save point (SavePoint)

JDBC defines the SavePoint interface, which provides a finer-grained transaction control mechanism. When a savepoint is set, you can rollback to the state at that SavePoint instead of rollback the entire transaction.

The Setsavepoint and Releasesavepoint methods of the connection interface allow you to set and release savepoint points.

Although the JDBC specification defines the above support behavior for transactions, each JDBC driver may have a different degree of support for the transaction by the database vendor. If you set any of these in your program, you may not get the effect you want. To do this, JDBC provides a DatabaseMetaData interface that provides a collection of JDBC feature support scenarios. Like what The support of the transaction isolation level can be judged by the Databasemetadata.supportstransactionisolationlevel method, and the support of the savepoint can be judged by the Databasemetadata.supportssavepoints method. Case

V. Use of the transaction:

When the JDBC program obtains a connection object to the database, by default the Connection object submits the SQL statement sent on it to the database automatically. If you want to turn off this default submission, let multiple SQL execute in a single transaction, and ensure that the statements are executed together at the same time, we should define a transaction for these multiple statements.

Among them, the case of bank transfer, the most can explain, the importance of the use of business.

 from set money=money-where name=fromset money=money+100 wherename=' B '; // as a result, changes in the two accounts are performed together. There are many examples of this kind of synchronous communication in real life, here, not to repeat. 

Of course, for the writing of transactions, it is also to follow a certain sequence:

First, set the transaction to be submitted in a non-autocommit manner: Conn.setautocommit (false);

Next, put the code that needs to add the transaction into the try catch block.

Then, the commit operation of the transaction is added inside the try block, indicating that the operation has no exception and commits the transaction. Conn.commit ();

In particular, do not forget to add a ROLLBACK transaction within the CATCH block, indicating an exception to the operation, revoking the transaction: Conn.rollback ();

Finally, set the transaction submission method to Auto-commit: Conn.setautocommit (TRUE);

So, with a few simple steps, we can finish writing the transaction.

//Example: Defines a transaction method and implements a consistency operation between statements within a methodConnection con =NULL; Statement St=NULL; ResultSet RS=NULL; PreparedStatement PS=NULL; Public voidstarttransaction () {con= Dbcmanager.getconnect ();//Get Connection Object    Try {        //to set a transaction to be submitted as non-auto-commit:Con.setautocommit (false); //put the code that needs to add the transaction together into the Try,catch block//To create an execution statementString sql ="delete from me where id = 7"; String SQL1="update me set name = ' Chengong ', age = ' A ' where ID =4"; //Perform transactions separatelyPS =con.preparestatement (SQL);        Ps.executeupdate (); PS=con.preparestatement (SQL1);        Ps.executeupdate (); //The commit operation of the transaction is added inside the try block, indicating that the operation has no exception and commits the transaction. Con.commit (); } Catch(SQLException e) {Try {          //To add a ROLLBACK transaction within a catch block, indicating an exception to the operation and revoking the transaction:Con.rollback (); } Catch(SQLException E1) {e1.printstacktrace ();    } e.printstacktrace (); }finally{        Try {            //to set the transaction submission method to Auto-commit:Con.setautocommit (true); } Catch(SQLException e) {e.printstacktrace ();    } dbcmanager.release (RS, ps, con); }}

A detailed explanation of JDBC transactions in Java