Locking Vs Concurrency top database mechanism you need to know

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

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.

Types of Locks in DBMS

  • Exclusive Lock
  • Share Lock
  • Update Lock
Different popular locks in DB2

Why Concurrency you need

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.

  1. UR
  2. CS
  3. RS
  4. 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 isoloation level depending on the requirement.

Locking
LOCKING VS CONCURRENCY

SQL Query Example with Isolation Level and Locks

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

Tricky 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

  1. Use proper isolation level.
  2. Use COMMIT as frequent as possible
  3. Use FETCH ONLY to avoid exclusive locks.
  4. 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.

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

Bottom line

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.

Advertisements