What is database locking?
When we say we have a database lock over a record, a set of records, a database page, a database table, a table-space, etc. we refer to the fact that the database prevents any changes on the locked values. When a set of records are locked any transaction trying to change those data will be queued until the lock is released. The number of acquired locks and proximity of data being locked determine whether the lock should scale up and go to an upper level, for example from record level to page level, or it should shrink from a table level lock to a page level lock. When the lock goes up, it prevents changes to a larger number of records; for example, when a row level lock, which locks a few hundreds of records, scales to table level lock, which can lock millions of records. A database manages these locks in the most resource efficient way. For example when we have 100 locks on a table, the database may up the lock and lock the entire table for new transactions instead of keeping hundreds
