Sybase Question: Download Questions PDF
Why do my page locks not get escalated to a table lock after 200 locks?
Answer:
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.
* 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.
Download Sybase Interview Questions And Answers
PDF
| Previous Question | Next Question |
| How Do I Take Advantage of Table Partitioning with bcp in Sybase? | On startup, the transaction log of a database has filled and recovery has suspended, what can I do? |