Concurrency in Transaction

[bala]

hi gurus

the scenario
Frontend - MS Access (not yet decided whether MDB or ADP)
Backend - MS SQL Server

it is a conversion from MS Access backend to MS SQL Server Backend.

Planning to create stored procedures for all the Inserts, Updates,
Deletes and Business Rules / Validations wherever it is possible.

the problem
i am running in concurrency problem. the same classic scenario of two
users retrieving the same row (record) from the same table. it allows
both the user to update the record, that is, the user who updates last
has his changes saved though he retrieved that particular record
second.

what i need is that the user who retrieved the record second shouldn't
be able to update or delete the record when it is already retrieved by
any other user.

would appreciate if someone pointed me in the right direction to solve
the above problem, i know it is related to isolation property but am
not sure

thanx in advance

regards
bala

[Erland Sommarskog]

Re: Concurrency in Transaction

bala (balkiir@gmail. com) writes:[color=blue]
> the problem
> i am running in concurrency problem. the same classic scenario of two
> users retrieving the same row (record) from the same table. it allows
> both the user to update the record, that is, the user who updates last
> has his changes saved though he retrieved that particular record
> second.
>
> what i need is that the user who retrieved the record second shouldn't
> be able to update or delete the record when it is already retrieved by
> any other user.
>
> would appreciate if someone pointed me in the right direction to solve
> the above problem, i know it is related to isolation property but am
> not sure[/color]

One convenient solution is to use a timestamp column. A timestamp column
is a column which automatically is updated every time a row is update.
Timestamp has nothing to do with date and time, but is a 8-byte binary
value.

When you read a row, you retrieve the timestamp value to the client,
and then you use that in the WHERE condition when you update:

UPDATE tbl
SET ...
WHERE keycol = @key
AND tstamp = @tstamp

If @@rowcount is 0 after the update, this means that the tstamp value
that you read is no longer good, because someone else have updated the
row.

This is a little different from your request, as here the first process
that update wins, not the first that reads it.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[bala]

Re: Concurrency in Transaction

thanx a lot erland. have a great day