|
Home > Archive > Microsoft SQL Server forum > August 2005 > Concurrency in Transaction
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Concurrency in Transaction
|
|
|
| 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 2005-08-04, 3:23 am |
| bala (balkiir@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
|
| thanx a lot erland. have a great day
|
|
|
|
|