Db2 isolation level-Detailed Description

 

JDBCIsolation level	Database isolation level	Data Access
TRANSACTION_READ_UNCOMMITTED (Uncommitted Read) Uncommitted Read	Ur	Dirty read can read updated data when no data is submitted.
TRANSACTION_READ_COMMITTED (Cursor Stability) Cursor Stability	Cs	When querying a transaction, data before submission can be read. After the data is submitted, the data can be read from the current query. The table is not locked during data update.
TRANSACTION_REPEATABLE_READ (Read Stability) Read Stability	Rs	When querying a transaction, the data updated by other transactions cannot be read, and new data committed by other transactions can be read.
TRANSACTION_SERIALIZABLE (repeated Read) Repeatable Read	Rr	During a transaction query, no data modification to the query table is allowed.

"Repeatable read" isolation level rr
When repeated read isolation levels are used, all rows referenced by the transaction are locked during execution of a single transaction. When this isolation level is used, the same SELECT statement sent multiple times by the same transaction will always produce the same result; the loss of updates, dirty reads, non-repeated reads, and Phantom will not happen.
Transactions at the read isolation level that can be repeated can be used to retrieve the same row set multiple times, and can perform any operation on them until the transaction is terminated by the commit or rollback operation; other transactions are not allowed to perform insert, update, or delete operations because these operations will affect the row set being used during the isolation of the transaction. To ensure that the data accessed by a transaction running at the "Repeatable read" isolation level is not negatively affected by other transactions, therefore, each row referenced by the isolation firm is locked-instead of the rows actually retrieved and/or modified. Therefore, if a transaction scans 1000 rows but only retrieves 10 rows, the 1000 rows scanned (not only the 10 rows retrieved) will be locked.
In the real environment, how does this isolation level work? Assume that you have a large hotel with a website. The website accepts the customer's room reservation based on the "first come first served" principle. If your hotel reservation application runs at the "Repeatable read" isolation level, when the customer retrieves a list of all available rooms in a certain date segment, you will not be able to change the fees for those rooms within the specified date range, and other customers will not be able to make or cancel the reservation will change the list until the transactions that generate the list are terminated. (For any room outside the scope specified by the first customer's query, you can change the room rate, and other customers can also make or cancel the room reservation .)


"Read stability" isolation level rs
When the read stability isolation level is used, all rows retrieved by the firm are locked during the execution of a single transaction. When this isolation level is used, other transactions cannot change all rows read by the isolation transaction until the isolation transaction ends. In addition, changes made by other transactions to other rows are invisible to transactions running at the read stability isolation level before committing. Therefore, when the read stability isolation level is used, multiple SELECT statements in the same transaction may produce different results. Loss of updates, dirty reads, and non-repeated reads will not occur. However, phantom may occur.
When the "Repeatable read" isolation level is used, every row referenced by the isolation transaction is locked. However, at the "read stability" isolation level, only the rows actually retrieved and/or modified by the isolation transaction are locked. Therefore, if a transaction scans 1000 rows but only retrieves 10 rows, only the 10 rows retrieved (instead of the 1000 rows scanned) are locked.
How does this isolation level change the way Hotel Booking applications work? Now, when a customer retrieves a list of all available rooms in a certain date segment, you can change the room rate for any room in the hotel, other customers can also cancel the reservation for the reserved room within the specified date range of the first customer's query. Therefore, if a list is generated again before the transaction to be submitted for query is terminated, the new list may contain a new house price or a room unavailable when the list is generated for the first time.


"Cursor stability" cs at isolation level
When the stability isolation level of a cursor is used, the row referenced by the cursor is locked as long as the cursor used by the isolation firm is located on a row. The obtained lock remains valid until the cursor is relocated (usually by calling the FETCH statement) or the isolation transaction ends. Therefore, when this isolation level is used, multiple SELECT statements in the same transaction may produce different results. The loss of updates and dirty reads does not occur, but there may be non-repeated read and phantom.
When a transaction with the "cursor stability" isolation level retrieves a row from a table through a updatable cursor, when the cursor is located on the row, other transactions cannot update or delete the row. However, if the row to be locked is not accessed by an index, other transactions can add new rows to the table, update and/or delete the rows before and after the locked rows. In addition, if an isolation transaction modifies any row it retrieves, other transactions cannot update or delete the row even if the cursor no longer exists in the modified row before the isolation transaction ends.
Changes made by other transactions on other rows are invisible to transactions that use the "cursor stability" isolation level before committing. By default, most transactions use the "cursor stability" isolation level.
What is the impact of this isolation level on the hotel booking application? Now, when a customer retrieves the list of all available rooms in a certain date segment and then views information about each room in the generated list (one room is viewed each time ), you can change the room rate for any room in the hotel, while other customers can make or cancel reservation for any room in any date segment. The only exception is the room currently being viewed by the first customer. When the first customer views the information of another room in the list, the same applies to this new room. Now you can change the room rate that the first customer just viewed, other customers can also reserve the room, but cannot perform these operations on the room currently being viewed by the first customer.


"Uncommitted read" isolation level ur
When an uncommitted read isolation level is used, when a single transaction retrieves a row, only when another transaction attempts to delete or change the table where the row to be retrieved is located, these rows will be locked during a single transaction. When using this isolation level, the row is usually not locked, so the loss of updates, dirty reads, non-repeated read and phantom may occur.
In most cases, changes made by other transactions to rows are visible to transactions that use the uncommitted read isolation level before committing or rollback. However, such transactions cannot see or access tables, views, or indexes created by other transactions until those transactions are committed. Similarly, if other transactions Delete existing tables, views, or indexes, transactions with the "uncommitted read" isolation level can be understood only when the transaction for the delete operation is terminated. This is an exception: When transactions running at the uncommitted read isolation level use updatable cursors, this transaction runs at the same level as the "cursor stability" isolation level, and applies the constraints at the "cursor stability" isolation level.
The uncommitted read isolation level is usually used for transactions that access read-only tables and/or some transactions that execute SELECT statements. These statements have no negative effect on the uncommitted data of other transactions.
So what is the impact of this isolation level on the hotel booking application? Now, when a customer retrieves a list of all available rooms in a certain date segment, you can change the room rate for any room in the hotel, other customers can also make or cancel reservations for any room within any date range. In addition, if other customers cancel the reservation, even if they have not terminated their transactions and submit those canceled to the database, the generated list can contain the rooms for the cancellation.
2. Differences between databases: 1). Oracle uses the multi-granularity blocking mechanism with intention locks to control concurrency and ensure data consistency. Its DML lock (Data lock) is divided into two levels (granularity): Table-level and row-level. Generally, DML operations only obtain the intention lock (RS or RX) at the table level, and the real blocking granularity is still at the row level; DB2 also uses a multi-granularity blocking mechanism with intention locks to control concurrency to ensure data consistency. Its DML lock (Data lock) is divided into two levels (granularity): Table-level and row-level. Generally, DML operations obtain only the intention lock (IS, SIX or IX) at the table level, and the real blocking granularity IS also at the row level. In addition, in the Oracle database, simply reading data (SELECT) is not locked, which improves the system's concurrency. Oracle emphasizes the ability to read & quot; to data, and can quickly read data. DB2 locks emphasize & quot; read consistency & quot;. When reading data (SELECT), data will be read at different isolation levels (RR, RS, CS) the S, IS, and IS locks are applied separately. The lock IS not applied only when the UR isolation level IS used. This ensures that the data read by different applications and users is consistent.

2 ). while supporting high concurrency, DB2 and Oracle have different locking mechanisms: Oracle uses design techniques such as intention locks and lock marks on data rows, this reduces the cost of Oracle's Row-Level Lock maintenance and makes it advantageous in database concurrency control. In DB2, each lock will apply to allocate a certain byte of memory space in the lock memory (locklist), specifically the X lock 64 byte memory, S lock 32 byte memory (note: before DB2 V8, X locks 72 bytes of memory and S locks 36 bytes of memory ).

3) There is no lock upgrade in the Oracle database. When the row-Level Lock usage in the database table exceeds the locklist * maxlocks In the DB2 database, the lock upgrade will occur.

4 ). in Oracle, when a session is used to insert, update, and delete a table, another session can still read the table's front image (before image) from the mongoe rollback segment or restore the tablespace ); in DB2, when a session is used to insert, update, or delete a table, the other session is still in the lock wait status when reading the table data, unless the UR isolation level is used, the uncommitted values of the first session can be read. Therefore, different sessions in Oracle have read inconsistencies at the same time, all sessions of DB2 at the same time are read-consistent. 5). db2 uses cs by default, and oracle uses ur by default.

Author: "java-true"