Discouraged about timestamps

I have read the forums and fail to be encouraged about using the timestamp to handle concurrency. I have SQL 2005 and am using the DataReader and command objects for data manipulation. The database has about a dozen related tables to each other in some way. Correct me if I am wrong, but using timestamps means I must store each table's original rowversion at read time in several possible combinations of variables (based on the joined tables at the time of read). Is this right? Then at update time, what if some related tables are timestamped with the original value and others have changed since read time? How would each update stored procedure know which other dependent tables were fetched at read time? It's easy to corrupt the data this way.

SELECT

s.OrderNumber, a.UserNameASOrderedBy, cn.LastName +', '+ cn.FirstNameASContactName, s.CarbonCopy, s.Application, s.OrderDate,

s.FollowUpDate, s.ProdStartDate, s.InternalNote, s.SampleNote, s.UpdatedBy, s.DateLastUpdated, cn.EMail

ASContactEmail, s.MfgID,

co.CompanyName, s.ShipVia, cn2.LastName +

', '+ cn2.FirstNameASContactName2, co2.CompanyNameASExpr1,

cn3.LastName +

', '+ cn3.FirstNameASContactName3, s.MfgContactID, s.DistributorID, s.DistContactID, s.CustomerContactID,

s.VersionStamp, a.VersionStamp, cn.VersionStamp, co.VersionStamp, cn3.VersionStamp <--- Is all this really necessary?

FROM

SamplesASsLEFT OUTER JOIN

Associates

ASaONs.OrderedByID = a.AssociateIDLEFT OUTER JOIN

Contacts

AScnONs.CustomerContactID = cn.ContactIDINNER JOIN

Companies

AScoONs.MfgID = co.CompanyIDLEFT OUTER JOIN

Contacts

AScn3ONs.DistContactID = cn3.ContactIDLEFT OUTER JOIN

Contacts

AScn2ONs.MfgContactID = cn2.ContactIDLEFT OUTER JOIN

Companies

ASco2ONs.DistributorID = co2.CompanyID

WHERE

(s.SampleID = @.SampleID)

What would be a simple way to update a table that depends on other tables having original version stamps? Then at runtime how would I enforce it without generating violations when the users were updating, say, the Samples table and just viewing the dependent tables' columns, not changing them.

The timestamp datatype is used for replication and the values are system assigned.Do you mean using an Int (or even BigInt) data type to record a count of updates to a given row? If so, let's call the column, TSTAMP. All you have to do for a given row is save the TSTAMP value and feed to the update such that the update will only by actioned if the TSTAMP value is the same as when the data was read for the user. The TSTAMP value of course is incremented by 1 as part of a successful update.I question the wisdom of trying to update so many tables at once. Keep the update screen simple - only update one record at time. Obviously if a parent record has fields summerising the child values, then the stored procedure that update the child should also update the parent.|||Record permiation to do update tracking as you suggested is not an option according to my boss. We are running in production and wish to keep the remedy as simple as changing a flag in the configuration files. As of now I have no plan that he approves of without modification to the data layer's logic. Simple pessimistic table locking might address his needs. SQL 2005 only manages data locking optimistically in the realm of simplicity however.|||What I have suggested is simple and robust - I will readily admit that it has to built in from the start. I have used in numerous systems.|||

I've written a tutorial that should "encourage" you about timestamps and how to use them with asp.net

http://www.primedigit.com/implementing-optimistic-concurrency-with-sql-timestamps/

sql