A detailed explanation of JDBC transactions in Java

Features 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.

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 usually begins with a BEGIN transaction and ends with a 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.

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.

Java JDBC Transaction mechanism

First, let's take a look at what the existing JDBC operation will do to us, such as having a business: when we modify a message and then query this 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:

PRIVATE Connection conn = null;

Private PreparedStatement PS = null;

try {

Conn.setautocommit (FALSE); Set auto-commit to False

Ps.executeupdate ("Modify SQL"); To perform a modification operation

Ps.executequery ("Query SQL"); Perform a query operation

Conn.commit (); Manually submit after two successful operations

} catch (Exception e) {

Conn.rollback (); Once one of the operations fails, the two operations are not successful

E.printstacktrace ();

}

Theory related to transactions
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:
When transactions A and B are executed concurrently,
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.

Transactional support for JDBC
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.
A. 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.
B. 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

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/lhfqq/archive/2010/01/31/5274775.aspx

A detailed explanation of JDBC transactions in Java