A preliminary study on transaction isolation

========== The following article is excerpted from http://www.blogjava.net/agapple/archive/2009/02/15/254801.html ==========

Four properties of the transaction: atomicity (atomicity), consistency (consistency), isolation (isolation), and persistence (durability).

1. atomicity (Atomic)
The most important principle is also the easiest to understand. All methods that are managed by the transaction are either committed together or rolled back together.

2. Consistency (consistency)
Transactions are implemented consistently in system integrity, and if transactions are successfully completed, all changes in the system are applied correctly and the system is in a new and valid state. If an error occurs in a transaction, all changes in the system are automatically rolled back and the system returns to its original state.

3. Isolation (Isolation)
Perform transactions in the quarantine state so that they appear to be the only actions that the system performs within a given time. If there are two transactions running at the same time and performing the same function, the isolation of the transaction will ensure that each transaction is considered only in the system by the transaction. This attribute is sometimes referred to as serialization, in order to prevent confusion between transaction operations, the request must be serialized or serializable so that only one request at a time is used for the same data. Importantly, the state of the system may be inconsistent in the isolated state, ensuring that the system is in a consistent state before closing the transaction. However, in each individual transaction, the state of the system may change. If a transaction is not running in isolation, it may access data from the system, and the system may be in an inconsistent state. By providing transaction isolation, you can prevent such events from occurring.

4. Durability (Durability)
Persistence means that any changes that occur in the system will be permanent once the transaction is executed successfully. There should be some checkpoints to prevent loss of information when the system fails. Even if the hardware itself fails, the state of the system can still be rebuilt by recording the tasks completed in the log.

Database Lock Concepts:

There are two basic types of locks in the database: exclusive locks (Exclusive Locks, i.e., x locks) and shared locks (Share Locks, or S locks). When a data object is added to the exclusive lock, no other transaction can read and modify it. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic lock types to concurrently control the transactions of the database.

From a programmer's point of view, locks are grouped into the following two categories:

1. Optimistic lock (optimistic lock)
Optimistic locks assume that when working with data, you do not need to do anything in your application's code to lock the record directly, that is, relying entirely on the database to manage the lock. Typically, SQL Server locks the table that is updated in the transaction scope automatically when the transaction is performed.

2. Pessimistic lock (Pessimistic lock)
Pessimistic lock for the automatic management of database system not a cold, requires the programmer to directly manage data or object lock processing, and is responsible for acquiring, sharing and discarding any locks on the data being used.

Transaction ISOLATION LEVEL

The degree to which a transaction must be isolated from other transactions. Lower isolation levels can increase concurrency, but at the cost of reducing data correctness. Conversely, a higher isolation level ensures that data is correct, but can have a negative impact on concurrency.

Exception conditions for database concurrency operations:

1. Update loss (Lost update): Two transactions update a row of data at the same time but the second transaction failed to exit, resulting in two changes to the data are not valid this is the system does not perform any lock operation so concurrent transactions are not isolated

2. Dirty read (Dirty reads): A transaction begins to read a row of data but another transaction has updated the data but has not been able to submit it in time. It's quite dangerous, and it's possible that all operations are rolled back.

3. Non-repeatable reads (non-repeatable reads): One transaction reads the same row of data repeatedly two times but gets different results. For example, there is another transaction in the middle of two reads that the row data was modified and submitted

4. Two update issues (Second lost updates problem): Unable to read the special case repeatedly, two concurrent transactions read the same row of data and then one of them was modified to commit and the other was modified to submit this will cause the first write operation to fail

5. Phantom reading (Phantom reads): Also known as Phantom (Phantom). The transaction is queried two times during the operation, and the second query results in the data that did not appear in the first query (this does not require two queries for the same SQL statement) this is because another transaction was inserted into the data during the two query

Transaction ISOLATION LEVEL

To avoid several cases above, there are 4 transaction isolation levels defined in the standard SQL specification, and different isolation levels are different for transaction processing.

1. Unauthorized read (READ UNCOMMITTED): Also known as uncommitted. Allows dirty reads but does not allow updates to be lost, and if a transaction has begun to write data, another data does not allow simultaneous writes but allows other transactions to read the row data. The isolation level can be implemented through exclusive write locks. The lowest level of transaction isolation, which guarantees that no physical corrupted data is read. In contrast to the Read Committed isolation level, it allows reading of determined data that has been modified by another user but not yet committed.

2. Authorized read (Read Committed): Also known as submission read. Allow unreadable reads but not dirty reads. This can be achieved by "instantaneous shared read locks" and "exclusive write Locks", and transactions that read data allow other transactions to continue accessing the row's data, but uncommitted write transactions will prevent other transactions from accessing the row. The default level of SQL Server. Under this isolation level, the SELECT command does not return data that has not been committed (committed), nor does it return dirty data.

3. Repeatable reads (REPEATABLE READ): Non-repeatable reads and dirty reads are prohibited. However, phantom data can sometimes occur, implemented through shared read locks and exclusive write locks, and reading data transactions will prohibit write transactions (but allow read transactions), and write transactions that prohibit any other transaction. At this isolation level, data read with the Select command will not be changed during the entire command execution. This option affects the performance of the system, preferably not at this isolation level.

4. Serial (Serializable): Also known as Serializable read. Provides strict transaction isolation, which requires transaction serialization execution, which can only be performed one after the other, but not concurrently. If transaction serialization is not possible only through row-level locks, other mechanisms must be used to ensure that the newly inserted data is not accessed by the transaction that just executed the query operation. The highest level of transaction isolation, complete isolation between transactions. If a transaction runs at the Serializable isolation level, any concurrent overlapping transactions can be guaranteed to be serial.

Isolation level	Update missing	Dirty Read	Repeat Read	Phantom reading
Unauthorized Read	N	Y	Y	Y
Authorized Read	N	N	Y	Y
REPEATABLE READ	N	N	N	Y
Serial	N	N	N	N

============================================================================

Default transaction level for Oracle: READ committed

The transaction isolation level supported by Oracle:

SET TRANSACTION Isolation Level READ committed;

SET TRANSACTION Isolation Level SERIALIZABLE;

SET TRANSACTION Isolation level READ only;

It should be useless to ask for the isolation level set in Dba,java that Oracle does not support. I haven't confirmed that yet. Forget that someone confirmed to tell ~3ks.

A few database default isolation levels are repeatable Read, such as the MySQL InnoDB storage engine

Even at the lowest level, there is no first class loss update problem.

======================= the red part of the problem I've written the code to confirm ===============

See the following exception stack: Java code   exception in thread  "main"   Org.springframework.transaction.cannotcreatetransactionexception: could not open jdbc  connection for transaction; nested exception is java.sql.sqlexception:   Only  READ_COMMITTED  and  SERIALIZABLE  are valid transaction level        at  org.springframework.jdbc.datasource.datasourcetransactionmanager.dobegin ( datasourcetransactionmanager.java:238)        at  Org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction ( abstractplatformtransactionmanager.java:374)        at  Org.springframework.transaction.support.TransactionTemplate.execute (transactiontemplate.java:125)        at com.yajun.ibatis.transactiontest.main (transactiontest.java:19)    caused by: java.sql.sqlexception:  Only &nbsp read_committed  and  SERIALIZABLE  are effective transaction level        at  Oracle.jdbc.driver.SQLStateMapping.newSQLException (sqlstatemapping.java:70)         at oracle.jdbc.driver.databaseerror.newsqlexception (databaseerror.java:131)         at oracle.jdbc.driver.databaseerror.throwsqlexception (databaseerror.java:197)        at oracle.jdbc.driver.databaseerror.throwsqlexception (DatabaseError.java:261)        at oracle.jdbc.driver.databaseerror.throwsqlexception ( databaseerror.java:269)        at  Oracle.jdbc.driver.DatabaseError.throwSqlException (databaseerror.java:490)         At oracle.jdbc.driver.physicalconnection.settransactionisolation (physicalconnection.java:4070)         at org.apache.commons.dbcp.delegatingconneCtion.settransactionisolation (delegatingconnection.java:380)        at  Org.apache.commons.dbcp.poolingdatasource$poolguardconnectionwrapper.settransactionisolation ( poolingdatasource.java:343)        at  Org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction (datasourceutils.java:174)        at  Org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin (datasourcetransactionmanager.java:210 )        ... 3 more  

Exception in thread ' main ' org.springframework.transaction.CannotCreateTransactionException:Could not open JDBC Connection for transaction; Nested exception is java.sql.SQLException: Only read_committed and SERIALIZABLE are valid transaction levels at Org.springframework.jdbc.datasou Rce. Datasourcetransactionmanager.dobegin (datasourcetransactionmanager.java:238) at Org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction ( abstractplatformtransactionmanager.java:374) at Org.springframework.transaction.support.TransactionTemplate.execute (transactiontemplate.java:125) at Com.yajun.ibatis.TransactionTest.main (transactiontest.java:19) caused by:java.sql.SQLException: only read_committed and SERIALIZABLE is a valid transaction level at Oracle.jdbc.driver.SQLStateMapping.newSQLException (sqlstatemapping.java:70) at Oracle.jdbc.driver.DatabaseError.newSQLException (databaseerror.java:131) at Oracle.jdbc.driver.DatabaseError.throwSqlException (databaseerror.java:197) at oracle.jdbc.driver.databaseerror.tHrowsqlexception (databaseerror.java:261) at Oracle.jdbc.driver.DatabaseError.throwSqlException ( databaseerror.java:269) at Oracle.jdbc.driver.DatabaseError.throwSqlException (databaseerror.java:490) at Oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation (physicalconnection.java:4070) at Org.apache.commons.dbcp.DelegatingConnection.setTransactionIsolation (delegatingconnection.java:380) at Org.apache.commons.dbcp.poolingdatasource$poolguardconnectionwrapper.settransactionisolation ( poolingdatasource.java:343) at
	Org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction (datasourceutils.java:174) At Org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin (Datasourcetransactionmanager.java : 210) ...
 3 more

Spring sets the database transaction level that Oracle does not support to report an exception.