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]

bala (ba*****@gmail.com) writes:

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

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[bala]

thanx a lot erland. have a great day