This chapter discussed how concurrency, or serialization, is controlled. To protect the data as it is being modified, rules are established and the changes are grouped into units of work. The updated data is made permanent by the COMMIT statement or removed by the ROLLBACK statement. The rules of concurrency are determined by the isolation level, the lock size, and the rules of lock duration.
DB2 implements the isolation-level semantics of data access by implicitly acquiring locks on behalf of applications. Applications can decide to lock a resource for exclusive or share modes. The resources that can be locked are the row, page, table, table space, and partition.
If a requested lock is more restrictive and if another application already has the resource locked, a wait on the release of the lock will occur. The amount of time an application will wait is determined by systemwide parameters for both resource timeout and deadlocks.
If multiple applications require access to data that is held by other applications, a deadlock scenario can occur. DB2 will detect the occurrence of any deadlocks and force one of the transactions to roll back. Every lock requested requires memory in the IRLM, and the amount of lock storage is configurable by using IRLM parameters.
Many opportunities exist for avoiding excessive locks and lock problems. These opportunities come through proper database and application designs.