optimistic locking

I am writing an Application which is connected to a database on MSSQl
Server. For performing updates on a table I chose the way of optimistic
locking using timestamps. I use a stored procedure for updating the
values which returns either 0 or 1 to indicate if there was a conflict
or not. If there was one, the current values in the database are selected.
My stored procedure for updating data looks like this:
create procedure updateSomeTable
@.key int,
@.col1 nvarchar(100),
@.col2 datetime,
@.TS timestamp
AS
update MyDB.dbo.SomeTable
set col1 = @.col1,
col2 = @.col2
where key = @.key and TS = @.TS
if(@.@.rowcount = 0)
begin
select * from SomeTable where key = @.key
return 1
end
return 0
Do I need any transactions within souch a procedure? As far as I can
think of, I don't need any... Is this the typical way to perform this
task, or are there much more common ways to do this?
Thanks
Regards
StephanNot really. The UPDATE command is in an implicit transaction, and it is the
nature of the beast of opimistic locking that a user might change it after
you retrieve it (it is optimistic after all.)
Not sure that I would just conditionally receive the row. The return count
here is not of much value, since you already have to decide whether or not
to fetch a dataset which may or may not be returned. I would just always
return the row, which takes care of any trigger modifications (if there were
any, usually this in not a big deal) but also to handle the new timestamp.
Once you do the update it will change, meaning you will have to go fetch at
least the timestamp after the modification.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Stephan Zaubzer" <stephan.zaubzer@.schendl.at> wrote in message
news:utkvIuQIGHA.2300@.TK2MSFTNGP15.phx.gbl...
>I am writing an Application which is connected to a database on MSSQl
>Server. For performing updates on a table I chose the way of optimistic
>locking using timestamps. I use a stored procedure for updating the values
>which returns either 0 or 1 to indicate if there was a conflict or not. If
>there was one, the current values in the database are selected.
> My stored procedure for updating data looks like this:
> create procedure updateSomeTable
> @.key int,
> @.col1 nvarchar(100),
> @.col2 datetime,
> @.TS timestamp
> AS
> update MyDB.dbo.SomeTable
> set col1 = @.col1,
> col2 = @.col2
> where key = @.key and TS = @.TS
> if(@.@.rowcount = 0)
> begin
> select * from SomeTable where key = @.key
> return 1
> end
> return 0
> Do I need any transactions within souch a procedure? As far as I can think
> of, I don't need any... Is this the typical way to perform this task, or
> are there much more common ways to do this?
> Thanks
> Regards
> Stephan|||Ok, so my thoughts of not to need an explicit transaction was ok...
As you said it is definitely better to fetch the new dataset immediately
after inserting it. But the chances exist, that someone will update this
row between my insert and the fetch. But this probability should rather
be negligible.
Are there any other issues besides these with this procedure?
Louis Davidson schrieb:
> Not really. The UPDATE command is in an implicit transaction, and it is t
he
> nature of the beast of opimistic locking that a user might change it after
> you retrieve it (it is optimistic after all.)
> Not sure that I would just conditionally receive the row. The return coun
t
> here is not of much value, since you already have to decide whether or not
> to fetch a dataset which may or may not be returned. I would just always
> return the row, which takes care of any trigger modifications (if there we
re
> any, usually this in not a big deal) but also to handle the new timestamp.
> Once you do the update it will change, meaning you will have to go fetch a
t
> least the timestamp after the modification.
>|||> after inserting it. But the chances exist, that someone will update this
> row between my insert and the fetch. But this probability should rather be
> negligible.
This really isn't a big deal at all. The idea behind optimistic locking is
that you can get the rows, hold onto them for a long time, and still know
that they are good by the timestamp. You just accept this as the risk
(obviously the risk should be evaluated based on the data integrity
requirements).
I am going to assume that this is for 2000. If it is 2005, we could extend
using TRY...CATCH. You would possibly want transactions for 2005 with
TRY...CATCH because of how triggers behave. In 2000 an error/rollback in a
trigger just halted everything. If you are in a TRY...CATCH block, that
changes.
Anyhow, I wouldn't put the database name as a prefix, unless it is in a
different one from the tables. It makes it harder to use it with a
different named database, like for testing if you don't have the ability to
have multiple servers. I think this will work fine. I added a check to see
if the row had been deleted, though you will also know that because no rows
will be returned :)
create procedure updateSomeTable
@.key int,
@.col1 nvarchar(100),
@.col2 datetime,
@.TS timestamp
AS
declare @.rowcount int
update dbo.SomeTable
set col1 = @.col1,
col2 = @.col2
where key = @.key
and TS = @.TS
set @.rowcount = @.@.rowcount
select col1, col2
from dbo.SomeTable
where key= @.key
--this means that the row has been deleted
if @.rowcount = 1 --this means it has been updated
return 1
else
return 0
go
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Stephan Zaubzer" <stephan.zaubzer@.schendl.at> wrote in message
news:%235J5fSSIGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Ok, so my thoughts of not to need an explicit transaction was ok...
> As you said it is definitely better to fetch the new dataset immediately
> after inserting it. But the chances exist, that someone will update this
> row between my insert and the fetch. But this probability should rather be
> negligible.
> Are there any other issues besides these with this procedure?
> Louis Davidson schrieb: