I have an application with lot's of locking problems, I'd like to log detailed things related to locks in my mssql db. More precisely: I want to log not only deadlocks (I've found how to do that, it's rather well talked about on the net) but rather all attempts to lock an object that fail.
Can anyone give me pointers to how to log locking attemps that don't succeed immediately (I need to log : client hostname/ip, object the lock was trying to acquire on as a minimum, the query would be a bonus) I'm looking at something I can parse/process somewhat easily, as I expect to run this for a long time and gather a sizeable volume (GB/TB) of data.
The background is that the application I'm looking at has a respectable number of tables (50+), accessed by a outrageous collection (500+) of programs made 20 years ago. These were converted to be able to run on up-to-date computers last year, but the locking semantics were totally messed up during the convertion (mostly lock share converted to lock exclusive or vice versa, and infinite retry strats on failure to acquire lock where introduced "as a fix" previously, sigh...). Most of these programs access only one or 2 tables at a time so the lock graphs to be reconstructed won't be very complex.
I often end up with 2 users on different computers starting the same program, and one of them will either be blocked with no idea of what's happening, or worse, some operations will work and some other won't and it'll mangle something in the db because operations are stopped halfway through and the program is exited before finished it all off (forget about transactions..., each request is it's own transact as a rule of thumb and I can't change that on my budget).
For those reading the dailywtf, it's written in MAGIC, which is a close cousin of MUMPS, seriously.
I'm using server express 2012, but can easily switch to express 2017 or mssql 2017 dev edition (in fact, I've started doing so yesterday).