1

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

stigzler
  • 793
  • 2
  • 12
  • 29
  • I would never believe it is late to change the ORM stack (it's always an option if you really mean to). I also would set "Use optimistic concurrency" to false on all 60 tables if I were you. That said, every service handling db operation always have something like Gi_gamethemeTableAdapter.Open, Gi_gamethemeTableAdapter.BeginTransaction(), Gi_gamethemeTableAdapter.Begin()... and then Gi_gamethemeTableAdapter.Close(), Gi_gamethemeTableAdapter.Commit(), Gi_gamethemeTableAdapter.SaveChanges(). This is usually used for opening and closing transactions on the db. – Oluwadamilola Adegunwa May 25 '21 at 22:35

2 Answers2

1

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 youre 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 retreieved, 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

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • This one of the best answers I've ever received on SO. Thank you so much. It's given me something to work from. From your informed response, I'm wondering a few things: 1. "The database has updated itself": This makes me think of the question I posed [here](https://stackoverflow.com/questions/67626618/foreign-key-constraints-not-imported-through-visual-studio-data-source-wizard) about foreign key constraints. I don't think it's that in this instance, but this could feasibly happen if DELETE CASCADE causes a record removal in the db, but not in the dataset. [Cont...] – stigzler May 25 '21 at 21:11
  • 2. My structure is DataTable>BindingSource>DataGridView. Before I do the [DataTableAdapter].Update([DataTable]), I do [BindingSource].EndEdit(). If I missed a crucial linked table off - could this have something to do with it? 3. I'm assuming then that the fact that the DataTableAdpater.Update is run on a separate thread is not effecting it? – stigzler May 25 '21 at 21:12
  • Sorry - one final daft question. I can see the datarow via the intellisense explorer. However, exploring these (there's ex.row visible) you cannot access datarow.original from this - it only shows the current? I did place a try, catch around it to try and expose ex.row, but .row is not available so can't access the row versions via this route either. Hope that makes sense... – stigzler May 25 '21 at 22:04
  • 1 potentially. While I'd expect that a dataset with related tables and a database with fk'd tables could/would be configured the same (the data relation in the c# side can be configured for different cascade behavior than the db which could then give rise to a mismatch) I don't recall ever looking at how columns that are FKs are scripted when the datasetgenerator makes optimistic queries.. it could be that they wouldn't be expected to change and hence don't have an old/new value comparison baked in (not in a position to check atm) – Caius Jard May 26 '21 at 07:19
  • 2. If you are working with related datatables (datatables in a dataset that are linked via datarelations) and bindingsources you remain aware that doing a new row on the bindingsource will create a detached row that is committed to the table only when endedit is called, and editing a row will only commit the changes when endedit is called.. but from a ui standpoint EndEdit probably already occurred when the datagridview lost focus.. Generally I think you'd struggle to achieve a situation where an edit hasn't been committed and it impacts an ability to save because the row remains consistent – Caius Jard May 26 '21 at 11:06
  • with the DB regardless of the edits you have/havent committed to it. In other words, your editing of it doesn't influence the original values, so the only way that a concurrency violation occurs there is if the DB is different. Of course, if you do an AcceptChanges on the row, then that modifies Original values (using the Current values), but if you haven't saved those changes to the DB then you've sent the row out of sync with the DB, which will also generate a concurrency violation. – Caius Jard May 26 '21 at 11:09
  • 3. True, you cannot see anything other than current row values via the dataset visualizer (magnifying glass in the tooltip when pointing to a dataset/table). If you want original values it might be easiest to paste this method into your code somewhere `public static string RowInfo(DataRow r) => string.Concat(r.Table.Columns.Cast().Select(c => $"Column={c.ColumnName}, Current={r[c, DataRowVersion.Current]}, Original={r[c, DataRowVersion.Original]}\r\n"));` – Caius Jard May 26 '21 at 11:43
  • and then when the debugger breaks do something like `?RowInfo(YOUR_DATA_ROW)` or `Clipboard.SetText(RowInfo(YOUR_DATA_ROW))` in the Immediate window, so you can inspect the values (or paste them somewhere else). ps; dont call it on a Added row, as such a row has no Original data – Caius Jard May 26 '21 at 11:43
  • Hey. Just to thank you for the comprehensive replies again. I haven't forgotten this, but can't mark it as the answer at the moment, because, weirdly, I haven't been getting the concurrency violation error of late. Not sure what's going on, but I have a sneaking suspicion it will rear its ugly head again at some point, at which point i will try your suggestions. – stigzler May 29 '21 at 11:40
0

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.

Neil
  • 11,059
  • 3
  • 31
  • 56