What is a DB2 Lock? It is a Mechanism provided by DB2 to maintain the access to different database objects.
What is Concurrency in DB2? Concurrency is a way which enables more than one application to access the same database object at the same time. Locks and Concurrency control is the most important aspect in database design. This topic covers these in detail.
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.
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.
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.
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 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.
Duration Of Lock decides how much time a lock can be acquired on a particular object.
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
In Shared Lock mode, lock owner and other concurrent processes can not update the database(Page or row) as it gives the read only access. If more than one application is trying to read the same data, then it allows them to access the data but if any transaction is trying to update this lock mode prevents the update until the read operations are done thus it prevents the data accuracy. While updating the data if any other transaction tries to read the data
It gives exclusive access on that database object thus it gives access to no other transaction while this is in effect. This lock mode allows read & write. While it is updating any data item any other transaction requests for a read access, this lock mode prevents that read. Only after the update, it allows a read. Exclusive lock will be released after the update.
Intent locks are the automatic locks that IRLM acquires whenever necessary. For example if a page level Share lock is requested, then S lock will be applied on that page as requested and along with that IRLM acquires a Intent share lock at Table level as well. This is required because, at the same time if any other application is looking for Exclusive lock at the table level, then it becomes difficult ad overhead to check each and every page/row for the locks acquired and thus take appropriate action. So if there is a Intent lock at Table level, IRLM easily identifies that there is some other lock at low level and decides whether to grant that lock or put it on Hold.
Intent locks can be applied at Table, Table space and partition level.