Unanswered: What might explain unreleased row locks?
exclusive row (Ex_row) locks still lingering around after one would expect them to be released.
We've configured select tables on an ASE 12.0 dataserver to use the datarow locking scheme. Our intent was to reduce contention on these high transaction tables. We've gone through the recommended checks and locking guidelines in the Performance and Tuning Guide and appeared to be on the right track, i.e. we no longer have blocking locks and perceived performance is better.
The problem is that sometimes, after a heavy multi-threaded insert/update test on the tables in question, sp_lock shows many locks of type Ex_row still unreleased. This is still the case after the application driver has returned, seemingly with no errors. Note that this observation can only be made sometimes. The usual observation is that the lock count grows and shrinks as the driver application runs. When the driver program completes no locks are outstanding. We've investigated the uncommitted transaction theory and feel confident that this is not the case.
We could really use some help coming up with other theories to explore.
p.s. see version number below and attached example of sp_lock results
Adaptive Server Enterprise/126.96.36.199/P/SWR 9307 ROLLUP/Sun_svr4/OS 5.6/1660/32bit/FBO/Mon Dec 18 19:54:56 2000
In the TXT file, I can see two proccess running in trading DB.
I think the problem here is that there is a transaction open or may be there is a deadlock.
You can check that with this select:
-- OPEN TRANSACTION
SELECT substring(db_name(master..syslogshold.dbid),1,15) db_name,
datediff(second, starttime, getdate()) segundos
-- USERS IN DB ( if you don't like sp_who and need extra info)
select substring(db_name(dbid),1,15) db_name,
where dbid = db_id()
and suid <> 0
order by physical_io desc,
You can execute sp_who, and try to kill some users to see what happen.