Can you explain the mechanism of lock escalation in SQL database management systems?

Lock escalation in DBMS is designed to optimize lock management and prevent excessive resource consumption. When a transaction holds many fine-grained locks (e.g., on rows), the DBMS can automatically escalate to a coarser lock (e.g., on a page or entire table). This reduces overhead in managing many small locks and decreases deadlock chances.

Example: if a transaction locks thousands of rows in a table, instead of maintaining thousands of row locks, the DBMS escalates to a table lock.

In SQL Server, for instance, escalation occurs automatically when a threshold of locks is reached, but it can be configured or disabled.