It can be potentially troublesome when DB2 lock promotion and escalation occur. These situations can greatly reduce the availability of concurrent access to data.First let's look at lock promotion. When binding a program with an ISOLATION level of RR, the optimizer sometimes decides that table space locks will perform better than page locks. As such, the optimizer promotes the locking level to table space locking, regardless of the LOCKSIZE specified in the DDL. This process is called lock promotion.When you set the LOCKSIZE bind parameter to ANY,...
Wednesday, 19 June 2013
Monday, 17 June 2013
DB2 Locking, Part 11: Data Sharing Global Lock Management
Posted on 09:07 by Unknown
Data sharing adds an additional level of complexity to the DB2 locking strategies and techniques we have been discussing in this series. Because data sharing group members can access any object from any member in the group, a global locking mechanism is required. It is handled by the lock structure defined in the coupling facility. The lock structure is charged with managing inter-member locking. Without a global lock management process, data integrity problems could occur when one member attempts to read (or change) data that is in the process...
Monday, 10 June 2013
DB2 Locking, Part 10: Know Your ISOLATION Levels
Posted on 09:39 by Unknown
DB2 provides a way to change the way that a program or SQL statement acquires locks. That way is known as the isolation level and it can be set to specify the locking behavior for a transaction or statement. Standard SQL defines four isolation levels that can be set using the SET TRANSACTION ISOLATION LEVEL statement: SerializableRepeatable readRead committedRead uncommittedThe isolation level determines the mode of page or row locking implemented by the program as it runs. DB2 supports a variation of the standard isolation levels. DB2 implements...
Monday, 3 June 2013
DB2 Locking, Part 9: Avoid Bachelor Programming Syndrome
Posted on 14:14 by Unknown
Unless you plan for, and issue regular COMMITs in your DB2 application programs, you will be causing locking problems. It is important for every programmer to issue COMMIT statements in all application programs where data is modified (INSERT, UPDATE, and DELETE). A COMMIT externalizes the modifications that occurred in the program since the beginning of the program or the last COMMIT. A COMMIT ensures that all modifications have been physically applied to the database, thereby ensuring data integrity and recoverability.Failing to code COMMITs in...
Subscribe to:
Posts (Atom)
