If you were to use ADO.NET and your selected an optimistic concurrency model, all fields would be check when the update attempt is made. If there are any contradicting values then you would get concurrency errors.

In regards to LLBLGen, I can write a class that implements IConcurrencyPredicateFactory and add predicates for each field that I want to check for concurrency. In some systems, where there are many entities, I could have an abundance of extra coding to do.

If I add a single timestamp field to each entity that is to use concurrency, and then create a ConcurrencyFactory for each entity, would timestamp value enforce row level concurrency for all fields in the entity? I beleive it would because when any value changed on a row, I beleive the timestamp is automatically updated.

Yes.

Devildog74 wrote:

If I add a single timestamp field to each entity that is to use concurrency, and then create a ConcurrencyFactory for each entity, would timestamp value enforce row level concurrency for all fields in the entity? I beleive it would because when any value changed on a row, I beleive the timestamp is automatically updated.

We usually use a single int column we call "RowLockKey" which is just incremented on every update to implement the same thing. But I'm curious as to your statement that the "timestamp is automatically updated". What would you use for your "automatic" update? I assume that you're using your ConcurrencyFactory to do that, but how would that work? Or are you thinking if a DB trigger? Or something else in the code? I just want to see if its easier than remembering to do rowlockkey = rowlockkey + 1 before on each update (which would be neat).

takb wrote:

What would you use for your "automatic" update? I assume that you're using your ConcurrencyFactory to do that, but how would that work? Or are you thinking if a DB trigger? Or something else in the code? I just want to see if its easier than remembering to do rowlockkey = rowlockkey + 1 before on each update (which would be neat).

If the DBMS being used is Sql Server, a row's timestamp value is updated automatically by the database after the row is updated.

Jeremy wrote:

If the DBMS being used is Sql Server, a row's timestamp value is updated automatically by the database after the row is updated.

Yep.

Jeremy wrote:

If the DBMS being used is Sql Server, a row's timestamp value is updated automatically by the database after the row is updated.

Bewty! Exactly what I've been looking for (and obviously to this point...haven't looked hard enough). Always too busy trying to be database independent and missing neat fundamental features like this one.

Thanks guys.

just a note...dont expect to see an actaul timestampl such as 1-1-2004 10:23. From the books on line

_The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

Microsoft® SQL Server™ 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements. rowversion is subject to the behaviors of data type synonyms. _

I usually create 2 extra columns on my tables inserttimestampGMT with a default of getdate() and an updatetimestmpGMT that my application will update. I find this info much more valuable. I had been thinking of trying to figure out a way for LLBLGen to update the UpdateTimestampGMT whenever an update was issued..just havent had a chance to look into it yet

Thanks for the info. I did have a check of the docs and the timestamp column is fine for our purpose (though your date columns would be useful for diagnostic purposes too).

Unfortunately, I can't find the same behaviour in Oracle. (I need to be able to target both databases with the same code - and will need DB2 support as soon as Otis gets a driver written for it). For these other databases, I guess I could go with a trigger to update the "timestamp" column. That should simulate the SQLServer out-of-the-box behaviour. I'd just need to ensure that I get my database types right across the three databases (each instance of my application runs against a single database but a client can choose which database they want to use - SQLServer, Oracle, DB2. The schema and code is the same across all three).

bertcord wrote:

I usually create 2 extra columns on my tables inserttimestampGMT with a default of getdate() and an updatetimestmpGMT that my application will update. I find this info much more valuable. I had been thinking of trying to figure out a way for LLBLGen to update the UpdateTimestampGMT whenever an update was issued..just havent had a chance to look into it yet

Definitly better than relying on sql server timestamp funationality.

We have to support Oracle and Sql Server, we did this by deriving a subclass of DataAccessAdapter and overriding the SaveEntity method and implementing a BeforeSave where we set any values like concurrency timestamps.

Using datetime columns for concurrency is a dangerous game (NOTE: not timestamps in SQL Server they are different). DateTime are usually not accurate enough for highly transactions systems (the granularity is usually not to the micro or nano second). If you are using the datetimes for another purpose (e.g. auditing), then no issue. You are usually better off doing something like row or column level checksum's for concurrency if you cannot use timestamps. See my article on the relative performance characteristics in SQL Server (in Oracle your performance may vary):

http://www.adoguy.com/2006/06/08/BINARY_CHECKSUM_and_Database_Concurrency.aspx

Shawn Wildermuth Microsoft MVP (C#)