DB2 Locks and Importance of Concurrency Control

Locks & Concurrency:

Concurrency is the feature that makes multiple applications or users to access the resources at the same time without affecting each other’s work. It makes the applications/users to share the resources interactively at the same time by using the locking concept.

Concurrency control is important as without the proper concurrency the applications may end with invalid data access.

Concurrency controls the applications from Lost updates, uncommitted data & unrepeatable reads, Phantom reads. We will discuss in detail about these later in this topic.

Here are some examples that show the importance of concurrency in DB2 applications.

Lost updates:

Suppose two applications A & B are accessing and updating the TableA simultaneously in microsecond’s difference. Both the applications read the data and First applications A has accessed row1 and updated it. Later application B accesses the same row and updated it with some other values. It shows that A updates has LOST. This is the Lost Updates scenario.

So to prevent this, the table needs to be locked properly so as to prevent the Application A updates and B to access the updated value. If B accesses the updated value of A, its calculations would be based on the current value so the results would be accurate.

Uncommitted reads:

In the same above example if A has updated the data and it has not committed yet, and B accessed the uncommitted data and produces some results. But what happens if the A has rolled back its updates? Whatever the results produced by B are invalid. This is uncommitted reads scenario.

Uncommitted reads needs to be prevented as it may produce the invalid results, this can be prevented by using the proper concurrency control.

Unrepeatable reads:

Some processes expect the same data values to be present for the multiple reads. If A has read one row and goes to process other SQLs. When it comes back to access the same first row, it should find the same values. But here if B updates that row, application A would not find the same values.

So depending upon the application requirement the concurrency needs to be controlled. This can be controlled at the time of database design or table design or application program level.

As said earlier, concurrency is achieved by using the locking concept.


Lock Definition:

Lock prevents the access to a Db2 Object from multiple applications at the same time and it decide how/when one object can be accessed by other applications which is being accessed by one application/process.
Locks prevent the applications from accessing uncommitted data, lost updates & unrepeatable reads are also avoided by using the proper locking mechanism.

There are three major concepts of Locking

  • Lock Size
  • Lock Mode
  • Duration of the lock

Lock Size tells the object on which lock is to be applied. In Db2 the Lock Size can be table space, Partition, table, page or Row level.
Of course here locksize selection depends on the database structure, i.e. all locksizes are not appropriate in all situations, like partition lock size is only applicable to partitioned table spaces.
As the name implies, here the Table space level lock is the highest level which controls most of the data and locks the entire table space.

Lock Mode determines the type of access the applications (lock owner) gets over the DB2 object and it decides what access the other applications can have on the locked object. Three types of lock modes are available

  • Shared
  • Update
  • Exclusive

Duration Of Lock decides how much time a lock can be acquired on a particular object.

we will discuss more on this in next article soon.

Leave a Reply

Your email address will not be published. Required fields are marked *