I'm working on a PB / OLE DB / SQL2000 application architecture. Here is my requirement:
I need to find out if/how I can interrogate a SQL2000 database for its locking information, with a fairly high degree of granularity. That is to say I'd want to extract the following info:
---> The name of the table thats locked. (this I can get from SYS tables...)
---> The table and the row or range of rows that are locked.
---> The table and the row (RId or physical key) that is locked.
My understanding is that rows that are locked as part of a page lock, or a row that is rowlocked, is managed internally within the DBMS. Any one of the three lock types will register e.g. on the SYSLOCKS table, but only specifies the name of the locked object (i.e. table name, index, etc...).
Is my only real solution to create my own custom locking info table(s)? I don't strongly favor that sort of solution, because the code to properly manage such a table would be pretty ambitious to write, and prone to corruption at some point no matter how well we build it.
All the tools to do that are at your finguretips, - PerfMon, DBCC, Profiler, and QA. Look into sp_lock stored procedure that will point you to master.dbo.syslocks and master.dbo.syslockinfo. Look into perfmon counters as well as various profiler templates.