- 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…
0 comments:
Post a Comment