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).


MAGIC, ouch.

Check out the "blocked process threshold" configuration option.

You can also use SQL Trace/Extended Events to log all or filtered lock requests and lock acquisition and release events. This information will be very voluminous and expensive to process, so you typically only do that when troubleshooting a particular operation.

Also consider setting the READ_COMMITTED_SNAPSHOT database option to switch to a concurrency model where readers don't block writers and writers don't block readers.

| improve this answer | |
  • Agreed - READ_COMMITTED_SNAPSHOT might be the magic to fix your existing magic :) .... or not, but it's worth try in your dev system. You've got a dev system right? :) – Nick.McDermaid Aug 16 '18 at 23:16
  • I've got a dev system, but dont have a budget (knowledge/time) to setup and run concurrency tests (I don't have a ready solution to automate running MAGIC programs, and I dont know which programs to run either). This is all very much "run, hit the wall, learn to turn before the wall next time" kind of thing. In general, I need to instrument the prod system because I just can't generate a prod-like load on the test system. I would wait for the database option mod as a last resort, because I have not much in the way of testing and validation that can be done before throwing this in prod. – caliloo Sep 28 '18 at 9:49
  • I have used SQL Trace/Extended Events, it generated GB and even TB of data, but wading thru it was possible, and that allowed me to do post mortems after hitting the proverbial wall. Was manual, so not efficient, but better than not being able to do postmortems at all. – caliloo Jul 29 '19 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.