ROWLOCK usage

hi. i don't have much experience with locking using lock hints so wondered i
f
someone could help me with usage of ROWLOCK. i am writing a number of procs
which will perform validation on data prior to performing updates. i need
read consistency for the duration of these procs whilst guaranteeing maximum
concurrency. therefore i intend to use the following pattern:
create procedure MyProcedure
@.MyParam int
as
-- wrap in transaction
begin transaction
-- obtain lock
select 1
from MyTable (HOLDLOCK, UPDLOCK)
where MyPKColumn = @.MyParam
-- other operations here involving SELECTs on MyTable
-- perform update
update MyTable
set MyOthercolumn = 'NewValue'
where MyPKColumn = @.MyParam
-- release all locks
commit transaction
what i don't like about this is that i am beginning my transaction earlier
than i would prefer, but otherwise this appears to meet my requirements. is
this a good strategy, or is there a better way? what issues might i face?
many thanks
kh> what i don't like about this is that i am beginning my transaction earlier
> than i would prefer, but otherwise this appears to meet my requirements.
> is
> this a good strategy, or is there a better way? what issues might i face?
You are probably also locking pages (or perhaps even the entire table). IF
you take this path you might want to look into specifying ROWLOCK so that
you only lock the particular row that you are working on.
It seems like you are trying to reinvent the wheel.
You don't account for the situation where the data has changed since the
user initially retrieved the data.
User A retrieves the data for PKcol = 1
User B retrieves the data for PKcol = 1
User A goes to lunch
User B starts updating the data (within the application GUI)
Userr B hits "save" and writes the data to the database
User A comes back from lunch, finishes updating the data within the GUI, and
clicks save.
The data that User B entered is overwritten by User A.
One way around this problem is to pass the old and new values to the stored
procedure. The WHERE clause would use the primary key and it would compare
the @.old params to the data that is in the table. If @.@.rowcount = 0 the
data was different and the update did not happen.
Now that I have you worried about that type of concurrency issue, lets get
back to your validation question.
Can't you validate data within the GUI?
If not you should perform data validation (I assume that this is the "--
other operations here involving SELECTs on MyTable" outside of a
transaction. Heck, I don't even see why you need a transaction in this
stored procedure. It does not seem to buy you anything.
I don't know what type of data validation you need to do. Lets say that you
cannot have multiple UserNames or FileNames within a table. You could do
something like this before update statement. The RETURN will cause the
stored procedure to end. It will also pass back (via the return code) the
value within the parens.
--validation check
IF EXISTS (SELECT * FROM dbo.MyTable WHERE UserName = @.MyUserName )
BEGIN
RETURN (1)
END
--validation check
IF EXISTS (SELECT * FROM dbo.MyTable WHERE FileName= @.MyFileName )
BEGIN
RETURN (2)
END
--everything is a-ok, lets update
UPDATE dbo.MyTable SET MyOthercolumn = 'NewValue'
WHERE MyPKColumn = @.MyParam
RETURN (0)
Keith Kratochvil
"kh" <kh@.newsgroups.nospam> wrote in message
news:FBDE5CBC-4CB8-4CE2-8928-13BB3624F0C4@.microsoft.com...
> hi. i don't have much experience with locking using lock hints so wondered
> if
> someone could help me with usage of ROWLOCK. i am writing a number of
> procs
> which will perform validation on data prior to performing updates. i need
> read consistency for the duration of these procs whilst guaranteeing
> maximum
> concurrency. therefore i intend to use the following pattern:
> create procedure MyProcedure
> @.MyParam int
> as
> -- wrap in transaction
> begin transaction
> -- obtain lock
> select 1
> from MyTable (HOLDLOCK, UPDLOCK)
> where MyPKColumn = @.MyParam
> -- other operations here involving SELECTs on MyTable
> -- perform update
> update MyTable
> set MyOthercolumn = 'NewValue'
> where MyPKColumn = @.MyParam
> -- release all locks
> commit transaction
> what i don't like about this is that i am beginning my transaction earlier
> than i would prefer, but otherwise this appears to meet my requirements.
> is
> this a good strategy, or is there a better way? what issues might i face?
> many thanks
> kh
>|||keith. many thanks. some notes for clarity:

> You are probably also locking pages (or perhaps even the entire table). I
F
> you take this path you might want to look into specifying ROWLOCK so that
> you only lock the particular row that you are working on.
sorry, copy and paste error: the lock hints should of course be (HOLDLOCK,
ROWLOCK) and since I am selecting using the Primary Key I am (hopefully) onl
y
locking a single record.

> You don't account for the situation where the data has changed since the
> user initially retrieved the data. <snip>
there is no user access to the database accept via our app server. users can
go for lunch as often as they like, data will never be left in an uncommitte
d
state other than during stored procedure execution. the usage of ROWLOCK
hopefully avoids the situation you describe since it is only used during wel
l
defined units of execution.

> One way around this problem is to pass the old and new values to the store
d
> procedure. The WHERE clause would use the primary key and it would compar
e
> the @.old params to the data that is in the table. If @.@.rowcount = 0 the
> data was different and the update did not happen.
i am intentionally taking a 'pessimistic concurrency' approach here

> Can't you validate data within the GUI? <snip>
the validation involves selects and inserts into other tables (auditing,
etc) and relates to the requirements of downstream applications rather than
business rules within our own application. it is therefore not appropriate t
o
perform this validation within our UI or app server.

> Heck, I don't even see why you need a transaction in this
> stored procedure. It does not seem to buy you anything.
the only reason that the validation takes place within a transaction is so
that the ROWLOCK is held and i can guarantee that the data has not changed
between the beginning of the validation and the ultimate commit of this data
to the database.
kh|||> users can go for lunch as often as they like
That sounds great. I would like to take 3 or 4 lunches per day!

> the only reason that the validation takes place within a transaction is so
> that the ROWLOCK is held and i can guarantee that the data has not changed
> between the beginning of the validation and the ultimate commit of this
> data
> to the database.
That sounds reasonable. You know your system better than any of us. I
guess you are taking the correct approach.
Keith Kratochvil|||cheers keith. so in summary:
- i know my app better than you
- you know sql server better than me
- my users will shortly need a strict exercise regime
kh