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