Database transaction isolation level, acid,spring transaction propagation

ACID, refers to the four characteristics of a transaction in a database management system (DBMS): atomicity (atomicity), consistency (consistency), isolation (isolation, also known as independence), persistence (durability).

In a database system, a transaction refers to a complete logical process consisting of a series of database operations. For example, bank transfer, deduction of the amount from the original account, and the addition of the amount to the target account, the sum of the two database operations constitutes a complete logical process that cannot be split. This process is called a transaction and has an acid characteristic.

Atomic nature:

All operations in the entire transaction, either complete or incomplete, are not likely to stall in one part of the middle. When an error occurs during execution, the transaction is rolled back (Rollback) to the state before the transaction begins, as if the transaction had never been executed.

For example, a transaction starts updating 100 rows of records, but after updating 20 rows (for some reason) it fails, the database rolls back (revokes) The changes to the 20 records.

Consistency:

The integrity constraints of the database are not compromised until the transaction begins and after the transaction has ended.

A transaction takes a database from one consistent state to another consistent state. For example, in a banking transaction (when describing the characteristics of a relational database transaction, basically using a bank transaction as a description object), you need to deduct the money from the storage account and then add the money to the payment account. If the process fails in this relay, then it is absolutely impossible to allow the database to execute only one of the accounts, because it will cause the data to be in an inconsistent state (so the bank's accounts are unbalanced).

If a database system fails, and some things are not yet completed, the system will undo all the completed operations on the database in this transaction and roll back to the consistent state at the beginning of the transaction.

Isolation:

The execution of two transactions is non-disruptive, and one transaction cannot see the data in the middle of a time when other transactions are running.

This feature means that until the end of the transaction (Commit/rollback), other transactions (or sessions) are not visible to the data that the transaction is operating on (but not to say that the read of other sessions is blocked). For example, if a user is modifying the Hr.employees table, but does not commit, the other user cannot see the change until the change is committed.

Durability:

After the transaction is completed, the changes made to the database by the firm persist in the database and are not rolled back.

This means that as long as the transaction completes successfully, the updates it makes to the database must be persisted. Even if a system crash occurs, the database can be restored to the state at the end of the transaction when the database system is restarted.

Committed changes are permanently saved to the database (not to mention that they cannot be modified later). After a transaction commits, the database must pass the recovery mechanism to ensure that the data for the transaction change is not lost.

Factors that may be damaged by the acid properties of the transaction are:

1. When multiple transactions run in parallel, the operation of different things runs cross-operation;

2. The transaction was forcibly stopped during operation.

There are several states for transactions:

1. Abort the transaction: Failure in execution, unable to perform the completed transaction;

2. Transaction rollback: Undo the Abort transaction to the database update;

3. Committed transaction: Successful execution of completed transaction.

Several propagation characteristics of a transaction
1. Propagation_required: If there is a transaction, the current transaction is supported. If no transaction is enabled,
2 is turned on. Propagation_supports: If there is a transaction, the current transaction is supported. If there is no transaction, the execution of the non-transactional
3. Propagation_mandatory: If a transaction already exists, the current transaction is supported. Throws an exception if there is no active transaction.
4. Propagation_requires_new: Always open a new transaction. If a transaction already exists, the existing transaction is suspended.
5. Propagation_not_supported: Always executes in a non-transactional manner and suspends any existing transactions.
6. Propagation_never: Always executes in a non-transactional manner, and throws an exception if there is an active transaction
7. Propagation_nested: If an active transaction exists, it is run in a nested transaction. If there is no active transaction,
      executes by the Transactiondefinition.propagation_required property

Isolation level for spring transactions
1. Isolation_default: This is a platfromtransactionmanager default isolation level, using the default transaction isolation level of the database.
The other four correspond to the isolation level of JDBC
2. Isolation_read_uncommitted: This is the lowest isolation level of a transaction, which allows a foreign transaction to see the uncommitted data for this transaction.
This isolation level produces dirty reads, non-repeatable reads, and Phantom reads.
3. isolation_read_committed: Ensure that a transaction modified data is committed before it can be read by another transaction. Another transaction cannot read uncommitted data for the transaction
4. Isolation_repeatable_read: This transaction isolation level prevents dirty reads and cannot be read repeatedly. However, Phantom reads may occur.
In addition to ensuring that one transaction cannot read uncommitted data from another transaction, it ensures that the following conditions are avoided (non-repeatable read).
5. Isolation_serializable This is the most cost-effective, but most reliable, transaction isolation level. Transactions are processed for sequential execution.
In addition to preventing dirty reading, non-repeatable reading, but also avoids phantom reading.

There are 4 isolation levels for database transactions, from low to high to read uncommitted, Read committed, Repeatable read, and Serializable, which can solve the problems of dirty reading, non-repeatable reading, and Phantom reading one by one.

√: May appear x: does not appear

	Dirty Read	Non-REPEATABLE READ	Phantom reading
Read UNCOMMITTED	√	√	√
Read committed	X	√	√
REPEATABLE READ	X	X	√
Serializable	X	X	X

Note: We discuss the isolation level scenario, mainly in the case of multiple transactions concurrency, so the next explanation is around the transaction concurrency. READ UNCOMMITTED not submitted

The company paid, the leader of the 5000 yuan to the Singo account, but the transaction did not submit, and Singo just to check the account, found that the salary has been to the account, is 5000 yuan whole, very happy. Unfortunately, the leadership found that the amount of wages issued to Singo is not correct, is 2000 yuan, and then quickly rolled back to business, modify the amount, the transaction will be submitted, and finally singo the actual salary of only 2000 yuan, Singo empty joy a game.

The above situation, that is what we call dirty Read, two concurrent transactions, "transaction A: lead to Singo payroll", "Transaction B:singo query Payroll account", transaction B read the transaction A has not yet committed data.

When the isolation level is set to read UNCOMMITTED, dirty reads can occur and how to avoid dirty reads, see the next isolation level. Read Committed reading commit

Singo take the payroll card to spend, the system read to Cary really have 2000 yuan, and at this time her wife also just in the online transfer, the Singo Pay card of 2000 yuan to another account, and before Singo submitted the business, when Singo deduction, System Check to Singo's payroll card has no money, deduction failure, Singo very puzzled, obviously card money, why ...

The above situation, that is what we call non-repeatable read, two concurrent transactions, "transaction A:singo consumption", "Transaction B:singo wife online transfer", transaction A in advance read the data, transaction B immediately updated the data, and committed the transaction, and transaction a read the data again, The data has changed.

When the isolation level is set to read Committed, dirty reads are avoided, but may cause non-repeatable reads.

The default level for most databases is read committed, such as SQL Server, Oracle. To resolve the issue of non-repeatable reads, see the next isolation level. Repeatable READ Repeat

You can avoid non-repeatable reads when the isolation level is set to repeatable read. When Singo took the payroll card to spend, once the system began to read the Payroll card information (that is, the start of the transaction), Singo's wife could not change the record, that is Singo wife can not be transferred at this time.

Although repeatable read avoids non-repeatable reads, it is possible to have phantom reads.

Singo's wife works in the banking department, and she often views Singo's credit card consumption records through the internal banking system. One day, she was inquiring into the total consumption amount of credit card in Singo month (select SUM (amount) from transaction where month = this month) was $80, and Singo at this time was good to eat outside the sea plug at the cashier to pay, spend 1000 yuan , which adds a $1000 consumption record (insert transaction ... ), and submitted a transaction, then Singo's wife will singo the current month credit card consumption details printed to A4 paper, but found that the total consumption of 1080 yuan, Singo wife is very surprised, thought there was an illusion, the illusion of such a generation.

Note: The default isolation level for MySQL is repeatable read. Serialization of Serializable

Serializable is the highest transaction isolation level, with the highest cost and low performance, which is rarely used at this level, where the transaction sequence executes not only to avoid dirty reads, non-repeatable reads, but also to avoid Phantom reads.

A description of some of these concepts:

Dirty read: refers to when a transaction is accessing the data, and the data is modified, and this modification has not yet been committed to the database, then another transaction also accesses the data, and then used this data. Because this data is data that has not yet been submitted, the data that is read by another transaction is dirty, and the operation based on dirty data may not be correct.

non-repeatable read: refers to reading the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction, the data that the first transaction two reads may be different because of the modification of the second transaction. This occurs when the data that is read two times within a transaction is not the same and is therefore called non-repeatable read.

  Illusion read: refers to a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table that involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred.