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