4 Locking Mechanisms for Concurrency in DB2

The Locking and Concurrency are two different mechanisms. Both have a separate purpose. Both have inter dependency.

ON THIS PAGE

Why do you need Concurrency in database

The concurrency mechanism allows the user to wait while other user writing a row to the database. The Locking mechanism, on the other hand, is to apply a lock to maintain data concurrency.

Locking

CONNECT TO sample IN EXCLUSIVE MODE

The Exclusive mode means it prevents to carry out any operation. In this case, exclusive lock applied on ‘sample‘ database.

DB2 and its 4 Locks

Exclusive Lock
Share Lock
Update Lock

Concurrency allows at a time one user can make changes to the database. The concurrency not in place, you will get incorrect data. You can read complete concurrency issues here.

In DB2, you can have 4 isolation levels to take care of concurrency.

UR
CS
RS
RR

Note: When you select ‘Blank’ it applies default isolation level.

Locks are bound to database. The isolation levels are not bound to the database. User can select type of isolation level depending on the requirement.

SQL Query Example with Isolation Level and Locks

SELECT empno, lastname, firstnme
  FROM employee
 WHERE deptno='A01'
  WITH RR USE AND KEEP EXCLUSIVE LOCKS

Prime Points on Locks

Locking granularity in Database can be applied either on

Database
Table Space
Table
Row

The locks applied on rows are taken care by isolation levels.

Dead Lock

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

Command to know which applications locked

Sometimes due to, locking mechanism, you cannot go ahead with your work. If you want to know which applications are locked, you need to issue list command.

>db2 list applications

Command to release locks

>db2 force application (208)

Ideas you need to avoid Locking

Use proper isolation level.
Use COMMIT as frequent as possible
Use FETCH ONLY to avoid exclusive locks.
In Cursors mention clearly the reason you are created. For example to read or update.

Why Locks You Need

Locks are required to prevent another transaction from reading inconsistent data.

Read more on Locking

Locks are controlled by ‘Database Manager’.

How Concurrency Acheived

By using Locks and Isolation levels you can achieve concurrency.

Locksize option you can use depending on your requirement. The size of the lock can be

Table Space
Table
Page
Row
Any
LOB
XML

Conclusion

To maintain concurrency in DB2 you need locks. You have option to choose size of the lock. Data must be consistent and usable. Else there is no point having database.

Srini

Data Engineer with deep AI and Generative AI expertise, crafting high-performance data pipelines in PySpark, Databricks, and SQL. Skilled in Python, AWS, and Linux—building scalable, cloud-native solutions for smart applications.