Database transaction isolation mechanism and transaction isolation mechanism

Database transaction isolation mechanism and transaction isolation mechanism

Transaction isolation level: the degree of isolation between one transaction's modification to the database and another transaction in parallel.

When two concurrent transactions access the same row in the database table at the same time, these problems can be attributed to five categories: 3 types of data reading problems (dirty reading, non-repeated reading, and phantom reading)
Two types of data update problems (first type loss update and second type loss update ).

1. Phantom read:Transaction T1 reads a statement that specifies the where condition and returns the result set. At this time, transaction T2 inserts a new row, which meets the where condition of T1. Then T1 uses the same condition to query again. The records inserted by T2 are displayed in the result set. This new record is an illusion.

2. Duplicate read is not allowed:Transaction T1 reads a row of records, and then transaction T2 modifies the records that T1 has just read. Then T1 queries again and finds that the records read for the first time are different. This is called non-repeated read.

3. Dirty read:Transaction T1 updates a row of records and has not submitted any modifications. This T2 reads the updated data, and T1 performs a rollback operation to cancel the modification, therefore, the row read by T2 is invalid, that is, dirty data.

4. Category 1 loss update

When transaction A is canceled, the updated data of transaction B that has been committed is overwritten. This error may cause serious problems. You can see it through the following account withdrawal transfer:

Type 2 lost updates

Transaction A overwrites the data committed by transaction B, resulting in loss of operations performed by transaction B.

To solve these problems, the SQL standard defines the following transaction isolation levels

The Oracle Database supports read committed and SERIALIZABLE transaction isolation levels. Therefore, Oracle does not support dirty reading.

The default transaction isolation level defined by the SQL standard is SERIALIZABLE,Oracle uses read committed by default.

To avoid loss of the second type of update

SQL _1: select 1 from dual for update;
SQL _2: select 1 from dual for update;
SQL _3: select 1 from dual for update nowait;

Execute SQL _1, do not submit, table dual is locked

Branch 1): Execute SQL _2, SQL _2 is blocked, wait for SQL _1 to submit

Branch 2): Execute SQL _3, because there is nowait, so immediately return the error message "ORA-00054: the resource is busy, but specify to get the resource in NOWAIT mode"

Conclusion: The nowait keyword notifies Oracle that the SQL statement modifies or deletes data in a non-blocking manner. If the involved data is found to be occupied (locked ), the system immediately notifies Oracle that this resource is in use and returns an error message.

In the SQL92 standard, there are four transaction isolation levels: Read Uncommitted, Read Committed, Read Repeatable, and Serializable.

Read Uncommitted and Read Committed are statement-level, while Read Repeatable and Serializable are transaction-level.

The statements for setting transaction isolation levels in Oracle and SQL Server are the same, and both use the standard SQL92 syntax, that is:

Set Transaction Isolation Level Read Committed

1. isolation level and implementation mechanism in SQL Server

All four isolation levels are provided in SQL Server.

Read Uncommitted

A session can read the uncommitted UPDATE results of other transactions. If the transaction ends with a rollback, the read results may be incorrect, therefore, most database applications do not use this isolation level.

Read Committed

This is the default isolation level of SQL Server. A transaction with this isolation level can only read the UPDATE results submitted by other transactions. Otherwise, wait, however, other sessions can modify the records read in this transaction without waiting for the transaction to end. Obviously, at this isolation level, two identical read operations in a transaction, the results may be different.

Read Repeatable

In a transaction, if no record is added between two read operations with the same condition, and there are no other update operations, the number of records increases under this query condition, the two read results are the same. In other words, the record read for the first time in a transaction will not change during this transaction. SQL Server locks the read records throughout the transaction to achieve this isolation level. In this way, before the transaction ends, other sessions cannot modify the records read in the transaction, however, SQL Server can only wait for the transaction to end, but does not prevent other sessions from adding records to the table or modifying other records.(Other sessions: you cannot modify the data read by the current session, but you can add data --- You can read it in fantasy)

Serializable

In a transaction, the read operation results in records committed by other transactions before the transaction starts. SQL Server implements this isolation level by locking the table throughout the transaction. At this isolation level, all DML operations on this table are not allowed, that is, to wait until the transaction ends, this ensures that the results of the two read operations in a transaction are certainly the same.

 

2. isolation level and implementation mechanism in Oracle

 

In Oracle, there is no Read Uncommitted or Repeatable Read isolation level. In this way, a session is not allowed to Read the Uncommitted data modification results of other transactions in Oracle, this avoids read errors caused by transaction rollback. The Read Committed and Serializable levels in Oracle are similar to those in SQL Server, but their implementation methods are quite different.

 

In Oracle, the so-called rollback segment (earlier than Oracle9i) or the revocation segment (Oracle9i version) exists. When Oracle modifies data records, the modified results will be stored in the rollback or withdrawal segments. This is because of this mechanism that Oracle implements transaction isolation levels completely different from SQL Server. In Oracle, read operations do not impede update operations, and update operations do not impede read operations. In this way, read operations do not wait until the update transaction ends at various isolation levels in Oracle, the update operation does not wait because of the read operation in another transaction, which is also an advantage of Oracle transaction processing.

Read Committed

The default setting of Oracle is Read Committed isolation level (also called statement-level isolation). At this isolation level, if a transaction is performing DML operations on a table, at this time, another session reads the records of this table, Oracle will read the records stored before the rollback segment or the Undo segment, instead of waiting for the end of the update transaction like SQL Server.

Serializable

At the Serializable isolation level (also known as transaction-level isolation), The read operation in the transaction can only read the data results submitted before the transaction starts. If another transaction is modifying the record at the time of reading, then Oracle will find the original unmodified record in the rollback segment or undo segment (and store it in the rollback segment or undo segment before the transaction where the read operation is located starts ), at this time, the read operation will not wait because the corresponding record is updated.