Learning Visually with Examples [Electronic resources] نسخه متنی

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.

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 

or

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.

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).

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.

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

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.

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