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.
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
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.
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.
SQL Query Example with Isolation Level and Locks
SELECT empno, lastname, firstnme
WITH RR USE AND KEEP EXCLUSIVE LOCKS
Tricky Points on Locks
Locking granularity in Database can be applied either on
- Table Space
The locks applied on rows are taken care by isolation levels.
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.
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
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.