IBPhoenix Research

Lock Promotion and Demotion

by Ann Harrison

In a system that demotes locks, a transaction takes an initial lock on the whole table. When another transaction wants to use the table in an inconsistent way, the first transaction acquires locks on pages that contain the records it actually wrote or read plus any index access path locks, and releases the table lock. If releasing the table lock eliminates the conflict, well and good. If not, the transaction acquires locks on the records it wrote or read on the conflicting page and releases the page lock. If the database system includes another, larger, identifiable group of records, like a sector, the demotion goes through more levels.

In a system that promotes locks, a transaction starts by locking records. If it reads all the records on a page, it locks the page and releases the record locks. If it reads all the pages in a table, it locks the table and releases the page locks. Some systems promote locks when some percent (less than 100) of the records on a page or pages in a table have been accessed. In that case, the system may maintain a shadow copy of lower level locks to allow lock demotion on conflict.

Lock promotion and demotion are a fascinating exercise, but their only reason for existence is that transactions read a lot of records and using record locks to maintain serializability is a major overhead cost.