4 weeks ago - last edited 4 weeks ago
We've been having some deadlock problems where we get deadlock messages like this in the ASE server errorlog file (note the shared/exclusive address locks):
Deadlock Id 8827: Process (Familyid 0, Spid 278) was waiting for a 'shared address' lock at 0x0x7fbdda5ac978
but process (Familyid 0, Spid 730) already held a 'exclusive address' lock.
Deadlock Id 8827: Process (Familyid 0, Spid 730) was waiting for a 'exclusive page' lock on page 107591674 of table 'MyTable' , indid 4 in database 'MyDB'
but process (Familyid 0, Spid 278) already held a 'exclusive page' lock on it.
I don't remember hearing about "address locks" before. Looking at the docs, I found statements like:
"SAP ASE acquires address locks on the index pages of allpages-locked tables. These locks block access to data pages"
"Address locks are held on index pages of allpages-locked tables. Address locks can have serious impact, since a lock on an index page blocks access to all data pages pointed to by the index page"
Interestingly, in the Types of Locks in SAP ASE doc pages, there's no mention address locks. I couldn't find much in the "SAP for Me Knowledge [aka "notes"] Search" either
Anyway, we're thinking about converting the table at issue from All pages locking to Data [pages] Only Locking (DOL). The above comments seem to imply that "address locks" don't apply do DOL locked tables. Is that true?
It's not trivial to change the locking scheme for this table. The "alter table" command will have to rewrite the whole table to change the locking type (ie., peak space usage of 2x). This is a big and busy table, so we don't want to go to the trouble of changing to DOL locking if it won't help.
Thanks in advance
Ben
Request clarification before answering.
User | Count |
---|---|
74 | |
30 | |
9 | |
7 | |
7 | |
6 | |
6 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.