Database transaction characteristics and isolation Levels

Transactions

A set of business operations, either all successful or all unsuccessful.

characteristics of----------------transactions--------------

atomicity : A transaction is a whole, indivisible, and the operations in the transaction either succeed or fail.

consistency : A transaction must change the database from one consistent state to another. The state of the database is consistent before and after the transaction executes.

For example: A has 1000, Yuan, B has 1000 yuan, add up is 2000, the middle of the operation of the transfer, two people after the transfer of money added up or 2000.

isolation : two or more transactional concurrency actions can affect each other. The database provides isolation levels to eliminate these effects.

Persistence : Once a transaction commits, the impact on the data in the database is permanent, and if the database fails or no other action should affect it.

The above is the four characteristics of the transaction abbreviation ACDI, the focus is the isolation of the transaction, in the operation of the database, open multiple transactions at the same time to operate, in order to be able to accurately obtain data, the database should have isolation. If there is no isolation, there are several isolation issues:

----------------Isolation Issues----------------

Dirty reads : one transaction reads uncommitted data from another transaction

non-repeatable read : one transaction reads data that has been committed by another transaction (mainly for update)

Phantom read/virtual read : one transaction reads data that has been committed by another transaction (mainly for insert)

----------------Isolation Level----------------

To avoid these problems, the database provides four isolation levels (level from low to high, efficiency from high to low)

Read uncommitted: reading uncommitted, lowest level, dirty read, non-repeatable read, Phantom read three questions

Read committed: reads committed, only data that has been committed by other transactions can be read in the current transaction. Avoid the problem of dirty reading.

repeatableread: repeatable reading avoids dirty reads and non-repeatable reads.

serializable: serialization, avoid dirty reading, non-repeatable reading, the occurrence of phantom reading. The equivalent of locking the transaction, the efficiency will be relatively low.

In the MySQL database, the above four transaction isolation levels are supported, and the default transaction isolation level is repeatable read;

In the Oracle database, read committed, serializable two transaction isolation levels are supported, the default isolation level is read Committed;

----------------JDBC for database transaction management----------------

Use JDBC to manage database transactions: Setting the transaction isolation level before opening a transaction

Part of the code:

savepoint (save point): records the current position of the operation, which can then be rolled back to the specified location

Database transaction characteristics and isolation Levels