Sql-server – Use SSMS to Create Database Diagrams but not Database Objects

Fixed this - VSS Writer had fallen over for some reason, and the upshot of that was that a load of stuff (BACKUP amongst them) could not complete (BlkBy column in sp_who2 showed this up). A reboot of the server fixed this issue (services.msc wouldn't let me kill VSS Writer, and neither would the KILL command)...only thing left to do is find out WHY it happened.

And sort myself a disaster recovery plan ;)

The goal is to make the query quick and to avoid deadlocks.

This is an unrealistic goal. Deadlocks are determined by the application acquiring the locks and is out of control of how you implement the locking. The best you can hope for is to detect deadlocks.

Implementing locks as record is problematic. Rows persist and you'll leak locks on application crash, even when the implementation is perfect. Do locks with applocks. They have clear transactional semantics and deadlocks are detected by the engine.

However, looking at what you're trying to achieve, is unlikely you need locks at all. You are describing a queue (pick the next item available to process and avoid conflicts => queue, not locking). Read Using tables as Queues.

As for your specific implementation (using a table that keeps the history of the locking) I must be honest: it is a disaster. To start with the table design is completely inadequate for the intended usage: you query by name but the table is a heap with no indexes. It has an identity column for no apparent reason. You may reply that it is just a 'pseudo-code' table, but this is DBA.SE, you do not post here incomplete DDL!

But more importantly, the implementation does not implement locking! There is nothing to prevent two users from 'locking' the same object twice. Your 'locking' relies entirely on the callers magically behaving correctly. Even a best written application could not use this locking, because there is no way of checking and acquiring the lock atomically. Two users can check, conclude that 'a' is unlocked, and simultaneously insert the ('a', 1) record. At the very least you would need an unique constraint. Which of course would break the semantics of "count the locks vs. unlocks to determine status".

Sorry to say, but this is an F grade implementation.

UPDATE

So we want to provide a capability to track each lock call. Upon restarting the server, we will call unlock on the objects which previously locked.

W/o engaging into two-phase commit distributed transaction with the remote system all you can do is a 'best effort', because there are many race conditions between you writing the 'unlock' and actually calling 'unlock' on the third party system. As a best effort, here is my recommendation:

• create a simple table to track the locks:

  CREATE TABLE locks (name VARCHAR(255) NOT NULL PRIMARY KEY);

• before calling lock insert the lock into your table and commit.

• after calling unlock delete the lock from your table and commit
• on system startup look at the table. any row there is a left over lock from a previous run and must be 'unlocked'. Call unlock for each row, then delete the row. Only after all pending locks have been 'unlocked' you can resume the app normal functionality.

I'm proposing this because the table will stay small. At any time it only contains current, active locks. It will not grow ad-nauseam and cause problems later due to sheer size. Is trivial to see what is locked.

Of course this implementation does not offer an audit history of what was locked by whom and when. You can add that if needed, as a different table, into which you only insert the events (lock or unlock) and never query that to figure out 'orphaned' locks.

You still have to be prepared with calls to 'unlock' to fail during startup because you cannot guarantee that your system did not crash after calling unlock but before deleting the row (in other words your table and the third party system have drifted apart and have different versions of the truth). Again you cannot prevent this w/o distributed transactions and I would never advise for DTC.