In DB2, locks are mechanisms used to manage concurrent access to data in a database. They ensure data integrity and consistency by controlling how multiple users or processes interact with the data.

They prevent problems such as:

• Lost updates: Two users attempt to update the same data simultaneously.
• Dirty reads: One transaction reads data that hasn't been committed yet by another transaction.
• Inconsistent data: One transaction sees partial changes made by another transaction.

Locks ensure that transactions are isolated from each other, meaning that one process cannot interfere with another process's operation. The primary goal is to allow multiple users to access data concurrently while maintaining data integrity.

Locking Modes -

DB2 uses different locking modes to determine the transaction access level to the data. Below are the main locking modes used in DB2:

Shared Lock (S) -

• Used when a transaction only reads data (no updates).
• Multiple transactions can hold a shared lock on the same data simultaneously.
• Prevents other transactions from acquiring an exclusive lock on the same data.

Example - If two users are viewing the same bank account balance, both will have shared locks on the data.

Update Lock (U) -

• An intermediate lock between a shared lock and an exclusive lock.
• Used when a transaction first reads data (shared mode) and intends to update it later.
• Prevents deadlocks by avoiding two processes from waiting for each other to release a shared lock.

Example - A user reads the product inventory to decide whether to update the stock level. Initially, the transaction holds an update lock to later escalate to an exclusive lock.

Exclusive Lock (X) -

• Used when a transaction updates or deletes data.
• No other transactions can read (shared lock) or update (exclusive lock) the data at the same time.

Example - When a user updates an employee's salary, DB2 places an exclusive lock on that record until the update is committed.

Intent Locks (IS, IX, SIX) -

It indicates to place a shared or exclusive lock at a lower level, such as on specific rows within a table. These locks allow DB2 to manage the level of detail in the data (such as locking a few rows vs. the whole table).

• Intent Shared (IS): Indicates the intention to set shared locks on individual rows.
• Intent Exclusive (IX): Indicates the intention to set exclusive locks on individual rows.
• Shared Intent Exclusive (SIX): A combination lock, indicating a shared lock on the table but an exclusive lock on specific rows.

Example - An IS lock on a table signals that the process will read rows in the table but not update them.

Lock Compatibility Matrix -

The compatibility matrix shows which types of locks can be together on the same data. For example, two transactions can both hold shared locks (S), but a shared lock cannot be combined with an exclusive lock (X).

Lock Type	S (Shared)	U (Update)	X (Exclusive)
S (Shared)	Yes	Yes	No
U (Update)	Yes	No	No
X (Exclusive)	No	No	No

Explanation:

• Yes: These locks can be together on the same data.
• No: These locks are incompatible; one transaction must wait or be rolled back.

Example -

Scenario - Two users are accessing the same customer record.

• User A views the customer's profile (Shared Lock).
• User B wants to edit the customer's phone number (Exclusive Lock).

Solution -

• Step 1: User A acquires a shared lock to read the data.
• Step 2: User B tries to acquire an exclusive lock to update the data.
• Outcome: User B should wait until User A releases the shared lock, ensuring data integrity.

Lock Granularity: Table-Level vs. Row-Level Locks

• Table-Level Lock: Locks the entire table to prevent any access to rows until the operation completes.
• Row-Level Lock: Locks only the specific row being accessed, allowing other rows to be accessed by other transactions.

Example -

• Table-Level Lock: Useful during large updates or batch processes.
• Row-Level Lock: Used for individual updates, like changing a customer's address.

How DB2 Manages Deadlocks -

A deadlock occurs when two transactions wait for each other to release locks. DB2 has a built-in deadlock detection mechanism that:

• Detects deadlocks and automatically rolls back one of the transactions to break the deadlock.
• Uses update locks (U) to reduce the chances of deadlocks.

---