DataSet/DataAdapter Update fails intermittently on multi-processor machine

  • Thread starter Thread starter Ben Gavin
  • Start date Start date
B

Ben Gavin

Hi,
I am running into an interesting problem. I have a process which
populates a strongly typed dataset from a database, updates the contents
(including adding new rows), and stores the contents back to the database.
All loads/updates are done via a SqlDataAdapter utilizing custom stored
procedures that have been built for this purpose.

The behavior that I am seeing is that on a rather intermittent basis, the
SqlDataAdapter.Update call will fail with a "Column 'x' is constrained to be
unique. Value 'y' already exists" (or something close to that). This
appears to happen most often on DataSets which contain a large number of
records (5000+), although I believe it has happened on smaller DataSets as
well.

To add to the confusion, resubmitting the same update a second time does
not necessarily cause the problem to occur again. This particular update is
part of a larger job, which if resubmitted, many times completes
successfully. Additionally the production server is a multi-processor
(dual) machine, and all development and testing systems are single processor
machines. I have done extensive code review, and have been unable to find
the reason behind these errors. The dataset definition does not change, the
code itself does not change (on the database or application server), and the
input data does not change, yet the job miraculously heals itself.

Is it possible that there is a race condition of sorts within the
DataAdapter's update methods which is causing this error to occur? Is it
possible that something within SQL Server 2000 is causing duplicate IDs to
be generated? The update itself is not running within a transaction, but
I'm fairly certain that I've seen the error for updates that were performed
within one.

Thanks,
Ben
 
What datatype is your primary key? Do you depend on SQL to assign the key
value. Maybe an int datatype? If the database is not in production yet,
change your PK field types to Guid? It makes it alot simpler to write code.

When new records are being created in the datatable, how are you assigning a
temporary key value? Upon updating and receiving updated key values for the
new records, how are you updating the temporary values into the newly SQL
assigned values?
 
Once you add records into the datatable, use the locals window (while
debugging) to navigate to the new DataRow in the DataTable. Look at the
values for that DataRow. What is the value for the PK of that new DataRow?
ADO.NET is assigning some kind of temporary value is it not?

You can try setting "DataSet.EnforceConstraints" to false before an update,
and then back to true afterwards. You'll probably get the same error
message when you set it back to true. Or does the row have the new key
value assigned by SQL?
 
OK,
After thinking about the problem a bit, how does the DataSet treat inserts
that have failed? Is it possible that some of my inserts are failing (for
whatever reason) and no exception is being thrown? If that were the case,
does a call to Update() automatically re-enable the constraints after
completion? I could imagine a scenario where ADO.NET tracks inserts, since
the auto-number field is assigned by the database, it leaves these fields
empty and the unique constraint ignores "new" records.

Then during the update process, one or more row inserts fail, but the
corresponding datarow gets marked as "existing", thus no longer ignored by
the unique constraint. The first failure would probably not result in an
error, since the "new" row would have some default-assigned value, however a
second failure would leave another row with the same "default assigned" key
value. At that point the unique constraint causes an exception and the
process exits.

Is such a scenario possible? I can't see how it would happen, since I'm
using strongly typed datasets, so if the eventual DB insert were to fail, it
would seem that adding the entry to the dataset should also fail. Maybe a
database timeout, or temporary locking problem?? All these seem a bit
remote because of how the system is used, but they certainly aren't
impossible...

I guess I'm still hoping that _someone_ else has seen similar behavior, even
if they haven't themselves found a solution for it.

Thanks,
Ben
 
Ben,

Are these tables quite small?

I wonder if you are encountering collisions on the client side. Try using a
negative 1 seed and increment in the client side DataTables.
 
Back
Top