What is lock escalation? Provide an example and explanation of lock escalation.
Some database vendors like DB2, Sybase, and SQL Server support what is known as lock escalation. Lock escalation occurs when database locks are raised to higher “levels”, because a particular database session places an increasing number of locks on the same types of database objects.
An example of lock escalation
For example, if there are multiple locks at the row level for the same table, then it might make sense to ‘escalate’ the lock and just lock the entire table instead. This reduces the overall number of locks, and may substantially improve database performance.
Does lock escalation happen automatically?
Yes, lock escalation is built into RDBMS’s, and they determine when it’s performed. However, there are some DBMS products that allow you to set flags on a table so that you can disable lock escalation for that particular table, or even have the lock always escalate to a table level lock (from a lower level like a row or page).
Here’s what the SQL would look like in SQL Server 2008 to disable lock escalation:
ALTER TABLE some_table SET (LOCK_ESCALATION = DISABLE)
And here’s what the SQL would look like to have the lock always escalate to a table level lock:
ALTER TABLE some_table SET (LOCK_ESCALATION = TABLE)
What is the point of lock escalation? When would lock escalation be necessary?
Because of the fact that having small amounts of data constantly being locked and unlocked can cause some significant overhead, performing lock escalation – and raising the lock to a higher level – can really improve database performance. For example, if we have many locks on rows from the same table, it might just make sense to escalate the lock to the table level, and this could improve performance. So, remember that lock escalation exists to improve performance.