Sybase locking strategy

Question

Adaptive Server provides these locking schemes:

• Allpages locking, which locks data pages and index pages
• Datapages locking, which locks only data pages
• Datarows locking, which locks only data rows

My question is that if the number of locks depends on the locking strategy. Some people say that many locks is a sign of bad application implementation or something similar. What I mean is that the datarows strategy will need more locks than for example datapages since datapage covers many rows under one lock. I'm not entirely sure of my assumptions.

I currently have 200k and this is still not enough.

Recently changed the locking strategy on my tables to datarows to improve the performance and since then I've been increasing the locks.

Sybase documentation says the range is within 1000–2147483647.

Why not just setting it to 2147483647?

Answer 1

Datapages and Datarows locking are better when there are a many processes accessing the database with quite many WRITE/UPDATE operation which take exclusive lock. With All-Pages locking, more pages are locked by one operation ( more than those being affected by the write operation ) and thus can block processes which want to access the data which is not effected by the write operation.

But if that table has datapage or datarow locking, the same write process will lock only the effected data and thus lock lesser amount of data and therefore provides better concurrency. But the Number of locks held by this writer process is more and thus more memory is required.

Say for example, a page has 10 rows and the write process effects five rows. If the process locks the page, it takes just One lock and blocks any process that wants to read any of the rest five rows.

If that table has datarow locking, then the writer process will take Five locks ( which means more memory ) and leaves the rest of five rows on page for any other process to read/write

Answer 2

Locks use memory, so setting to the maximum value could cause your ASE instance to run out of available memory. SAP Sybase has some performance and tuning documentation that may help you figure out how to better tune your application, and server.