IBM DB2 LUW DBA Tutorial -- Lock & Concurrency

/**

声明：此随笔为在youtube上学习‘ IBM DB2 LUW/UDB DBA Tutorial for Beginners' 课程笔记.

YouTube视频连接：https://www.youtube.com/watch?v=Pjvwm4rC5Ok&list=PLCNIKwM6iuv7g1hDWDcNkc66vZuVJmvZg

**/

Concurrency is the ability for multiple users or application programs to interactively share resources at the same time.

To what extent we can provide concurrency is dependent on isolation levels

An isolation level determines how data is isolated or locked from other processes while the data is being accessed.

Depending on the isolation level specified, DB2 performs locking differently.

DB2 supports following isolation levels:
    -Uncommitted read (UR)
    -Cursor stability (CS)
    -Read stability (RS)
　-Repeatable read (RR)

 

 

 

 

 Uncommitted Read (UR):
    -Lowest isolation level, highest concurrency
    -No row locks to read data
    -Dirty data reads are possible
　-Uncommitted reads, nonrepeatable read and phantom reads can occur

　

 

 

 Example: select * from t1 with UR

Cursor Stability (CS):
    -Default DB2 isolation level
    -In a result set, DB2 only locks the row currently being read
    -nonrepeatable read and phantom read scenarios are still possible
　　

 

 Example: select * from t2

Read Stability (RS):
    -Not only the current row but also the complete result set
    -This ensures that within same transaction, rows that have been previously read cannot be altered by other applications
    -phantom read scenarios are still possible
　　

 

 Example: select * from t2 with RS
           update t2 set c1=100 --It would not do, because the data was locked by RS
    ***Then use commit after select query. The update sql would auto commit.***
    
Repeatable Reads (RR):
    -Highest and most restrictive isolation level
    -gives lowest concurrency
    -Not only the rows in the result set but also the rows that are accessed to build the result set
　　

 

 Summary:
    1. Uncommitted Read (UR) => Dirty read, Fuzzy read, Phantom read
    2. Cursor Stability (CS) => Fuzzy read, Phantom read
    3. Read Stability (RS) => Phantom read
    4. Repeatable Read => No transactional anomalies

Isolation level & Concurrency are inversely proportional


When to use what type of isolation?
RR isolation level is used if we are executing large queries and we don't want concurrent transactions to happen.
RS is used when we want some concurrency to happen, yet we want qualified rows to remain stable for duration of our transactions.
CS is used when we want maximum concurrency to happen and we don't want any queries to see any uncommitted data.
UR is used if we are querying READ ONLY tables/views and it does not matter whether my query sees uncommitted value.

How to specify isolation level?
    -At statement level: SELECT * FROM customer WITH RR
    -Within current session:
            -SET CURRENT ISOLATION = {RR, RS, CS, UR}
            -SET ISOLATION {RR, RS, CS, UR}
Values current isolation
db2pd -db <DBNAME> -active

 

 

Depending on the operation requested, DBM can acquire locks on table rows, table blocks, table spaces, buffer pools, and databases.
DB2 users row-level locking by default
ALTER TABLE employee LOCKSIZE TABLE (To change to TABLE level lock)
ALTER TABLE employee LOCKSIZE ROW (To change back to ROW level lock)
CONNECT TO sample IN EXCLUSIVE MODE
QUIESCE TABLESPACES FOR TABLE <table_name> <Share/Intent to share/Exclusive/Reset>
LOCK TABLE <tablename/nickname> IN <SHARE/EXCLUSIVE> MODE
The only objects we can explicity lock are database, tables, and table spaces.

 

 

 

 

 

 

 Lock Waits
LOCKTIMEOUT: To define how long an application is going to wait for a lock
    db2 get db cfg | grep -I LOCKTIME
    *The value of locktime: -1 stands for infinite time. Ie., wait until a lock acquired or a deadlock is detected
    
Set lock timeour command: set lock timeout=20

Dead Lock:
Deadlock is a situation when two applications are waiting for locks that the other is holding.

 

 db2 get db cfg for testdb1 | grep -i deadlock

Internal operation when LOCKLIST is full.
Ex: Assign table level lock and release row level locks
db2 get db cfg for db1 | grep -i LOCKLIST

Lock escalation degrades performance by reducing database concurrency.
db2 get db cfg for db1 | grep -i MAXLOCKS
db2 get db cfg for db1 | grep -i MAXAPPLS

Diagnosing lock problem
    1. Checking locks:
        db2pd -db testdb1 -wlocks

 

     2. Fix locks:
        db2 "force application (26)"
        
    3. Check locks by statements
        SELECT AGENT_ID, substr(TABNAME,1,30) TABNAME, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_STATUS FROM TABLE(SNAP_GET_LOCK('',-1)) as T where TABNAME IS NOT NULL

 

         SELECT AGENT_ID, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_STATUS, substr(TABNAME,1,30) TABNAME FROM SYSIBMADM.SNAPLOCK WHERE DBPARTITIONNUM=0

 

     4. Get locks, applications information
        db2pd -db testdb1 -wlocks -transactions -applications -dynamic