DB2 concurrency and Transaction isolation levels

1. When transactions are not isolated from each other in a multiuser environment, the following three consequences occur:
1 Dirty reads : This happens when a transaction reads data that has not yet been committed. For example, transaction 1 changes a row of data, and transaction 2 reads the data for a row changed by transaction 1 before transaction 1 commits the modification. If transaction 1 rolls back the modification, then transaction 2 attends to data that does not actually exist.
2 not repeatable reads : This happens when a transaction reads data from the same row two times and gets a different value. For example: Transaction 1 reads a row of data, and transaction 2 changes or deletes those rows and submits the changes. If transaction 1 reads the row again, transaction 1 gets a different number (if that row is updated) or if the row is not present (if that row is deleted).
3 Phantom Data : This happens when a row of data satisfies the search rules but is not seen at the beginning. For example, transaction 1 reads a series of rows that satisfy the search rule, and transaction 2 inserts a row that satisfies the transaction 1 search rule. If transaction 1 executes the query again, a different series of rows is obtained.
When you allow multiple applications to access the same data at the same time, you should maintain database consistency and data integrity. This is called "Concurrency" (concurrency). DB2 uses four different transaction isolation levels to implement concurrency. An isolation level determines how data is locked or isolated from other processes when the data is accessed. DB2 supports the following isolation levels:
• Repeatable Reading
• Read stability
• Cursor stability
• UNCOMMITTED Read

2. Isolation Level

The isolation level associated with an application process defines the level at which a process is isolated from other concurrent execution of application processes. The isolation level is specified as a property of the package used by the application process. A package (package) is a database object created during the execution of the BIND, precompile, or rebind command, which contains the SQL compiler output and is used to execute the SQL statement. You can specify an appropriate isolation level when you precompile (prepare) an application, or when you bind an application to a database. When the application is precompiled (precompiled), the embedded SQL statement is replaced by the source code that can be submitted to the SQL compiler. When the application is bound to the (bound to) database, the SQL compiler output is converted into a package.

The DB2 UDB supports the following isolation levels: repeatable reads (repeatable READ,RR) ensure that any table row reads during the work unit (UOW) are not changed by other application processes until UOW completes. Similarly, any rows that are changed by another application process are not read until they are submitted by the application process. Application processes running at the RR level are completely isolated from the effects of concurrent application processes. reading stability (read Stability,rs) is similar to RR. However, application processes running at the RS level are not completely isolated from the effects of concurrent application processes. If such an application process issues the same query more than once, it will see the changed data or a new Phantom (phantom) row added by another application process. cursor stability (Cursor Stability,cs) also ensures that any rows that are changed by another application process are not read until they are submitted by that application process. However, the CS isolation level only ensures that the current row for each updatable cursor is not changed by another application process, and that the rows read during UOW can be changed by other application processes. UNCOMMITTED read (uncommitted read,ur) for certain operations, any row that is read during UOW can be changed by another application process, and any row that has been changed by another application process is allowed to be read, even if the change has not yet been committed. For other operations, UR is similar to CS.