Record locking? Well... not exactly...

Hi... I have just read tons of articles on the (blessed optimistic and
condemned pessimistic) record locking and... I feel like I know nothing ;-)
Actually, it is not only the record locking, what I need, and nobody seems
to descibe this.
Imagine the following scenario.
There is a database with, say 10000 records with some unvalidated data. And
there is an Intranet ASP.NET application which is an interface to the
database in question... and there are 100 pretty girls eager to... uhmm...
use the application and validate the data of course ;-).
The task is to enable the data validation in such a way that two girls NEVER
get the same record for validation (it is just a waste of time). When a girl
presses 'next' or something like that, she gets the access to an unvalidated
(and not being validated) record. This is more than pessimistic locking,
actually. And I missed the most important part - all this has to work
smoothly ;-).
I am thinking about a few solutions to this problem, I developed one once,
actually, basing on transactions with highest isolation level, but this was
not very smooth. Anyway, if anybody can give me a good advice or direct to a
good article, I will be very grateful.
Keep in mind that I might use synchronisation not on the DB level, but on
the level of the web application... maybe this would be faster?
Any comments will be welcome
DW.A combination of OUTPUT and TOP can get you part way there. Your ASP.NET
app would have to clean things up if for some reason the row was not fully
processed...
create table #justone
(
id int identity primary key,
data varchar(max),
inProcess bit default 0,
processed bit default 0
)
insert into #justone values ('asdfawerf', default, default)
insert into #justone values ('1asdfawerf', default, default)
insert into #justone values ('2asdfawerf', default, default)
insert into #justone values ('3asdfawerf', default, default)
insert into #justone values ('4asdfawerf', default, default)
-- this will get a new row every time
update top(1) #justone
set inProcess = 1
OUTPUT deleted.id, deleted.data
where inProcess = 0 and processed = 0
-- do processing of row here in ASP.NET app
-- do another update or whatever to indicate
-- the results of the user validating the data
Dan

> Hi... I have just read tons of articles on the (blessed optimistic and
> condemned pessimistic) record locking and... I feel like I know
> nothing ;-)
> Actually, it is not only the record locking, what I need, and nobody
> seems to descibe this.
> Imagine the following scenario.
> There is a database with, say 10000 records with some unvalidated
> data. And
> there is an Intranet ASP.NET application which is an interface to the
> database in question... and there are 100 pretty girls eager to...
> uhmm...
> use the application and validate the data of course ;-).
> The task is to enable the data validation in such a way that two girls
> NEVER get the same record for validation (it is just a waste of time).
> When a girl presses 'next' or something like that, she gets the access
> to an unvalidated (and not being validated) record. This is more than
> pessimistic locking, actually. And I missed the most important part -
> all this has to work smoothly ;-).
> I am thinking about a few solutions to this problem, I developed one
> once, actually, basing on transactions with highest isolation level,
> but this was not very smooth. Anyway, if anybody can give me a good
> advice or direct to a good article, I will be very grateful.
> Keep in mind that I might use synchronisation not on the DB level, but
> on the level of the web application... maybe this would be faster?
> Any comments will be welcome
> DW.|||Provided I understood you correctly, I tried a similar solution in fact.
All my tables have an 'ID' field, so I added an additional tLocks table
whose records contained a user name, a table name and a record ID. Then
handling the 'next' button was something like:
- begin a transaction,
- delete a previous tLocks record for a current user, if it existed,
- select first not yet validated record from the required table for which no
record exists in tLocks,
- add a new tLocks record for the current user, the requested table and the
appropriate record ID,
- commit the transaction.
Looks OK, doesn't it?
Well, while using the default transaction level, it was very commonplace
that two users happened to lock the same record. Changing the level to the
highest possible value seemed to help... for 2-4 users. When 10 users used
the database simultaneously, they again happened to get the same records at
a time :-/.
Well, I do not understand why this happened, but it did ;-).
Frankly speaking, I would be glad to read some article providing a few
possible solutions to choose from. Or maybe a description of some system
that was tested and worked under a real stress...
DW.|||If you are using TABLOCK then it won't work, it only holds a shared lock
on the table, not an exclusive one.
Between here

>- select first not yet validated record from the required table for
> which no
> record exists in tLocks,
and here

> - add a new tLocks record for the current user, the requested table
someone else might read the same row.
Try using UPDLOCK or TABLOCKX something like
BEGIN TRAN
DECLARE @.id INT
DECLARE @.data VARCHAR(MAX)
SELECT top(1) @.id=id, @.data=data from justone with (UPDLOCK)
where inprocess=0 and processed = 0
SELECT @.id
UPDATE justone set inprocess = 1 where id = @.id
COMMIT TRAN
Dan

> Provided I understood you correctly, I tried a similar solution in
> fact.
> All my tables have an 'ID' field, so I added an additional tLocks
> table whose records contained a user name, a table name and a record
> ID. Then handling the 'next' button was something like:
> - begin a transaction,
> - delete a previous tLocks record for a current user, if it existed,
> - select first not yet validated record from the required table for
> which no
> record exists in tLocks,
> - add a new tLocks record for the current user, the requested table
> and the
> appropriate record ID,
> - commit the transaction.
> Looks OK, doesn't it?
> Well, while using the default transaction level, it was very
> commonplace that two users happened to lock the same record. Changing
> the level to the highest possible value seemed to help... for 2-4
> users. When 10 users used the database simultaneously, they again
> happened to get the same records at a time :-/.
> Well, I do not understand why this happened, but it did ;-).
> Frankly speaking, I would be glad to read some article providing a few
> possible solutions to choose from. Or maybe a description of some
> system that was tested and worked under a real stress...
> DW.
>|||I have written a few workflow type applications and I think my solution
is a bit more simple. I maintain a status column and an optional
deltauser column to keep track of who has "locked" an item to be
worked. Your stored procedure would execute something like this to
retrieve an item:
DECLARE @.LockedID int
SET ROWCOUNT 1
UPDATE yourtable SET Status = 50, deltauser = @.username WHERE status =
0 -- and additional criteria for where clause
SET ROWCOUNT 0
--return results
SELECT * FROM yourtable WHERE id_column = @.LockedID
Because this code updates an item to locked before it selects it, you
will never have 2 users able to retrieve the same record.
When you update the worked item, you update the status to 100 (or
whatever is previously determined for complete). With this numerical
status, you can track multiple steps of work on the same item (150 - in
progress/locked for step 2, 200 = complete for step 2)
By using the deltauser (or lockeduser) you can query the database for
items that that user left locked (by crashing, closing down, etc) and
return the already locked item instead of a new item.
I have been using this locking mechanism for years now - even with
automated applications that continuously hit the DB to retrieve new
work and have never had a problem with users getting the same record.
HTH
Jason|||oops, the update statement below should read:
UPDATE yourtable SET Status = 50, @.LockedID = id_column, deltauser =
@.username WHERE status =
0 -- and additional criteria for where clause
Jason wrote:
> I have written a few workflow type applications and I think my solution
> is a bit more simple. I maintain a status column and an optional
> deltauser column to keep track of who has "locked" an item to be
> worked. Your stored procedure would execute something like this to
> retrieve an item:
> DECLARE @.LockedID int
> SET ROWCOUNT 1
> UPDATE yourtable SET Status = 50, deltauser = @.username WHERE status =
> 0 -- and additional criteria for where clause
> SET ROWCOUNT 0
> --return results
> SELECT * FROM yourtable WHERE id_column = @.LockedID
> Because this code updates an item to locked before it selects it, you
> will never have 2 users able to retrieve the same record.
> When you update the worked item, you update the status to 100 (or
> whatever is previously determined for complete). With this numerical
> status, you can track multiple steps of work on the same item (150 - in
> progress/locked for step 2, 200 = complete for step 2)
> By using the deltauser (or lockeduser) you can query the database for
> items that that user left locked (by crashing, closing down, etc) and
> return the already locked item instead of a new item.
> I have been using this locking mechanism for years now - even with
> automated applications that continuously hit the DB to retrieve new
> work and have never had a problem with users getting the same record.
> HTH
> Jason