The higher the isolation level, the greater the integrity and consistency of the data, but also the greater the impact on concurrency performance. For most applications, it is preferable to set the isolation level of the database system to read Committed. It avoids dirty reads and has better concurrency performance. Although it causes concurrency problems such as non-repeatable reads, Phantom reads, and second-class loss updates, the application can be controlled by pessimistic or optimistic locks on individual occasions where such problems may occur.
Several isolation levels for the database:
- Read uncommitted: Allows a transaction to read change data that has not been committed by another transaction, which can cause dirty reads, non-repeatable reads, and Phantom read problems.
- Read COMMITTED: Only allow transactions to read change data that has been committed by other transactions, avoid dirty reads, and still have non-repeatable read and Phantom read problems.
- repeatableRead (REPEATABLE Read): Ensures that transactions can read the same value from one field multiple times, preventing updates to this field from other transactions during the duration of the transaction, avoiding dirty reads and non-repeatable reads, and still having a phantom read problem.
- SERIALIZABLE(serialization): Ensures that transactions can read the same rows from one table, preventing other transactions from performing insert, update, and delete operations on the table during this transaction, avoiding all concurrency problems, but with very low performance.
Oracle supports two levels of transaction isolation:
READ COMMITTED(default transaction isolation level),SERIALIZABLE
MySQL supports four types of transaction isolation Levels , where repeatable READ is the default transaction isolation level.
By knowing that multiple transactions run concurrently, if you do not use the above four isolation mechanisms, you may have multiple concurrency problems, including dirty reads, non-repeatable reads, and Phantom reads, which explain the following concurrency problems:
There are two things (T1,T2) running simultaneously
- Dirty Read : T1 read a field that has been T2 modified but not yet committed, and for some reason the T2 thing is rolled back, the contents of T1 read are temporary and invalid.
- non-repeatable read : T1 reads a field and then T2 updates the field, T1 This field value is changed.
- Phantom Read : T1 reads a field from a table, and then T2 inserts some new rows into the table, and then T1 reads the table a few more rows.
Ii. Characteristics of database transactions: atomicity, consistency, isolation, persistence
- atomicity: The atomicity of a transaction means that the program contained in the transaction acts as a logical unit of work for the database, which either executes all of the data modification operations or does not execute at all, a feature called atomicity. (Simply put, a few operations on the database are either fully executed or not executed at the same time, either successfully or simultaneously failed)
- Consistency: transactional consistency is worth the consistency of the database that must be in a consistent state (halfway across the line) before and after a transaction is executed. (If the state of the database satisfies all integrity constraints, the database is said to be consistent.) The protection of all semantics in the consistency processing database. such as: Customer K1 to the customer K2 transfer, K1 account reduction is the amount of K2 account increase, before the transfer of K1 and K2 account amount and the sum of the K1 and K2 account after the transfer of the same amount, may not meet this consistency during the transfer, but the database data is consistent before and after the transaction;
- Isolation: isolation means that concurrent transactions are isolated from one another. (The operations within a transaction and the data being manipulated must be encapsulated and not seen by other transactions attempting to modify)
- persistence: persistence refers to ensuring that committed updates cannot be lost when a system or media fails. (A transaction commit, the DBMS guarantees that its changes to the data in the database should be permanent, can withstand any system failure, and persistence is guaranteed through database backup and recovery)
Iii. Dissemination of acts
- Propagation_required: If there is currently no transaction, a new transaction is created, and if a transaction is currently present, the transaction is added, which is the most common setting.
- Propagation_supports: Supports the current transaction, joins the transaction if there is a current transaction, and executes as non-transactional if there is no current transaction. ‘
- Propagation_mandatory: Supports the current transaction, joins the transaction if there is a current transaction, throws an exception if no transaction is currently present.
- Propagation_requires_new: Creates a new transaction that creates a new transaction, regardless of whether the transaction currently exists.
- Propagation_not_supported: Executes the operation in a non-transactional manner, suspending the current transaction if a transaction is currently present.
- Propagation_never: Executes in a non-transactional manner and throws an exception if a transaction is currently present.
- Propagation_nested: Executes within a nested transaction if a transaction is currently present. If there is currently no transaction, perform a similar operation as propagation_required.
- 1:propagation_required
- Join the transaction that is currently being executed is not in another transaction, then a new transaction
- For example, Serviceb.methodb's transaction level is defined as propagation_required, and because of the execution of Servicea.methoda,
- Servicea.methoda has already started the transaction, when the call Serviceb.methodb,serviceb.methodb see that he has run in Servicea.methoda
- Transaction, no new transactions will be started. And if Servicea.methoda is running and finds himself not in a transaction, he assigns himself a transaction.
- In this way, the transaction will be rolled back if an exception occurs in the Servicea.methoda or anywhere within the SERVICEB.METHODB. Even if Serviceb.methodb's affairs have been
- Commit, but Servicea.methoda will roll back in the next fail, SERVICEB.METHODB will also roll back
- 2:propagation_supports
- If you are currently running in a transaction, that is, as a transaction, if you are not currently in a transaction, run as a non-transactional
- 3:propagation_mandatory
- Must run in a transaction. In other words, he can only be called by a parent transaction. Otherwise, he's going to throw an exception.
- 4:propagation_requires_new
- That's a bit of a detour. For example, we design Servicea.methoda with a transaction level of Propagation_required,serviceb.methodb of Propagation_requires_new,
- Then when the execution to Serviceb.methodb, Servicea.methoda the transaction will be suspended, Serviceb.methodb will start a new transaction, waiting for the completion of the SERVICEB.METHODB transaction,
- He just went on with the execution. The difference between his affairs and Propagation_required is the degree of rollback of the transaction. Because Serviceb.methodb is a new business, there is
- Two different transactions. If the SERVICEB.METHODB has been committed, then Servicea.methoda fails to rollback, SERVICEB.METHODB is not rolled back. If the serviceb.methodb fails to roll back,
- If the exception he throws is captured by Servicea.methoda, the Servicea.methoda transaction may still be committed.
- 5:propagation_not_supported
- Transactions are not currently supported. For example, Servicea.methoda's transaction level is propagation_required, and Serviceb.methodb's transaction level is propagation_not_supported,
- Then when execution to Serviceb.methodb, Servicea.methoda's transaction hangs, and he runs out of non-transactional state, and then continues the Servicea.methoda transaction.
- 6:propagation_never
- Cannot run in a transaction. Suppose the transaction level of Servicea.methoda is propagation_required, and Serviceb.methodb's transaction level is Propagation_never,
- Then Serviceb.methodb will throw an exception.
- 7:propagation_nested
- The key to understanding nested is savepoint. The difference between him and propagation_requires_new is that propagation_requires_new another transaction, which will be independent of his father's affairs,
- and nested's affairs are dependent on his father's affairs, and his submission is to be submitted as a piece of his father's business. In other words, if the parent transaction is finally rolled back, he will also be rolled back.
- The advantage of nested affairs is that he has a savepoint.
Database transactions 4 Isolation levels and 7 propagation behaviors
