So another irritating question that often gets asked – and answered incorrectly by all the self-appointed web gurus/trolls that lurk on the newsgroups is “Which is faster – APL or DOL locking?” Being the usual frustrating person I am, I typically answer initial questions with a question of my own – “Faster at what?” Usually the stock answer from anybody who really knows what they are doing is “It depends” – but my way of answering is intended to get the person to maybe think a little bit about why they are asking the question in the first place – which is the key to the whole point.
The problem is that when the question is asked to Sybase engineering, you need to understand the context in which they are replying are from a internals locking overhead and not necessarily the fastest for throughput or response time – and hence the recommendations in the Sybase manuals to use datapage locking. Locking in Sybase is implemented using a lock hashtable and a simple serial lock chain. Just for grins, assume that we start with 10 hash buckets and the hash function is a basic mod() function. If we want an exclusive lock on page 25, 25%10=5 – so we would simply do a linear scan of the lock chain in bucket 5. If we find a conflicting lock, we know we have to block. However, in any case, we need to add our lock to the lock chain, so we grab the spinlock on the lock hash bucket, modify the lock chain, and release the lock. The problem of course is that if the lock chain is long, the serial scan can take too long comparatively. For example, if I have 100,000 locks and only 10 hash buckets, each bucket would have 10,000 locks that I would have to check to see if there was a conflict. Mega-Ouch. Consequently Sybase ASE starts with a default ‘lock hashtable size’ of 2048. A quick check of Sybase’s P&T guide will also state that you should never let the lock chain length get greater than 5 – which is visible via sp_sysmon (one of the few things it is still useful for) from the section:

Lock Management
---------------
…
Lock Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------

Aha! We see that ASE actually maintains not one but two hashtables – one for table level locks and one for page & row locks. Now this is where a tad bit of tuning advice comes in handy. If this was the peak period for this system, likely no tuning for the lock manager is really necessary. But if that 2.1 crept up to 5 or 6 or higher, that might be the point to start thinking about increasing the ‘lock table spinlock ratio’ server configuration parameter. It is sometimes confusing, but the table locks are maintained in a separate form of hash table which is controlled by the ‘lock table spinlock ratio’ – in which the server uses an non-tunable 101 hash buckets to track all the table level locks. With a default of 20 for the ‘lock table spinlock ratio’, this defines 6 spinlocks by default for the table level locks – which may explain why the chain length seems a little longer than would ordinarily be expected. But then if you think about the fact that #temp tables typically cause table locks, it is easily explainable.
But what if it was the Page/Row hashtable that was showing a longer chain length? The page/row lock hashtable is controlled by the server configuration ‘lock hashtable size’ which unlike table level lock hashtables, you can tune both the hashtable size and the spinlock contention. In the above example, the spinlock contention is effectively non-existent. But if there was any at all – even 1 or 2% – that is when you start thinking about changing the server configuration for ‘lock spinlock ratio’. By default, ASE starts with a ‘lock spinlock ratio’ of 85 – which means a single spinlock guards 85 of the hash buckets – those 2048 buckets in the ‘lock hastable size’…..which some quick math (or reading the manuals) points out that by default there are only 26 spinlocks guarding all the hashbuckets for ASE. So if you start to see spinlock contention – simply decreasing the ‘lock spinlock ratio’ to say 32 means that instead of 26 spinlocks, you would have 64 spinlocks – not a huge number – but it can make a huge difference if there is contention. Another way to increase the number of spinlocks is to simply increase the ‘lock hashtable size’. If you think about it, ASE starts with a default of 10,000 locks – which with ~2,000 hash buckets would result in an average of 5 locks per lock chain if evenly distributed. So if we tune the server to increase the number of locks to something more realistic – such as 250,000 – if we leave the ‘lock hashtable size’ at 2048 – we could easily hit a lockchain size of 125 if all the locks were in use and evenly distributed. Ouch. Keeping that ratio in mind – it might not be a bad idea to increase the ‘lock hashtable size’ to ‘number of locks’/5 – or setting the ‘lock hashtable size’ to 50,000 – which at the default ‘lock spinlock ratio’ of 85 would result in ~600 spinlocks. By now many of you are desperately hunting for your config files as you know you have bumped ‘number of locks’ to the moon – but didn’t change the ‘lock hashtable size’ and realize the contention you might be getting on spinlocks is due more the ‘lock hashtable size’ vs. the ‘lock spinlock ratio’….or even if no contention, you are putting a bit of a drag on locking due to the length of the lock chains. By the way – all of this is in the docs.
So what does all of this have to do with which is faster – APL or DOL. The answer – in theory – is that with datarows locking, you need to do the whole spinlock/lock chain search for every row where as with allpage or datapage locking you would only need to get one lock for the page. Hmmmmm. Nice theory. Let’s take a simple example – which locking scheme is the fastest for:
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
go

The answer is none of them are necessarily faster than any other. In allpages, datapages, and datarows locking – the same number of locks would need to be acquired as the above are atomic inserts in which each independent insert is a separate transaction and would go through lock search and acquisition for each and every row. Now there is a difference with:
begin tran
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
insert into mytable values (…)
commit tran
go

But…the answer is “it depends”. If the table is a heap table and if we assume 4 rows per page then it would only be 2 locks for all 8 rows. But if the table had a clustered index that deliberately scattered the rows to reduce contention (as a number of customers have done so), the answer is it would take 8 page locks for the 8 unique pages. Hah! And you thought you were doing yourself a favor when you scattered the inserts!
Okay, okay, grumble, grumble – I do admit that for selects – especially range scans or the abhorred table scans, the page level locking could speed the process of shared locks. But I have never gotten panic calls about system performance due to locking speed – contention: yes. Bad query plans: yes. Locking speed: nope.
So, what do I recommend. Considering that I spend more time in the field and get to worry more about concurrency and multi-user situations and less on the server internals, I prefer and recommend “datarows locking”. There are many reasons for this:
• Almost completely and immediately eliminates contention. Period. Enough said.
• Allows locking optimizations such as “uncommitted insert bypass” and “pseudo column locking”
• Supports unique locking features such as “readpast locking” (unfortunately a topic for another day as this blog will be long enough just addressing the question).
But good grief. Dare to mention datarows locking to a lot of DBA’s and they immediately bring up all the extra maintenance, the reorgs and all the other headaches they have heard about or experienced. But is all that extra maintenance really necessary? Or is a lack of planning???
One of the more commonly cited reasons is the ‘forwarded rows’ syndrome. By why does this happen to begin with? As you insert a row on a blank page, it starts at the top of the page after the page header. The next row that is inserted starts contiguously after it. So if the first row inserted consumes 200 bytes, then it starts at byte 117 (for simplicity sake – the page header for DOL pages is 116 bytes since you get 1932 bytes per page) and ends at byte 316 – so the next row starts at byte 317 – and if it is 200 bytes, it will run to byte 516….and so forth. Now, let’s say someone updates that first row – and now the row size is 300 bytes instead of 200 bytes. Uh oh, we have a problem. Now if we did what Oracle whines about Sybase doing (“escalating” locks), we would escalate the row lock to a page lock to move the second and subsequent rows down the page so the first row would fit. Of course, this escalation to a page lock would result in more contention. So, instead, we “forward” the row to another location. Of course there is a way to avoid all of this. For DOL tables, if you set the expected row size via the create table () with exp_row_size=### or sp_chgattribute
, exp_row_size, ### – then ASE reserves the space for each row. My rule of thumb is not to use the average – but to pick something close to 90-95% of the rowsize – or even 100%. Voila. Just that simple. One little attribute change and forwarded rows become an absolute rarity.
Now the second complaint is the empty space that is unclaimed after deletes. The cause for this was to actually SPEED up the deletes as compared to APL tables. If you delete a row in APL, it reclaims the space immediately. Rows on the page are immediately compacted to remain contiguous. If it is the last row on the page, the page will be reclaimed as part of the delete transaction – which means the delete takes a tad bit longer. For DOL, the space reclamation is done by the housekeeper garbage collection process in ASE asynchronously to the transaction. So the deleted row is simply “marked” for deletion. If the space can be reused (see later paragraph on clustered indexes and DOL), it will be – but if the last row is deleted, rather than the transaction being held up – the page is not reclaimed until the housekeeper gets around to it. Now this is again where some ASE default server configs defaults cause havoc on an otherwise well behaved system. Take the following paragraph – which is a direct quote from the ASE product manuals (as well as a subject of a whitepaper on housekeeper tuning) – specifically the 12.5.1 System Admin Guide (vol 1), which stated under the section on config parameters for ‘enable housekeeper GC’:

To configure Adaptive Server for garbage collection task, use:
sp_configure “enable housekeeper GC”, value
For example, enter:
sp_configure “enable housekeeper GC”, 4
The following are the valid values for enable housekeeper GC configuration parameter:
• 0 – disables the housekeeper garbage collection task, but enables lazy garbage collection by the delete command. You must use reorg reclaim_space to deallocate empty pages. This is the cheapest option with the lowest performance impact, but it may cause performance problems if many empty pages accumulate. Sybase does not recommend using this value.
• 1 – enables lazy garbage collection, by both the housekeeper garbage collection task and the delete command. This is the default value. If more empty pages accumulate than your application allows, consider options 4 or 5. You can use the optdiag utility to obtain statistics of empty pages.
• 2 – reserved for future use.
• 3 – reserved for future use.
• 4 – enables aggressive garbage collection for both the housekeeper garbage collection task and the delete command. This option is the most effective, but the delete command is the most expensive. This option is ideal if the deletes on your DOL tables are in a batch.
• 5 – enables aggressive garbage collection for the housekeeper, and lazy garbage collection by delete. This option is less expensive for deletes than option 4. This option is suitable when deletes are caused by concurrent transactions.
Using the reorg command
Garbage collection is most effective when you set enable housekeeper GC to 4 or 5. Sybase recommends that you set the parameter value to 5. [bold and italics mine] However, if performance considerations prevent setting this parameter to 4 or 5, and you have an accumulation of empty pages, run reorg on the affected tables. You can obtain statistics on empty pages through the optdiag utility.
When the server is shut down or crashes, requests to deallocate pages that the housekeeper garbage collection task has not yet serviced are lost. These pages, empty but not deallocated by the housekeeper garbage collection task remain allocated until you remove them by running reorg.

Now that is straight from the ASE 12.5.1 manuals circa 2003….6 years ago (so no excuses about not knowing this factoid). For some goofy reason the 15.0 manuals dropped the last two paragraphs (doc bug! doc bug!) so newbies get a grace period.
So if doing the usual batch purge jobs at night, the answer is to set the ‘enable housekeeper GC’ configuration to 4 -possibly 5 if also deleting in transactions during the day – but not the lame 1 that serves as the default that everyone leaves it at. The default is because the default locking scheme is APL and nothing more than 1 is necessary. The original 12.5.0.3 white paper provide a bit more detail stating for the setting of 1 that:

• 1 – Enables lazy garbage collection by both the housekeeper garbage collection task and the delete command. This is the default value. Empty pages may not be processed due to non-optimal lazy evaluation when there are long running transactions. This slowdown in empty page processing could result in HK GC queue overflow,[bold italics mine] causing empty pages. If more empty pages accumulate than your application allows, consider using a value of 4 or 5 (described below). You can use the optdiag utility to obtain statistics on empty pages.

Before we get to the HK GC queue overflow that I bolded and italicized (or the shutdown/crash comments), let’s take a look at optdiag. Here is a sample output:

Data page count: 3065907
Empty data page count: 8
Data row count: 5990014.0000000000000000
Forwarded row count: 32418.0000000000000000
Deleted row count: 35312.0000000000000000
Data page CR count: 383495.0000000000000000
OAM + allocation page count: 76852
First extent data pages: 89170
Data row size: 898.3669699918971219

Ouch. 32,000 forwarded rows and 35,000 deleted rows. Sooo….what happens – why is a reorg necessary?? The answer is that the HouseKeeper GC process maintains a non-tunable queue of pending requests such as deletes. If the HouseKeeper GC is not running aggressively enough, this queue overflows and some of the requests will be “dropped” or lost – this is known as a “HK GC queue overflow”. Worse yet, if you shutdown the server (or it crashes), since the HK GC queue is an in-memory only queue, the entire queue is lost – and the only way to reclaim all this “lost space” in either case is to run a reorg.
Of course, a ounce of prevention is always better than a pound of cure. In ASE 15.0.2 and higher, the MDA tables monEngine, monOpenObjectActivity and monOpenPartitionActivity track the number of HK GC queue overflows via columns in the schema such as:
create table monOpenObjectActivity (
DBID int,
ObjectID int,
IndexID int,
DBName varchar(30) NULL,
ObjectName varchar(30) NULL,
LogicalReads int NULL,
PhysicalReads int NULL,
APFReads int NULL,
PagesRead int NULL,
PhysicalWrites int NULL,
PagesWritten int NULL,
RowsInserted int NULL,
RowsDeleted int NULL,
RowsUpdated int NULL,
Operations int NULL,
LockRequests int NULL,
LockWaits int NULL,
OptSelectCount int NULL,
LastOptSelectDate datetime NULL,
UsedCount int NULL,
LastUsedDate datetime NULL,
HkgcRequests int NULL,
HkgcPending int NULL,
HkgcOverflows int NULL,
)
external procedure
at "@SERVER@...$monOpenObjectActivity"

Yehaw!!! Not only can we detect how many overflows actually occur, we can tell which tables they actually occur on….and therefore which ones we need to run a reorg on. And by a quick look at the HkgcPending – we can determine if we should wait a bit before shutting down. Oh, by the way, as I mentioned, monOpenPartitionActivity includes the same columns – so you could further narrow it to specific partitions – which could reduce the need to run reorg. If it is the oldest partition – and the one you are likely going to drop in a day or two – why bother?
The short of all the above: if you tune ‘enable housekeeper GC’ correctly, reorgs may not be needed anymore. And if you check the HKgcPending from monEngine before a shutdown, you can prevent it from happening as well. One more nail in the coffin of DOL naysayers.
And then there is the internet troll that keeps stating that DOL tables are heap tables and clustered indexes are not used effectively as placement indexes. The same troll complains because his submissions never get accepted at TechWave. Small wonder. The real facts are different…and generally TechWave prefers technically accurate presentations. What is clearly stated in the manuals is that:

Unlike clustered indexes on allpages-locked tables, the data rows in a data-only-locked table are not necessarily maintained in exact order by the key. Instead, the index directs the placement of rows to pages that have adjacent or nearby keys.
When a row needs to be inserted in a data-only-locked table with a clustered index, the insert uses the clustered index key just before the value to be inserted. The index pointers are used to find that page, and the row is inserted on the page if there is room. If there is not room, the row is inserted on a page in the same allocation unit, or on another allocation unit already used by the table.
To provide nearby space for maintaining data clustering during inserts and updates to data-only-locked tables, you can set space management properties to provide space on pages (using fillfactor and exp_row_size) or on allocation units (using reservepagegap).

Not exactly a heap table which always inserts on the last page. What it states is rather than going through the expense of page splits (which were an ugly synchronous IO operation until 15.0.2), overflow chains (the original version of row forwarding if you will) or escalating to a page lock so it can rewrite every row on a page to maintain the order, we will simply try to place the row “near” where it belongs. Yes, you have to run reorg rebuild to get the table back in precise clustered index order – somehow I find that not so different than all the times I used to have to drop and recreate the clustered index on APL tables to fix overflow chains, reclaim space from deleted rows, etc. – all for the same reason (I think) – trying to get the data cluster ratio to something “perfect” to reduce the IO’s used for range queries….although I have learned something over time since my neophyte days. 99.999999999% of the performance problems are not resolved by reducing the IO’s for a range query from 100 to 50. It is by eliminating the contention and eliminating the table scans – and finding the bad query plans.
Save yourself some grief – use datarows locking. Use expected row size. Set ‘enable housekeeper GC’ to 4 or 5. Watch the HkgcPending in MDA prior to a shutdown. And tune out the troll.