Isolation level
The isolation level used during the execution of SQL statements determines the degree to which the activation group is isolated from concurrently executing activation groups.
Thus, when activation group P executes an SQL statement, the isolation level determines:
- The degree to which rows retrieved by P and database changes made by P are available to other concurrently executing activation groups.
- The degree to which database changes made by concurrently executing activation groups can affect P.
The isolation level can be explicitly specified on a DELETE, INSERT, SELECT INTO, UPDATE, or select-statement. If the isolation level is not explicitly specified, the isolation level used when the SQL statement is executed is the default isolation level.
| SQL Interface | Specification |
|---|---|
| Embedded SQL | COMMIT parameter on the Create SQL Program (CRTSQLxxx) commands. The SET OPTION statement can also be used to set the COMMIT values. (For more information about CRTSQLxxx commands, see Embedded SQL Programming.) |
| SQL functions and procedures | Static SQL statements in SQL functions and procedures use the isolation level that was in effect at the time the SQL function or procedure was created. The SET OPTION statement (COMMIT) can be used to set the isolation level. |
| Run SQL Statements | COMMIT parameter on the Run SQL Statements (RUNSQLSTM) command. (For more information about the RUNSQLSTM command, see SQL Programming.) |
| SET TRANSACTION SQL statement | Overrides the default isolation level within a unit of work. When the unit of work ends, the isolation level returns to the value it had at the beginning of the unit of work. This statement overrides any other specification of isolation level for static and dynamic SQL statements in the unit of work. (For more information about the SET TRANSACTION statement, see SET TRANSACTION.) |
| isolation-clause | The isolation-clause on the SELECT, SELECT INTO, INSERT, UPDATE, DELETE, and DECLARE CURSOR statements overrides the default isolation level for a specific statement or cursor. The isolation level is in effect only for the execution of the statement containing the isolation-clause and has no effect on any pending changes in the current unit of work. (For more information about the isolation-clause, see isolation-clause.) |
| Call Level Interface (CLI) on the server | SQL_ATTR_COMMIT or SQL_TXN_ISOLATION environment variable or connection options (For more information about CLI, see SQL Call Level Interfaces (ODBC).) |
| JDBC or SQLJ on the server using IBM® IBM Developer Kit for Java™ | transaction isolation property object (For more information about JDBC and SQLJ, see IBM Developer Kit for Java.) |
| ODBC on a client using the IBM i Access Family ODBC Driver | Commit Mode in ODBC Setup (For more information about ODBC, see IBM i Access.) |
| JDBC on a client using the IBM Toolbox for Java | Isolation Level in JDBC Setup (For more information about JDBC, see IBM i Access.) (For more information about the IBM Toolbox for Java, see IBM Toolbox for Java.) |
| OLE DB on a client using the IBM i Access Family OLE DB Provider | IsolationLevel Connection Object Property (For more information about OLE DB, see IBM i Access.) |
| ADO .NET on a client using the IBM i Access Family ADO .NET Provider | IsolationLevel in Connection Object Properties (For more information about ADO .NET, see IBM i Access.) |
These isolation levels are supported by automatically locking the appropriate data. Depending on the type of lock, this limits or prevents access to the data by concurrent activation groups that use different commitment definitions. Each database manager supports at least two types of locks:
- Share
- Limits concurrent activation groups that use different commitment definitions to read-only operations on the data.
- Exclusive
- Prevents concurrent activation groups using different commitment definitions from updating or deleting the data. Prevents concurrent activation groups using different commitment definitions that are running COMMIT(*RS), COMMIT(*CS), or COMMIT(*RR) from reading the data. Concurrent activation groups using different commitment definitions that are running COMMIT(*UR) or COMMIT(*NC) are allowed to read the data.
The following descriptions of isolation levels refer to locking data in row units. Individual implementations can lock data in larger physical units than base table rows. However, logically, locking occurs at the base-table row level across all products. Similarly, a database manager can escalate a lock to a higher level. An activation group is guaranteed at least the minimum requested lock level.
For a detailed description of record lock durations, see the discussion and table in the Commitment control topic of the SQL Programming topic collection.
Db2 for i supports five isolation levels. For all isolation levels except No Commit, the database manager places exclusive locks on every row that is inserted, updated, or deleted. This ensures that any row changed during a unit of work is not changed by any other activation group that uses a different commitment definition until the unit of work is complete.