DBA > Job Interview Questions > Sybase Interview Questions and Answers

Why am I running out of locks on the replicate s

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

Why am I running out of locks on the replicate side in Sybase ?

Sybase replication works by taking each transaction that occurs in the primary dataserver and applying to the replicate. Since replication works on the transaction log, a single, atomic, update on the primary side that updates a million rows will be translated into a million single row updates. This may seem very strange but is a simple consequence of how it works. On the primary, this million row update will attempt to escalate the locks that it has taken out to an exclusive table lock. However, on the replicate side each row is updated individually, much as if they were being updated within a cursor loop. Now, Sybase only tries to escalate locks from a single atomic statement (see ASE Qx.y), so it will never try to escalate the lock. However, since the updates are taking place within a single transaction, Sybase will need to take out enough page locks to lock the million rows.

So, how much should you increase the locks parameter on the replicate side? A good rule of thumb might be double it or add 40,000 whichever is the larger. This has certainly worked for us.

(Continued on next question...)

Other Job Interview Questions