Locking parameters in transaction processing in SQL: Lock size, timeout, escalation, number of locks

Here are the locking parameters which can be set up for improving performance of a database. These parameters can be set up by the database administrator.

Lock size: A locking size can be set up for a database like row level locking, table level locking, and  page level locking.

Lock timeout: Timeout is used to relieve a transaction from waiting indefinitely. It avoids deadlock between the transactions. When a transaction is timed out an error code is generated. Time out for transactions can be set up as per the needs of the database.

Lock escalation: Locking escalation is done to improve the performance of a database. Smaller level locks are usually escalated to larger level locks. For Example: many row level locks can be escalated to a page level lock for increasing performance of the database.

Number of locks: Administrator of a database can set up limits in the number of locks. It can be st up based on the requirements of a database.

Leave a Reply