What's causing a Concurrency Violation when using a MySQL linked Dataset via TableAdapters?

Question

The more I read on this, the more confused I get, so hope someone can help. I have a complex database setup, which sometimes produces the error on update:

"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records"

I say sometimes, because I cannot recreate conditions to trigger it consistently. I have a remote mySQL database connected to my app through the DataSource Wizard, which produces the dataset, tables and linked DataTableAdapters.

My reading suggests that this error is meant to occur when there is more than one open connection to the database trying to update the same record? This shouldn't happen in my instance, as the only updates are sequential from my app.

I am wondering whether it has something to do with running the update from a background worker? I have my table updates in one, for example, thusly:

    Gi_gamethemeTableAdapter.Update(dbDS.gi_gametheme)
    Gi_gameplaystyleTableAdapter.Update(dbDS.gi_gameplaystyle)
    Gi_gameTableAdapter.Update(dbDS.gi_game)

These run serially in the backgroundworker, however, so unsure about this. The main thread also waits for it to finish, and there are no other db operations going on before or after this is started.

I did read about going into the dataset designer view, choosing "configure" in the datatableadapter > advanced options and setting "Use optimistic concurrency" to false. This might have worked (hard to say because of the seemingly random nature of the error), however, there are drawbacks to this that I want to avoid:

1. I have around 60 tables. I don't want to do this for each one.
2. I sometimes have to re-import the mysql schema into the dataset designer, or delete a table and re-add it. This would obviously lose this setting and I would have to remember to do it on all of them again, potentially. I also can't find a way to do this automatically in code.

I'm afraid I'm not at code level in terms of the database updates etc, relying on the Visual Studio wizards. It's a bit late to change the stack as well (e.g. can't change to Entity Framework etc).

SO my question is:

1. what is/how can I find what's causing the error?
2. What can I do about it?

thanks

Answer 1

Some of your tables will contain a field that is marked as [ConcurrencyCheck] or [TimeStamp] concurrency token.

When you update a record, the SQL generated will include a WHERE [ConcurrencyField]='Whatever the value was when the record was retrieved'.

If that record was updated by another thread or process or something other than the current thread, then your UPDATE will return 0 records updated, rather than the 1 (or more) that was expected.

What can you do about it? Firstly, put a try/catch(DbConcurrencyException) around your code. Then you can re-read the offending record and try and update it again.

Answer 2

When you have tableadapters that download data into datatables, they can be configured for optimistic concurrency

This means that for a table like:

Person
ID  Name
1   John

They might generate an UPDATE query like:

UPDATE Person SET Name = @newName WHERE ID = @oldID AND Name = @oldName

(In reality they are more complex than this but this will suffice)

Datatables track original values and current values; you download 1/"John", and then change the name to "Jane", you(or the tableadapter) can ask the DT what the original value was and it will say "John"

The datatable can also feed this value into the UPDATE query and that's how we detect "if something else changed the row in the time we had it" i.e. a concurrency violation

Row was "John" when we downloaded it, we edited to "Jane", and went to save.. But someone else had been in and changed it to "Joe". Our update will fail because Name is no longer "John" that it was (and we still think it is) when we downloaded it. By dint of the tableadapter having an update query that said AND Name = @oldName, and setting @oldName parameter to the original value somedatarow["Name", DataRowVersion.Original].Value (i.e. "John") we cause the update to fail. This is a useful thing; mostly they will succeed so we can opportunistically hope our users can update our db without needing to get into locking rows while they have them open in some UI

Resolving the cases where it doesn't work is usually a case of coding up some strategy:

• My changes win - don't use an optimistic query that features old values, just UPDATE and erase their changes
• Their changes win - cancel your attempts
• Re-download the latest DB state and choose what to do - auto merge it somehow (maybe the other person changed fields you didn't), or show the user so they can pick and choose what to keep etc (if both people edited the same fields)

---

Now you're probably sat there saying "but noone else changes my DB" - we can still get this though, if the database has changed some values upon one save and you don't have the latest ones in your dataset..

There's another option in the tableadapter wizardd - "refresh the dataset" - it's supposed to run a select after a modification to import any latest database calculated values (like auto inc primary keys or triggers/defaults/etc). Some query like INSERT INTO Person(Name) VALUES(@name) is supposed to silently have a SELECT * FROM PERSON WHERE ID = last_inserted_id() tagged on the end of it to retrieve the latest values

Except "refresh the dataset" doesn't work :/

---

So, while I can't tell you exactly why you're getting your CV exception, I hope that explaining why they occur and pointing out that there are sometimes bugs that cause them (insert new record, calculated ID is not retrieved, edit this recent record, update fails because data wasn't fresh) will hopefully arm you with what you need to find the problem: when you get one, keep the app stopped on the breakpoint and inspect the datarow: take a look at the query being run and what original/current values are being put as parameters - inspect the original and current values held by the row using the overload of the Item indexer that allows you to state the version you want and look in the DB

Somewhere in all of that there will be the mismatch that explains why 0 records were updated - the db has "Joe" as the name or 174354325 as the ID, your datarow has "John" as the original name or -1 as the ID (it never refreshed), and the WHERE clause is finding 0 records as a result