About concurrency....

Twice, in the last week, concurrency issues have jumped up to bite me.

The first time round, the server had 'NO COUNT' set to on. This confused the .Net software, which couldn't tell if any rows had actually changed before it updated the table: LINQ to SQL uses @@ROWCOUNT after updates to issue an optimistic automated concurrency check. If 'NO COUNT' is on, the @@ROWCOUNT value is always zero, and the concurrency check fails.

The second occurred while using the SSMS GUI to try and edit data in a table: right click on the table, pick 'edit top 200 rows'. The error we got was

Data has changed since the Results pane was last retrieved. Do you want to save your changes now?
(Optimistic Concurrency Control Error)
Click Yes to commit your changes to database anyway.
Click No to discard your change and retrieve the current data for this row.
Click Cancel to continue editing.

Again: concurrency issues. So, the first thing I did was to check whether 'NO COUNT' was on. It wasn't. Then I went in for a bit of google. It turns out the problem was in the data.

Several rows in the table were identical, and contained text fields. Despite having an 'id' column, the table didn't have a primary key. Via the GUI, SQL couldn't work out which row to delete, and, of course, running a DELETE T-SQL statement would have deleted all the rows. We wanted to keep one of them (natch).

To tidy up the data, I ran an INSERT, based upon a SELECT TOP 1 T-SQL statement (it was not possible to SELECT DISTINCT - there were text fields involved), but with a new ID value. Then I DELETEd the duplicate data, and finally UPDATEd the new row to the original ID value.

At this point, we tried changing some other data in the table. We had the same error message, despite the fact that the data was, this time, unique.

Unique, but without a primary key. Redesigning the table to include a primary key on the 'id' column (and why there wasn't one on there in the first place is anyone's guess) has made it possible to update the data via the GUI.

There's one last reason why this particular concurrency issue may happen: data. Columns containing '%', '_' or '[' can also cause issues.