Posts
Propagation is used to define how transactions related to each other. Common options
Isolation Defines the data contract between transactions.
The problem with multiple transactions
Scenario 3 is called Phantom reads.
So, the isolation level is the extent to which Scenario 1, Scenario 2, and Scenario 3 can be prevented. You can obtain a complete isolation level by implementing locking. That is preventing concurrent reads and writes to the same data from occurring. But it affects performance. The level of isolation depends upon application to application on how much isolation is required.
- Required: Code will always run in a transaction. Create a new transaction or reuse one if available.
- Requires_new: Code will always run in a new transaction. Suspend current transactions if one exists.
- Read Uncommitted: Allows dirty reads
- Read Committed: Does not allow dirty reads
- Repeatable Read: If a row is read twice in the same transaction, the result will always be the same
- Serializable: Performs all transactions in a sequence
An isolation level is about how much a transaction may be impacted by the activities of other concurrent transactions. It supports consistency leaving the data across many tables in a consistent state. It involves locking rows and/or tables in a database.
The problem with multiple transactions
- Scenario 1. If the T1 transaction reads data from table A1 that was written by another concurrent transaction T2. If on the way T2 is a rollback, the data obtained by T1 is an invalid one. E.g a=2 is original data. If T1 read a=1 that was written by T2.If T2 rollback then a=1 will be a rollback to a=2 in DB. But, Now, T1 has a=1 but in the DB table, it is changed to a=2.
- Scenario2.If a T1 transaction reads data from table A1.If another concurrent transaction(T2) updates data on table A1. Then the data that T1 has read is different from table A1. Because T2 has updated the data in table A1.E.g if T1 read a=1 and T2 updated a=2.Then a!=b.
- Scenario 3. If the T1 transaction reads data from table A1 with a certain number of rows. If another concurrent transaction(T2) inserts more rows on table A1. The number of rows read by T1 is different from the rows in table A1
Scenario 1 is called Dirty reads.
Scenario 2 is called Non-repeatable reads.Scenario 3 is called Phantom reads.
So, the isolation level is the extent to which Scenario 1, Scenario 2, and Scenario 3 can be prevented. You can obtain a complete isolation level by implementing locking. That is preventing concurrent reads and writes to the same data from occurring. But it affects performance. The level of isolation depends upon application to application on how much isolation is required.
- ISOLATION_READ_UNCOMMITTED: Allows to read changes that haven’t yet been committed. It suffers from Scenario 1, Scenario 2, Scenario 3
- ISOLATION_READ_COMMITTED: Allows reads from concurrent transactions that have been committed. It may suffer from Scenario 2 and Scenario 3. Because other transactions may be updating the data.
- ISOLATION_REPEATABLE_READ: Multiple reads of the same field will yield the same results until it is changed by themselves.It may suffer from Scenario 3. Because other transactions maybe inserting the data
- ISOLATION_SERIALIZABLE: Scenario 1,Scenario 2,Scenario 3 never happens.It is complete isolation. It involves full locking. It affects performance because of locking.
