Sql-server – Use database to track locking/unlocking of objects

sql serversql-server-2008-r2

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

Best Answer

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.

Related Question