Important Concurrency questions

[Toni]

Hello folks,


Overview:
We use optimistic concurrency scheme for our database accesss. (We use
timestamp on all of our tables)
Since the data that are currently in memory (dataset) may not be the most
updated one, we anticipate that concurrency problems when we want to update
the database (UPDATE and DELETE) would likely to happen.
What we want is to catch the concurrency violation and ask the user if
he/she wants to overwrite the more current data with the older ones that
currenly in the dataset or abort the operation and reload the dataset.


Working with one table, everything works fine.
But with more than one table things work differently.

For example there are two tables: table A and table B.
(database access always use ado.net transaction)

Updating TableA failed, we got the exception, rollback transaction, ask the
user if he/she wants to overwrite the data.
if the user says yes, we write the data by ignoring timestamp comparison.
if the user says no, we just abort the whole thing.

Updating TableA succeed, TableB failed, we got exception, rollback
transaction, ask the user if he/she wants to overwrite the data.
if the user says yes, we write the data by ignoring timestamp comparison,
but for tableA we got another concurrency violation yet for tableB the
update was a success.

That's pretty strange to me.

I don't know ADO.NET that much thus the way I handle Concurrency problems
may not be the most efficient one.
I don't know if anbody has done the things that I want to accomplish.

Thanks in advance,
-Toni

 

[Mary Chipman]

It sounds like you are using two separate transactions instead of one.
Some things you can do to reduce concurrency violations:

Fetch only the singe row that you are editing by having the user click
an Edit button -- this will load the most recent version from the
database instead of the stale version from the dataset. Commit or
rollback both table A and B in the same transaction. If the
transaction is rolled back, simply load both the version in the
database and the edited version that didn't get saved, and let the
user choose which record they want. If they choose to overwrite, start
a new transaction to write the changes to the database. Holding the
transaction open while the user decides is a bad idea. You want to
keep transactions as short as possible. Normalizing your tables also
helps -- partition your data so that multiple users aren't working on
the same records at the same time.

--Mary