LINQ and "Primary Key Violation"

[Paul]

Hi Everyone,
I am have a VS2008 multi-threaded application that process a list of
files by adding each file to a database. The directory names in one
table and file information in another. Since the threads are all doing
the same thing, only one could be he first to add a new directory. The
first add succeeds. Since the directory name is the key, the 2nd add
fails with an SQL exception (Primary Key Violation). I was expecting a
LINQ DuplicateKeyException, but I end up with an SQLException and all
subsequent LINQ operations in the datacontext fail. Any ideas?

Thanks,
Paul

VS2008 using SQL Express

 

[Mr. Arnold]

Hi Everyone,
I am have a VS2008 multi-threaded application that process a list of
files by adding each file to a database. The directory names in one
table and file information in another. Since the threads are all doing
the same thing, only one could be he first to add a new directory. The
first add succeeds. Since the directory name is the key, the 2nd add
fails with an SQL exception (Primary Key Violation). I was expecting a
LINQ DuplicateKeyException, but I end up with an SQLException and all
subsequent LINQ operations in the datacontext fail. Any ideas?

You check for the primary-key by querying for it before you do the add.

If the key exist, then you take another path to deal with the
duplication, otherwise, you do the add.

 

[Paul]

You check for the primary-key by querying for it before you do the add.

If the key exist, then you take another path to deal with the
duplication, otherwise, you do the add.

Thank you for the idea, but I do that now. Each thread looks to see if
the path already exists, but there is a race condition where each see
that the path does not exist, one adds it and the other looses. Do I
really need to synchronize this? Only one thread can check for the
existence of a path at a time? This seems horribly inefficient. Surely
this is what exceptions are meant for.

Paul

 

[Paul]

Maybe you need to just check for the dup-key exception on a try/catch
and clear the exception and continue processing.

I found that if a tried to add an existing path with a single threaded
test app, I will receive a LINQ DuplicateKeyException. When two threads
try to add the same path I get SQLException (primary key violation). I
expected the failure to be the same regardless of how the database was
updated. The SQLException will render the DataContext useless.

For now I have added a critical section around the path addition so that
only one thread can add at a time. This solves the problem, but seems
more strict than necessary.

Thanks for your help.
Paul