Section 11.4. Changing Isolation Levels

11.4. Changing Isolation Levels

The isolation level is not bound to a database. Each application can use a different isolation level so that a different locking mechanism can be applied. Isolation levels can be set at different levels:

Session level
Application level
Statement level

The following sections describe each of these levels.

11.4.1. Using the DB2 Command Window

The current isolation level is stored in a DB2 special register called CURRENT ISOLATION. The special register is a value with two characters (data type of CHAR(2)) of the current isolation level for any dynamic SQL statements issued within the current session.

To obtain the current isolation level value, connect to the database and issue either of these statements:

 VALUES CURRENT ISOLATION SELECT CURRENT ISOLATION FROM sysibm.sysdummy1

The following are the possible values:

UR (uncommitted read)
CS (cursor stability)
RS (read stability)
RR (repeatable read)
Blank (means that the default isolation level is used)

To change the isolation level, use the SET CURRENT ISOLATION statement. Figure 11.11 shows the syntax diagram for this statement.

Figure 11.11. Syntax diagram for the SET CURRENT ISOLATION command

         .-CURRENT-.             .-=-. >>-SET--+---------+--ISOLATION--+---+--+-UR----+---------------><                                        +-CS----+                                        +-RR----+                                        +-RS----+                                        '-RESET-'

Figure 11.12 demonstrates a few examples of how to set and obtain the current isolation level.

Figure 11.12. Examples of the SET CURRENT ISOLATION LEVEL command

It is important to understand that changes to the DB2 special register affects the current session. Subsequent dynamic SQL statements executed in this session will use this isolation level. The change only applies for dynamic SQL statements. For static SQL statements or packages, you can control the isolation level through the DB2 bind command discussed in the next section.

11.4.2. Using the DB2 PRECOMPILE and BIND Commands

To execute an SQL statement, it must be compiled in an executable form that DB2 understands. This executable form of the statement is known as the data access plan. Data access plans are stored in database objects called packages.

Data access plans for dynamic SQL statements are created at execution time. DB2 uses the most current database statistics, configuration parameters, and DB2 special register settings (such as CURRENT ISOLATION) to evaluate and generate the most optimal plan.

When an application with static SQL statements is precompiled, the prepared statements are stored in a bind file generated by the DB2 precompiler. To create the database access plan from the bind file, you need to invoke the bind utility. The utility takes the bind file as input, creates a package that contains the data access plan, and binds it to the database.

Both the DB2 precompile and bind commands let you specify some characteristics of how the package should be executed, like the query optimization level, use of row blocking, and the isolation level. For example, if you want to precompile or bind a package using a nondefault isolation level, use:

 precompile appfile.sqc isolation RR

 bind bindfilename.bnd isolation RR

where appfile.sqc is an embedded C program containing static SQL, and bindfilename.bnd is a bind file containing SQL in internal format that is to be bound into a package.

Once the package is bound, you can use the system catalog tables or DB2 Control Center to find out the isolation level specified.

Using the system catalog tables, you can issue the following query:

 SELECT pkgschema, pkgname, isolation FROM syscat.packages

Using the DB2 Control Center, navigate to the folders Instance > Database > Application Objects > Packages. You should see the isolation level column on the right panel, as shown in Figure 11.13. For example, the package highlighted in Figure 11.13 was bound with isolation level CS.

Figure 11.13. Using the Control Center to retrieve the isolation level of packages

11.4.3. Using the DB2 Call Level Interface

The DB2 call level interface (CLI) is the IBM callable SQL interface to DB2 database servers. It is a C/C++ application programming interface (API) for database access. If your application is using the DB2 CLI API, you can also set the isolation level with the CLI setting.

At the DB2 client, launch the Configuration Assistant. Right-click on the database you want to set the isolation level for, and select CLI Settings (see Figure 11.14).

Figure 11.14. Using the Configuration Assistant to access CLI settings

Switch to the Settings tab and click Add. This displays the Add CLI Parameter window as shown in Figure 11.15. Under the list of CLI keywords, select TxnIsolation and choose the desired isolation level. Note that the CLI settings apply to a database. This means that every application connecting to the database through this DB2 client will use the isolation level specified.

Figure 11.15. Setting the isolation level from the DB2 Configuration Assistant

If the Configuration Assistant is not available at the client, you can also update the DB2 CLI initialization file (db2cli.ini) directly. The file is located at the DB2 install directory. Insert TxnIsolation under the database name you wish and enter the isolation level you want to use. Each isolation level is identified by a number (see Table 11.2). The following example shows how to set the isolation for the SAMPLE database to repeatable read.

 [SAMPLE] DBALIAS=SAMPLE TXNIsolation=8

Table 11.2. DB2 CLI Values for the Isolation Levels
	Uncommitted Read	Cursor Stability	Read Stability	Repeatable Read
TXNIsolation Value	1	2	4	8

11.4.4. Using the Application Programming Interface

In addition to the CLI, DB2 provides various types of programming interfaces that your application can use. The Java Common Client for DB2 is one example. Most APIs such as Java Database Connectivity (JDBC) provide an option to specify the isolation level. A code snippet from a JDBC program is listed in Figure 11.16. For other APIs, check with the associated programming documentations.

Figure 11.16. Snippet of a JDBC program to specify an isolation level

 Class.forName("com.ibm.db2.jcc.DB2Driver"); Connection con=null; con = DriverManager.getConnection (jdbc:db2:sample,username,password); con.setTransactionIsolation(TRANSACTION_READ_UNCOMMITTED);

The names of the isolation levels used in APIs are usually different from those used in DB2. JDBC and DB2 isolation level mappings are listed in Table 11.3. For other APIs, refer to the documentation.

Table 11.3. Comparable JDBC and DB2 Isolation Levels
JDBC	DB2
TRANSACTION_READ_UNCOMMITTED	Uncommitted read
TRANSACTION_READ_COMMITTED	Cursor stability
TRANSACTION_REPEATABLE_READ	Read stability
TRANSACTION_SERIALIZABLE	Repeatable read

11.4.5. Working with Statement Level Isolation Level

So far, you have seen that isolation level can be set for a connection. To provide more granular concurrency control, DB2 has the ability to specify isolation level at the statement level.

Suppose an application has started a transaction with CS isolation level. To increase concurrency of a particular statement, you want the statement to be executed with RR isolation level. To do so, use the isolation clause WITH RR:

 UPDATE employee SET salary = 10000 WHERE empno='000010' WITH RR

Similarly, you can apply the WITH clause to the INSERT, DELETE, and SELECT statements. The same clause in the SELECT statement has an extra option for the RR and RS isolation level. Figure 11.17 shows the syntax diagram of the SELECT statement's isolation clause.

Figure 11.17. Syntax diagram of the SELECT statement's isolation clause

 >>-+---------------------------------------+-------------------><    '-WITH--+-RR--+---------------------+-+-'            |     '-lock-request-clause-' |            +-RS--+---------------------+-+            |     '-lock-request-clause-' |            +-CS--------------------------+            '-UR--------------------------' lock-request-clause: >>-USE AND KEEP--+-UPDATE----+--LOCKS--------------------------><                  '-EXCLUSIVE-'

The lock-request-clause is optional and specifies the type of lock that DB2 will acquire and hold. The owner of an UPDATE lock can update the row being locked. Concurrent processes can only read the data in the locked object but cannot update it. EXCLUSIVE lock, on the other hand, is a more restrictive type of lock. It does not allow concurrent processes to acquire any lock on the data.

A SELECT statement with the isolation clause will look similar to the following:

 SELECT empno, lastname, firstnme   FROM employee  WHERE deptno='A01'   WITH RR USE AND KEEP EXCLUSIVE LOCKS