Deadlocks and DataSets general issue

[MikeG]

I'm in the process of writing some generic dataaccess code to save
datasets to the database (generating SQL etc.)

I've not had a problem yet but I'm just checking in case anyone else
understands this better than me.

Any feedback appreciated.

If I have a dataset with two related tables (parent-child) then I
probably also want to save the dataset in a single transaction. In
this case, I need to do inserts and deletes in a different order so as
not to violate FK contraint in the database.
i.e.
-When inserting data: We must insert parent records first and then
insert child data second.
-When deleting data: We must process the datatables in the opposite
order, in other words delete from the child records first and then
delete from parent records second.

Now in my mind this is at odds with the stragegy for avoiding
deadlocks which is that you should process tables in the same order.
i.e.
-Client 1 saves dataset that has deleted rows in 2 related tables
-Client 2 saves dataset that has inserted rows in same 2 tables
If both updates start at same time...
1-Client 1 locks child table
2-Client 2 locks parent table
3-Client 1 tries to lock parent table, but is blocked by Client 2
4-Client 2 tries to lock child table but is blocked by Client 1
5-Deadlock!

That was all just the background... these are my questions:
"Is it true that trying to update data from multiple tables is likely
to result in deadlocks?"
"Does it depend on how much of table/page/row is locked in the
database"

Thanks,
MikeG

 

[Frans Bouma [C# MVP]]

(e-mail address removed) (MikeG) wrote in

That was all just the background... these are my questions:
"Is it true that trying to update data from multiple tables is likely
to result in deadlocks?"

this will deadlock: (if not using the same connection object)

1) UPDATE Foo SET Bar = @value WHERE Id = @otherValue
2) SELECT * FROM Foo Where NonIndexedField = @someValue

2) will deadlock, because 1) has exclusively locked a row which is
scanned by 2) in another connection and thus transaction.

So you will ONLY run into deadlocks if you select a row from a
table based on a non-indexed column filter and in that table are also rows
updated using another connection, AND you execute the update and select
from the same .NET routine (so they wait on eachother)

"Does it depend on how much of table/page/row is locked in the
database"

No. 1 row is enough

FB