Concurrency Violation best practices

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
[email protected]
Posts: 38
Joined: Tue 07 Mar 2006 17:13

Concurrency Violation best practices

Post by [email protected] » Wed 20 Sep 2006 15:34

I have a table that has an autoincrement primary key called 'ID' and a test field called TestData as VARCHAR(45).

In my code I create a MySQLDataTable and connect successfully to my table with "Select * from MyTable".

I then add a new row as follows:

Dim dr as DataRow = myTable.NewRow
dr("TestData") = "Random String"
myTable.Rows.Add(dr)
myTable.Update

Everything works as expected. Here's my question:

In theory, if two users are running this application concurrently, it is possible that they both might try to add a new row concurrently. One of the two could (and should) get a Concurrency Violation if they both try to add a row with the same ID. If this happens, I'd like the client app that gets the Concurrency Violation to try to increment the ID field and try again until it finds an unused 'ID' value.

What's the best (simplest) method to do this that CoreLab recommends? Can you provide a sample snippet of VB 2005 code that will do this using the MySQLDataTable component? I would prefer to not have to manually adjust the SQL code at runtime if possible. Can I increment the ID field in the Catch block until it successfully inserts the new row?

I haven't actually run into this scenario, but when I deploy to a wider audience I know it will happen and would prefer to have the cod ein place to catch it gracefully.

Many thanks,

John

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 21 Sep 2006 08:46

Don't worry about that. MySQL Server itself will treat such situation appropriately. You'll never get a concurrency violation.

Post Reply