Time Stamp in DBML is solving concurrency issues...why?

Question

We use LINQ in our ASP .NET application to run database queries and commands. We have been having concurrency issues on updates to tables in which we update the value of a Date field. More specifically, if two users click a button at the exact same time and the code attempts to update a Date field in the database then the built-in optimistic concurrency fails to do its job and one the users throws an exception.

Here's the weird part, if we go into the DBML and set the field's time stamp property to true and run the unit tests it works just fine...no explicit locks needed.

We would like to understand this a bit more before implementing this throughout the entire system. Does anyone have some understanding as to why this works?

Answer 1

Without knowing the details of your system I would guess that is because SQL Server DATETIME has a resolution of 3 ms at best, giving a high probability for conflicts. TIMESTAMP is a counter in desquise and cannot conflict.

Answer 2

Please check these out:

• 10 Tips to Improve your LINQ to SQL Application Performance
• On LINQ to SQL, Concurrency and Timestamps
• Optimistic Concurrency Database Updating

Answer 3

From memory, I believe this is how it works:

The default optimistic transactions ensure that all previous values on the row are the same before an UPDATE is allowed.

By setting the "Time Stamp" property to True, you are telling the system that a particular column is auto-generated by the DB, and should be used as the "time stamp type" column. In other words, if the value of that column alone is the same as it was before the UPDATE, then the UPDATE is allowed.

Since your unit test fails without time stamp enabled for the DateTime column, I'll guess that you're also changing something else on the row, which triggers the conflict. With time stamp enabled on the DateTime column, the changes happen so close together that they have the same time.

You should be able to verify this behavior using SQL Profiler.

FWIW, I suspect that this actually isn't the behavior you want...