Use database to track locking/unlocking of objects

Question

I have a requirement to track lock/unlock actions of objects. Before any action made on an object (contract, partner, etc), an lock event is issued. After the action is finished, it issues the unlock event.

I want to obtain those objects which are locked but not yet unlocked. The goal is to make the query quick and to avoid deadlocks.

Below is the table

create table locks (
    id int identity,
    name varchar(255),
    lock int
) 

insert into locks values('a', 1)
insert into locks values('b', 1)

insert into locks values('c', 1)
insert into locks values('d', 1)

insert into locks values('a', 0)
insert into locks values('c', 0)


insert into locks values('a', 1)
insert into locks values('b', 1)

I use the query below to object not-yet-unlocked objects:

select distinct m.name from locks m
    where (select COUNT(id) from locks locked
           where locked.lock = 1 and locked.name = m.name)
        >  (select COUNT(id) from locks unlocked
            where unlocked.lock = 0 and unlocked.name = m.name)

It works correct and result a, b and d.

My questions are: - Is my solution sufficient enough to avoid deadlocks? Is there any problem may occurs if there are a lots of INSERT during the query execution? - Do you have any other (better) way to solve this?

UPDATE

I apologize for not putting the context into the question. The database design above is not for replacing database locking.

We have an external system which we call it from our system. It requires to call lock and unlock method on their systems before each action performing on an object (could be a contract or a partner).

Recently, we have situations such that the server crashes and we have to restart it. Unfortunately, the running processes which already called lock did not have a chance to call unlock to release the objects, thus led to several other problems when our system connects again to the external one.

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.

Thank Remus Rusanu for pointing out that my question is using a prototype DDL. This is the first time I posted a question on DBA and I apologize for not reading the FAQ.

Thanks

Answer 1

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.

Answer 2

This is going to kill concurrency on your application. SQL Server already has everything it takes to avoid updating the same rows at the same time, so there is really no need to do that.

Deadlocks can happen for several reasons, so I suggest that you investigate those reasons before building your own locking system. You can capture deadlock graphs from the system health XE session and start to analyze them.

Usually deadlocks are the result of taking locks on objects in different orders, so you should try to take locks always in the same order. There are other reasons why deadlocks may occur, but general guidance is that short transactions hold locks for a short amount of time, so tuning your queries with better code, indexes and divide-et-impera strategies will probably be the best way to get rid of deadlocks.

"Readers blocking writers" kind of deadlocks can be highly mitigated by switching the database to Read Committed Snapshot Isolation. If your application was built with pessimistic locking in mind, you should review and test everything very carefully before activating this option on your database.

In case you insist going down the "custom locking system" route, at leat use something that guarantees realeasing locks in case something with the app goes wrong. You may want to investigate the built-in sp_getapplock stored procedure, wich does something similar to what you seem to be after.

UPDATE: After reading your edited question, this is an alternative way to express the same query:

SELECT *
FROM (
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY name ORDER BY id DESC) 
    FROM locks
) AS data
WHERE RN = 1 
    AND lock = 1;

It will work if objects are allowed to be locked just once, which seems to be the whole point of the locking system anyway.