Database Transaction Fundamentals

I. Overview1. Concept of transactions

In a database, a transaction is a series of actions performed by a set of logical units of work, either executed or not executed.

2. Transaction acid characteristicsAtomicity (Atomic) atomicity means that multiple database operations that make up a transaction are an indivisible atomic unit, and the operations in the transaction either occur or do not occur.
The consistency (consistency) transaction must transform the database from one consistent state to another (data is not corrupted). If you transfer from a account 100 to a B account, regardless of the success of the operation, A and B of the total deposit is unchanged.
Isolation (isolation) in concurrent data operations, different transactions have their own data space, and their operations do not interfere with each other. To be precise, it is not required to be completely non-intrusive, the database specifies a variety of transaction isolation levels, different isolation levels correspond to different degrees of interference, the higher the isolation level, the better the data consistency, but the weaker the concurrency.
Persistence (Durabiliy)

Once a transaction is committed, its changes to the data in the database are permanent, and subsequent operations and database failures should not have any effect on it.

in these transactional features, data "consistency" is the ultimate goal, and other features are measures, requirements, or means to achieve this goal.

The database management system uses logs to ensure the atomicity, consistency and persistence of transactions. The log records the updates that the transaction makes to the database, and if a transaction has an error during execution, it can undo the updates that the transaction has made to the database based on the log, returning the database to the initial state before the transaction was executed. In addition, for transactions that have already been committed, even if the database crashes, the data that has not been persisted can be re-executed according to the log when the database is restarted.

The database management system adopts the lock mechanism to realize the isolation of the transaction. When multiple transactions update the same data in the database at the same time, only transactions that hold the lock are allowed to update the data, and other transactions must wait until the previous transaction releases the lock, and other transactions have the opportunity to update the data. The Oracle database also uses the mechanism of the data version, where the rollback segment holds a version for each change in the data, so that changes to the data do not affect the reading of the data.

Second, the problem of data concurrency

a database may have multiple access clients that can access the database concurrently. The same data in the database may be accessed by multiple transactions at the same time, and if the necessary isolation measures are not taken, it can cause various concurrency problems and destroy the integrity of the data. These problems can be attributed to Class 5, which includes 3 types of data-read problems (dirty reads, non-repeatable reads, and Phantom reads), and 2 types of data update issues (first-class missing updates and second-class missing updates). Below, we explain the scenario that caused the problem separately by example.

Dirty Reads (Dirty read)

A transaction reads the change data that the B transaction has not yet committed and operates on the basis of this data. If the B transaction happens to be rolled back, then the data read by a transaction is not recognized at all. Look at the dirty read scenario that is raised when the withdrawal transaction and the transfer transaction are concurrent:

In this scenario, B wants to withdraw 500 yuan and then cancel the action, and a to the same account to transfer 100 yuan, because a transaction read the B transaction has not yet submitted data, resulting in the account lost 500 yuan. In an Oracle database, dirty reads do not occur.

Non-repeatable read (unrepeatable read) non-repeatable read refers to a transaction that reads the change data that the B transaction has committed. Suppose a in the process of withdrawal transaction, B transfers to the account 100 yuan, a two read the balance of the account is inconsistent:

In the same transaction, the T4 time Point and the T7 point-in-time read Account deposit balance are not the same.

Phantom Read (Phantom Read)A transaction reads the new data submitted by the B transaction, at which point a transaction will have phantom read. Phantom reading generally occurs in the transaction of computing statistics, for example, assuming that the banking system in the same transaction, the total amount of two statistics deposit accounts, in two statistics process, just add a deposit account, and deposited 100 yuan, then two times the total amount of statistics will be inconsistent:

If the new data just satisfies the query criteria of the transaction, the data enters the field of view, resulting in two statistical inconsistencies.

Phantom Read and non-repeatable reading are two confusing concepts, the former refers to other new data that has been committed to the transaction, while the latter refers to the change data (changes or deletions) that have been committed to the transaction, in order to avoid these two situations, the countermeasures are different to prevent reading to the change data, Simply adding row-level locks to the operational data, blocking data changes in the operation, and preventing reading to new data often requires adding table-level locks-locking the entire table to prevent new data (implemented by Oracle using multi-version data).

first category missing updatesWhen a transaction is revoked, the updated data of the already committed B transaction is overwritten. This error may cause serious problems, as can be seen through the following account withdrawal transfer:

When a transaction is withdrawn, "careless" erases the amount of the B transaction that has been transferred to the account.

Category two missing updates

A transaction overwrites the data already submitted by the B transaction, causing the operation of the B office to be lost:

in the above example, because the cheque transfer transaction covers the withdrawal transaction to the deposit balance of the update, resulting in the bank lost 100 yuan, on the contrary, if the transaction is first submitted, then the user account will lose 100 yuan.

third, the database lock mechanism

data concurrency can cause a lot of problems, and in some cases some issues are allowed, but in other cases it can be deadly. The database solves the problem of concurrent access through locking mechanism, although different databases differ in implementation details, but the principle is basically the same.

depending on the object being locked, it can be divided into table lock and row lock, which locks the whole table and the latter locks the specific row in the table. From a relationship locked by a concurrent transaction, it can be divided into shared locks and exclusive locks. A shared lock prevents exclusive locking, but allows other shared locks. Exclusive locking prevents other exclusive locks as well as other shared locks. In order to change the data, the database must impose row exclusive locks on the rows that make the change, and the INSERT, update, delete, and select for UPDATE statements implicitly take the necessary row locks. Let's look at 5 types of locks commonly used in Oracle databases.

Row sharing lock

in general, row-sharing locks are implicitly obtained through the Select FOR UPDATE statement, and in Oracle users can explicitly obtain row-sharing locks through the lock TABLE in row SHARE mode statement. Row sharing locks do not prevent changes to data rows, but they can prevent other sessions from obtaining exclusive data table locks. Multiple concurrent row and row exclusive locks are allowed, and data table sharing is allowed or shared row exclusive locks are used.

Row Exclusive Lock

Obtained implicitly through an INSERT, update, or DELETE statement, or explicitly through a lock TABLE in ROW EXCLUSIVE mode statement. This lock prevents other sessions from acquiring a shared lock, a shared row exclusive lock, or an exclusive lock.

Table sharing lockExplicitly obtained through the lock TABLE in SHARE mode statement. This lock prevents other sessions from acquiring row exclusive locks (INSERT, update, or delete), or prevents other tables from sharing row exclusive locks or table exclusive locks, which allow multiple row and table share locks in the table. This lock allows the session to have transactional-level access to the table, because other sessions cannot change the locked table until the user commits or backtracking the transaction and frees the lock on the table.Table Shared Row Exclusive

Explicitly obtained through the lock TABLE in SHARE ROW EXCLUSIVE mode statement. This lock prevents other sessions from acquiring a table share, row exclusive, or table exclusive lock, which allows other rows to share locks. This lock is similar to a table-sharing lock, except that only one table can be placed on one table at a time for exclusive locking of shared rows. If the a session has the lock, the B session can perform a select for update operation, but if the B session attempts to update the selected row, it waits.

Table Exclusive

Explicitly obtained through lock TABLE in EXCLUSIVE mode. This lock prevents other sessions from any other locks on the table.

Optimistic lock and pessimistic lock

The so-called pessimistic lock is based on the implementation of database mechanism. For example, when you add a for update with the SELECT clause, no application can modify the record of a select until the end of the transaction to which the clause was modified.

The so-called optimistic lock is implemented based on the version mechanism of the application. It is common to design a version of the field in the table (I usually set this field to long or timestamp). The general update scenario is this:

The first step:

Select a, version from TB where id=1;

Suppose to get the data is: [' xxx ', 100]

Step Two:

Update TB set a= ' yyy ', version=systimestamp where v=100; Note that version is generally not modified at the time of business operations

This requires that each update operation change the version field, or whether the data between processes will be overwritten with each other.

Optimistic locks cannot lock other apps ' operations on the data. The optimistic locking mechanism avoids the database lock-up overhead in long transactions (two transactions, transaction A and transaction B are not locking to the database data), and greatly improves the overall performance of the system under large concurrency. It should be noted that the optimistic locking mechanism is often based on the data storage logic in the system, so there are some limitations, because the optimistic locking mechanism is generally implemented in our system, the user balance update from the external system is not controlled by our system, it may cause dirty data to be updated into the database. In the system design phase, we should take full account of the possibility of these situations, and make appropriate adjustments (such as the optimistic locking policy implemented in the database stored procedures, external only open the data update path based on this stored procedure, rather than the database table directly to the public).

Iv. Transaction ISOLATION LEVEL

Although the database provides a way for users to lock DML operations, it is cumbersome to use lock management directly, so the database provides an automatic lock mechanism for users. As long as the user specifies the transaction isolation level for the session, the database parses the SQL statement in the transaction and then automatically adds the appropriate lock for the transactional operation's data resource. In addition, the database maintains these locks, and when the number of locks on a resource is too high, automatic lock escalation is performed to improve the performance of the system, which is completely transparent to the user.

The Ansi/iso SQL 92 standard defines a 4-level transaction isolation level that, in the same data environment, uses the same input to perform the same work, depending on the isolation level, which can result in different results. The ability to resolve data concurrency problems at different transaction isolation levels is different, as shown in the following table.

The isolation level of a transaction is opposed to the concurrency of the database, and it grows. In general, databases with the Read Uncommited isolation level have the highest concurrency and throughput, while database concurrency with the SERIALIZABLE isolation level is minimal.

SQL 92 defines read uncommited primarily to provide non-blocking read capability, although Oracle also supports read uncommited, but it does not support dirty reads, Oracle's Read Committed isolation level has met the SQL 92 standard REPEATABLE READ isolation level because Oracle uses a multi-version mechanism to completely resolve the problem of reading dirty data while non-blocking reads and ensuring read consistency.

SQL 92 recommends using repeatable read to ensure data read consistency, but users can choose the appropriate isolation level based on the needs of the application.

V. JDBC Support for transactions

Not all databases support transactions, and even if the database that supports the transaction does not support all transaction isolation levels, the user can obtain the DatabaseMetaData object through the Connection#getmetadata () method. The Supportstransactions (), supportstransactionisolationlevel (int level) method of the object is used to view the transaction support of the underlying database.

Connection is automatically committed by default, that is, each executed SQL corresponds to a transaction, in order to be able to execute multiple SQL as a transaction, you must first pass Connection#setautocommit (false) Prevents connection from automatically committing and sets the isolation level of the transaction through Connection#settransactionisolation (), which defines constants for the corresponding SQL 92 standard 4 transaction isolation levels in connection. A transaction is committed through Connection#commit (), and the transaction is rolled back through Connection#rollback (). Here is the code for the typical JDBC transactional data operation:

Connection Conn; try{     conn = Drivermanager.getconnection ();//① Get data connection     Conn.setautocommit (false);// ② turn off autocommit mechanism     conn.settransactionisolation (connection.transaction_serializable);//③ SET TRANSACTION isolation level     Statement stmt = Conn.createstatement ();          int rows = Stmt.executeupdate ("INSERT into T_topic VALUES (1, ' Tom ')");     rows = Stmt.executeupdate ("UPDATE t_user set topic_nums = topic_nums +1" + "WHERE user_id = 1");           Conn.commit ();//④ Commit Transaction}catch (Exception e) {     ...     Conn.rollback ();//⑤ rollback TRANSACTION}finally{...   }

In JDBC 2.0, a transaction can end up with only two actions: Commit and rollback. However, some applications may need more control over transactions than simply committing or rolling them back. JDBC 3.0 (JDK 1.4 and later) introduces a new SavePoint feature, which allows the user to split a transaction into multiple stages, which the user can specify to roll back to a specific savepoint of the transaction instead of just rolling back to the point where the transaction started, as in JDBC 2.0.

The following code uses the function of a savepoint to roll back to the specified savepoint, rather than rolling back the entire transaction, when a specific problem occurs, as shown in the following code:

... Statement stmt = Conn.createstatement (); int rows = Stmt.executeupdate ("INSERT into T_topic VALUES (1, ' Tom ')"); SavePoint svpt = Conn.setsavepoint ("SavePoint1");//① set a save point rows = Stmt.executeupdate ("UPDATE t_user set topic_nums = Top Ic_nums +1 "+          " WHERE user_id = 1 "); //② rolls back to the SQL operation before Savepoint1,① in ①, still commits after the entire transaction commits,//But the SQL operation between ① to ② is revoked Conn.rollback (SVPT); ... conn.commit ();//③ COMMIT Transaction

Not all databases support the Save Point feature, and users can see whether support is available through the databasemetadata#supportssavepoints () method.

Spring transaction mechanism in detail see: http://www.open-open.com/lib/view/open1350865116821.html

A large part of the text is excerpted from: http://stamen.iteye.com/blog/1541720

Database Transaction Fundamentals