ZippyV wrote:
I'm currently working on a project with a database and have to keep in account that concurrency problems might occur.
So I'm using a timestamp column in most/all of the tables. My question is: what's the best sql stored procedure when updating data? Obviously I should compare the timestamp and see if it matches the last time it was read. But other than that, do I have to use transactions, raise exception, just return a -1 value if the timestamps don't match?
What's the best stored procedure?
Well it depends...
first a transaction is used for any case like:
Customer withdraws money from account, you have to update the atm cash drawer and the customer account at the same time.
both have to work or you rollback the transaction.
if you are only modifing one table then a transaction may be overkill.
often I use 2 or more columns to make sure I have the right data, for example I often have WhoChanged int and LastChanged datetime plus an ID
when I update the row I always udate WhoChnaged and lastChanged so that if two (or more) users fetch the same row and one of them does an update the others will fail the update as the
WHERE will fail to find that record.
you could do a select and get a COunt(*) to see if the record was modified...
use that to have the proc return a status value like 0 for "OK" and 1 for "Row updated by another user" etc...