0 DB2 isolation level 101

• Repeatable read (RR) 3: Would lock every row even it is not in the result set.
• Read stability(RS) 2: would only lock the rows that is part of result set
• Cursor Stability (CS) 1  –> Default isolation level:  Only locks the row that is currently referenced by a cursor that was declared
  and opened by the owning transaction
• Uncommitted read (UR) 0:  Rows are locked only if the transaction modifies the data

How to set the isolation level

CLI/ODBC

• Assign TCNISOLATION in the db2cli.ini
• SQL_ATTR_TXN_ISOLATION

SQL

select * from employee where empno = '000120' WITH RR

 

LOCK Table/ROw

-- Force the row level lock - Default 

alter table employee locksize row

-- Force the table level lock

alter table employee locksize table

 

In transaction

lock table employee in share mode

lock table employee in exclusive mode

Locklist & MAXLOCKs& Lock escalation

locklist is the memory allocation within the database. It is used by db to manage the locks.

MAXLOCKS is the db parameter for number of locks each transaction can use.

When locklist is full, database will release one of the transaction’s row level lock and make the space within the locklist , then acquire the table level lock. This process is called LOCK ESCALACTION

When all the row level lock has been release but there is still not enough space in the lock list to hold the lock, the SQL error would be generate and the transaction would be terminated.

Locktimeout

LOCKTIMEOUT is the parameter to decide how long for the transaction would time out while waiting to acquire the lock. The default value is –1 , which means it will wait forever…