Persistence Layer Database Transaction foundation-the isolation level of the transaction

Content Guide:

The first and last database transactions, the risks associated with concurrency, and database locks are all designed to cushion the isolation of transactions.

The isolation of the transaction does not exist for no reason, his existence is to solve a certain kind of problem, bring some operation convenient; the problem is that the data consistency guarantee in the database concurrency operation, the convenience is that after defining the isolation level, The database is automatically locking for operations (different isolation levels have different auto-lock granularity) without manual locking for each operation.

Writing and writing that there is nothing to write about, this article has become a simple note

I. Database Transactions

Consider a set of database operations as an execution unit with special database semantics that has acid transaction properties. In the ACID properties of database transactions, atomicity, isolation, and persistence are all designed to ensure data consistency. The database semantics for acid are as follows.

1. Atomic (atomicity)

All operations in the execution unit are either successfully executed or not executed at all.

2. Consistency (consistency)

The status of execution unit execution is consistent before and after completion. An example of an account transfer.

Pre-Transfer status: A account 1000, B account 1000, the state before the transfer is a+b=2000 block;

Execution Unit operation: A to B transfer 200 pieces;

After the transfer status: A account 800, B account 1200, the state after the transfer is a+b=2000 block;

Conclusion: Both before and after the transfer of the same status, are 2000; there is no state inconsistency resulting in the loss or benefit of account A, account B or any party in the bank.

3. Isolation (Isolation)isolation describes how the database treats concurrent operations, that is, the degree to which different transactions affect each other in the context of a concurrent operational database. Depending on the user's need for a data-consistent environment, the database supports different isolation levels. It is important to note that it is meaningful to discuss isolation in a database concurrency environment, in essence the isolation level of the database is synonymous with automatic transactions . More details, discussed in more detail below. 4. Durability (persistent)

Persistence describes that when a transaction is successfully committed, the commit data is persisted to the database, even if the database crashes immediately, and can be resumed at restart time. Persistence is based on the operation log, in short, is the record database operation statement, a database statement contains the operation and data of the database, so long as the log is not damaged, even if the database storage media corruption, can also be restored through the log.

ii. risks associated with concurrency

The concurrent operations database can lead to some risks that could be categorized as three-class read problems and two types of update issues.

1. Dirty reading

One transaction reads to another transaction uncommitted update. As shown, transaction a reads uncommitted operations in transaction B, resulting in inconsistent data conditions.

2. Non-repeatable degree

One transaction reads the update that is committed byanother transaction. The difference between a dirty read is whether the read update transaction has been committed.

Reading the update submitted by another transaction is harmless in most cases, but this is problematic if the report is made at the end of the month. You need to count the data for one months, but there are constant update in the statistics process, even if you open the transaction at an accurate point in time, but the statistical data is still inaccurate. The solution is to lock the database, but if you think it would be cumbersome to repeat the same action every time, you can set the isolation level and let the database lock you up automatically.

3. Virtual reading

Inserts that are read in one transaction to another transaction commit. The difference between non-repeatable reads is that one reads the commit update and one reads the insert that was submitted.

To borrow an example of a non-repeatable read at the end of the month, read the submitted update, which means that the record exists in the database, which modifies the field, while reading the insert means that the record does not exist in the database. Although the time condition of where is added to the SQL of statistics, it is clear that the level of impact between transactions is not the same as that of previous records and nonexistent records.

4. First category missing updates

The rollback of one transaction overrides the update submitted by another transaction. The impact of such lost updates on the database is serious, unless the database does not support transactions, which must be prevented at any isolation level.

5. Category Two missing updates

One transaction commits an update that overwrites the update submitted by another transaction.

third, the database lock

Locks are exclusive locks and shared locks, placed in the database environment with row and table locks, and then fine-grained, row-sharing locks, row exclusive locks, table shared locks, table exclusive locks, table shared row exclusive locks. Database locks are similar to locks in programs in order to address risks in a concurrency environment-security, activity, and performance issues.

In fact, the problem of the database in the concurrency environment can be solved by lock-in, which is essentially the same, but a lot of lock operations are similar, there is no need to repeat the same action, so there is a transaction isolation level.

MySQL Lock explanation: Click to open the link

IV. Transaction ISOLATION Level1. The meaning of isolation

To solve the problems in database concurrency, like writing Java code, you need to lock to get the correct execution timing, so that every time you execute the operation of the SQL on the lock statement, for easy operation, so by setting the database isolation level, so that the database automatically lock.

2. Isolation's explanation

The Ansi/iso SQL 92 standard defines 4 levels of isolation levels, namely read_uncommited, read_commited, Repetable_read, SERIALIZABLE, four levels of isolation in Chengdu increments, and allows for decreasing levels of concurrency , where MySQL supports the third isolation level by default, and Oracle supports the second isolation level by default.

Some simple operations on the isolation level.

mysql> SELECT @ @tx_isolation;//view isolation level of current transaction +-----------------+| @ @tx_isolation  |+-----------------+| Repeatable-read |+-----------------+1 row in setmysql> SET TRANSACTION isolation Levels READ committed;//temporarily changes the database isolation level, Valid for this session only query OK, 0 rows affectedmysql> SELECT @ @tx_isolation;//View the isolation level that has been changed +----------------+| @ @tx_isolation |+----------------+| read-committed |+----------------+1 row in Set

Resolution of concurrency issues at the database isolation level.

v. JDBC Support for Transactions

In JDBC programming, all transaction-related operations are encapsulated into the connection object (by the Way,connection the implementation is encapsulated as a virtual reference saved in the driver Concurrenthashmap), the source extracts and comments are as follows.

/*** Com.mysql.jdbc.ConnectionImpl:mysql to Connection interface implementation Class */public interface Java.sql.Connection extends Wrapper, autocloseable {/** * A constant indicating that transactions is not supported. * There is no transaction condition and there is no isolation level */int Transa    Ction_none = 0;     /** * A constant indicating that * dirty reads, non-repeatable reads and phantom reads can occur. * This level allows a row changed by one transaction to is read * by another transaction before any changes on that RO  W has been * committed (a "dirty read"). If any of the changes is rolled back, * The second transaction'll has retrieved an invalid row.    * In addition to the first category of missing updates, the three-class read problems and the second class are all updates will occur */int transaction_read_uncommitted = 1; /** * A constant indicating that * dirty reads is prevented;  Non-repeatable reads and Phantom * reads can occur. This level is prohibits a transaction * from reading a row with uncommitted changes in it. * Prevent dirty reads and first class loss updates */int transaction_read_committed   = 2; /** * A constant indicating that * dirty reads and non-repeatable reads is prevented;  Phantom * reads can occur. Prohibits a transaction from * reading a row with uncommitted changes in it, and it also * prohibits th e situation where one transaction reads a row, * A second transaction alters the row, and the first transaction * Rereads the row, getting different values the second time * (a "non-repeatable read").    * Prevent dirty reads, non-repeatable reads and first class missing updates, second class missing updates, cannot prevent virtual reads */int transaction_repeatable_read = 4;     /** * A constant indicating that * dirty reads, non-repeatable reads and Phantom reads is prevented.  * This level includes the prohibitions in * <code>TRANSACTION_REPEATABLE_READ</code> and further prohibits  The * situation where one transaction reads all rows that satisfy * a <code>WHERE</code> condition, a Second transaction inserts a row that * satisfies that <code>where</Code> condition, and the first transaction * rereads for the same condition, retrieving the additional * "Phant Om "Row in the second read. * Prevent dirty Read, non-repeatable read, virtual read, first class, second class lost updates, the database is equivalent to serial processing transactions, the lowest concurrency */int transaction_serializable = 8;/** * is we in Autocommi T mode? * JDBC default is auto COMMIT TRANSACTION, each operation database is automatically transacted */private boolean autocommit = true;/*** Connection object supports transactional operations: * About Setautocommit, because JDBC defaults to autocommit transactions, That is, executing an SQL statement commits a transaction, so if you want to control the commit of the transaction, set autocommit to false; * Set Autocommit to false here, and do not interact with the database. In fact, automatic transactions that are set at the isolation level of the database are not affected, and this code only affects Jdbc*/void Setautocommit (Boolean autocommit) throws Sqlexception;void commit () throws Sqlexception;void rollback () throws sqlexception;void settransactionisolation (int level) throws Sqlexception;int Gettransactionisolation () throws SQLException; SavePoint Setsavepoint () throws SQLException; SavePoint setsavepoint (String name) throws Sqlexception;void rollback (SavePoint savepoint) throws SQLException;}

Note:

Write to the end already do not want to write, behind a lot of deep things slightly, sorry

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Persistence Layer Database Transaction foundation-the isolation level of the transaction