Transaction and JDBC Transaction isolation levels

Theory related to transactions

MySQL thing isolation level: http://mj4d.iteye.com/blog/1744276

Transactions (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.

1, the characteristics of the transaction (ACID):

1) atomicity (Atomic): 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.

Transactions must be isolated from one another, preventing concurrent reads and writes of the same data.

4) Persistence (durable): 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.

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.

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.

Types of Java transactions

There are three types of Java transactions: JDBC Transaction, JTA (Java Transaction API) transaction, container transaction.

1. JDBC Transaction

The JDBC transaction is controlled with the Connection object. The JDBC Connection Interface (java.sql.Connection) provides two transaction modes: Autocommit and Manual commit. Java.sql.Connection provides the following methods of controlling transactions:

public void Setautocommit (Boolean)
public boolean getautocommit ()
public void commit ()
public void rollback ()

When using JDBC transaction demarcation, you can combine multiple SQL statements into a single transaction. One drawback of JDBC transactions is that the scope of a transaction is limited to a single database connection. A JDBC transaction cannot span multiple databases.

2. JTA (Java Transaction API) transaction

JTA is a high-level, unrelated, protocol-agnostic API that enables applications and application servers to access transactions using JTA.

JTA allows applications to perform distributed transactions-access and update data on two or more network computer resources, which can be distributed across multiple databases. The JTA support of the JDBC driver greatly enhances the data access capability.

If you plan to define transactions with JTA, you need a JDBC driver that implements the Javax.sql.XADataSource, Javax.sql.XAConnection, and Javax.sql.XAResource interfaces. A driver that implements these interfaces will be able to participate in the JTA transaction. A Xadatasource object is a factory of a Xaconnection object. Xaconnection S is a JDBC connection that participates in JTA transactions.

You will need to set up Xadatasource with the application server's administration tools. You can learn about the guidance from the application server and the JDBC driver documentation.

The Java EE application queries the data source with JNDI. Once the application finds the data source object, it calls Javax.sql.DataSource.getConnection () to get a connection to the database.

XA connections are not the same as non-XA connections. Be sure to remember that the XA connection participates in the JTA transaction. This means that the XA connection does not support the auto-commit feature of JDBC. Also, the application must not call Java.sql.Connection.commit () or Java.sql.Connection.rollback () on the XA connection.

Instead, applications should use Usertransaction.begin (), Usertransaction.commit (), and Sertransaction.rollback ().

3. Container Service

Container transactions are mainly provided by the Java EE Application Server, and the container transactions are mostly based on JTA, which is a jndi-based, fairly complex API implementation. Relative coding implements JTA transaction management, and we can accomplish the same function through the container transaction management mechanism (CMT) provided by the EJB container, which is provided by the Java EE Application Server. This allows us to simply specify which method to join the transaction, and once specified, the container will be responsible for the transaction management task. This is our way of doing civil work, because in this way we can exclude the transaction code from the logic code, and all the difficulties to the Java EE container to solve. Another benefit of using EJB CMT is that programmers do not have to care about the coding of the JTA API, but in theory we have to use EJBS.

Four or three types of Java transaction differences

1. The limitations of JDBC transaction control are within a database connection, but they are simple to use.

2. JTA transactions are powerful, and transactions can span multiple databases or multiple DAO, and are more complex to use.

3, container transaction, mainly refers to the Java EE Application Server provides transaction management, limited to the use of EJB applications.

V. Summary

Java transaction control is an indispensable part of building the EE application, and it is very important for the whole application to choose what kind of transaction to apply. in general, the JDBC transaction can be selected in the case of a single JDBC connection connection, with the option to use JTA transactions across multiple connections or databases, and if EJB is used, consider using EJB container transactions

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 ();

}

The JDBC support for transactions is in three ways:

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

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.

The higher the transaction isolation level, the more effort is spent to avoid conflicts. The connection interface defines level five, where the lowest level specifies that transactions are not supported at all, and the highest level specifies that any other transaction must not make any changes to the data that the transaction is reading while the transaction is operating on a database . Typically, the higher the isolation level, the slower the application executes (due to increased resource consumption for locking, and fewer concurrent operations between users). When deciding what isolation level to adopt, the developer must weigh the performance requirements against the data consistency requirements.

When you create a connection object, its transaction isolation level depends on the driver, but it is usually the default value of the database involved. The user can change the transaction isolation level by calling the Setisolationlevel method. The new level will take effect for the remaining time of the connection process. To change only the transaction isolation level of a transaction, you must set it before the transaction begins and reset it after the transaction has ended. We do not advocate making changes to the transaction isolation level in the middle of a transaction, as this will immediately trigger the invocation of the commit method, making any changes made before this become permanent.

data isolation level settings for JDBC:

To resolve issues related to the request for the same data by multiple threads, the transactions are separated from each other by locks.         Most mainstream databases support different types of locks; therefore, the JDBC API supports different types of transactions, which are assigned or determined by the Connection object. In order to strike a balance between performance and consistency, there are several levels above.        The higher the level of transaction protection, the greater the performance penalty.        Assuming that your database and the JDBC driver support this feature, given a Connection object, you can explicitly set the desired transaction level: Conn.settransactionlevel (transaction_serializable); The level of the current transaction can be determined by the following methods:
int level = Conn.gettransactionisolation ();

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

Source: http://blog.csdn.net/yuejingjiahong/article/details/6663577

http://blog.csdn.net/applehoney/article/details/2270732

http://lvwenwen.iteye.com/blog/2045951

Transaction and JDBC Transaction isolation levels