DB Access

[RG]

I am using sql server 2005 as back end db. I am writting a concurrent
connection application where end users are updating, via a browser, the same
tables based on the data supplied by browser. It is possible that between
the times that a user pulls a row and updates that row, a concurrent user
may update it before him. Considering this, what is the optimal way of
handling database acces in these situations?

Any info is appreciated,

Thanks in advance

 

[Mr. Arnold]

I am using sql server 2005 as back end db. I am writting a concurrent
connection application where end users are updating, via a browser, the
same tables based on the data supplied by browser. It is possible that
between the times that a user pulls a row and updates that row, a
concurrent user may update it before him. Considering this, what is the
optimal way of handling database acces in these situations?

The simplest way and the way I implement concurrency issues even in
mainframe online and client/server online solutions is to use a timestamp
and/userid field on the record. The timestamp and userid is placed on the
record and the timestamp and userid are updated as the record is updated to
reflect the last user that updated the record and the time it was updated.

When I try to update the record the current user has changed online, in
computer memory for the record, I take the timestamp and/or userid that the
user has in memory, do a read of the record again and compare the timestamp
and/or userid with the ones in memory for the record.

If the timestamp and/or user-id are not the same for the record, the one in
memory compared against the record you just read again, then some other user
has updated the record, before the current user has had a chance to save the
record. In this case ,the current user is stopped form saving the changes,
with a message to the user that the record has changed, which means the user
has to get the record for update again and then save the changes via the
browser.

Some others have other was of doing it, but that's the simplest. Others say
last one saving wins, no concurrency check.




__________ Information from ESET NOD32 Antivirus, version of virus signature database 4357 (20090821) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Ed Murphy]

The simplest way and the way I implement concurrency issues even in
mainframe online and client/server online solutions is to use a timestamp
and/userid field on the record. The timestamp and userid is placed on the
record and the timestamp and userid are updated as the record is updated to
reflect the last user that updated the record and the time it was updated.

When I try to update the record the current user has changed online, in
computer memory for the record, I take the timestamp and/or userid that the
user has in memory, do a read of the record again and compare the timestamp
and/or userid with the ones in memory for the record.

If the timestamp and/or user-id are not the same for the record, the one in
memory compared against the record you just read again, then some other user
has updated the record, before the current user has had a chance to save the
record. In this case ,the current user is stopped form saving the changes,
with a message to the user that the record has changed, which means the user
has to get the record for update again and then save the changes via the
browser.

Some others have other was of doing it, but that's the simplest. Others say
last one saving wins, no concurrency check.

It's still possible for someone else to update the record between your
second read and your update; the time window is narrower, but not
nonexistent. I suggest adding the timestamp and/or userid to the WHERE
clause of the UPDATE, then if it reports 0 rows updated then the first
thing you check is whether the timestamp and/or userid has changed.