Lock Types

DB2 does not use just one kind of lock. It uses different lock modes depending on what the program wants to do with the data. The mode decides who else can touch the data at the same time.

S, U and X locks

• S (Share): the owner and other programs can only read the locked data, nobody can change it. Other programs can also take S or U locks on it.
• U (Update): the owner can read but not change yet. The owner can promote the lock to X and then update. Other programs may read with S, but a second U lock is not allowed.
• X (Exclusive): the owner can read and change the data. No other program gets any access to it until the lock is released.

Intent locks: IS, IX and SIX

• Intent locks are taken on the higher level object (table or tablespace) to announce what the program plans to do with the lower level (pages or rows).
• IS (Intent Share): the owner only reads data in the table or tablespace. Other programs can read locked pages, and can read and update other pages.
• IX (Intent Exclusive): the owner will read and update in the table or tablespace. Other programs can only read and update other pages.
• SIX (Share with Intent Exclusive): the owner reads the whole table but updates only some pages. Other programs can only read the other pages.
• Example:-
  -- let everyone else read while you read LOCK TABLE EMP IN SHARE MODE;

Lock compatibility and promotion

• Two locks are compatible when both owners can hold them on the same data at the same time. S is compatible with S and U, but X is compatible with nothing.
• The compatibility rules are shown in the matrix below. Y means the requested lock is granted, N means the requester waits.
• 
• When a program needs a stronger lock than it already holds, for example moving from S to X, DB2 upgrades it. This is called lock promotion.
• Promotion can cause a deadlock: if two programs both hold S and both try to promote to X, each waits on the other.