More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Why do my page locks not get escalated to a table lock after 200 locks?
Several reasons why this may be happening.
* Are you doing the updates from within a cursor?
The lock promotion only happens if you are attempting to take out 200 locks in a single operation ie a single insert, update or delete. If you continually loop over a table using a cursor, locking one row at time, the lock promotion never fires. Either use an explicit mechanism to lock the whole table, if that is required, or remove the cursor replacing it with an appropriate join.
* A single operation is failing to escalate?
Even if you are performing a single insert, update or delete, Sybase only attempts to lock the whole table when the lock escalation point is reached. If this attempt fails because there is another lock which prevents the escalation, the attempt is aborted and individual page locking continues.
(Continued on next question...)
