ADO.NET Transaction Locking

[Guest]

I have a transaction that is Updating or inserting multiple tables(12 or
more) This transaction takes about a 2 seconds to perform. During that two
seconds. We are getting timeouts expired errors from other clients
requesting records from the same 12 or so tables. What setting is there to
say only lock the changed or new records do not lock the entire table which
is what appears to be happening?

I am using SPs to do all inserting and a multiple command objects.

What is occuring in the ado.net lock is it a table lock page lock or record
lock.
These are simple insert or updates, except for one procedure that validates
the data.

 

[Marina Levit [MVP]]

Usually, this works fine without anything special. I don't understand why if
the entire transaction takes 2 seconds, why other clients are timing out,
even if the transaction is locking the whole table? That should be quick
enough, so that the other clients can just wait the 2 seconds, and then the
data they want should be available again.

In general, make sure your updates and anything your stored procedure is
doing, is using the table index for its queries, so table scans aren't being
done.

 

[Guest]

Usually, this works fine without anything special. I don't understand why if
the entire transaction takes 2 seconds, why other clients are timing out,
even if the transaction is locking the whole table? That should be quick
enough, so that the other clients can just wait the 2 seconds, and then the
data they want should be available again.

In general, make sure your updates and anything your stored procedure is
doing, is using the table index for its queries, so table scans aren't being
done.

My updates are primary key based and my inserts are just that inserts and
they only insert on record at a time. I was using the default command timeout
of 30 seconds I up it to 60 seconds which I think is way over kill. Not sure
what else to offer it is realy basic code BeginTransaction call Proc1 to n
CommitTransaction if an error occurs anywhere in between rollback
transaction. The only thing that may be out of the ordinarily is in a
seperate connection I log events telling me what step it is on, this is how I
know it is taking 2 seconds or more.

 

[Marina Levit [MVP]]

So these deadlocks keep going, long after the transaction completed? If the
transaction completes in 2 seconds, the previous request that was made
during the transaction, still continues to wait for 58 seconds, and then
says it timed out? That sounds odd to me.
Do you have a simple re-pro case of this?

 

[Guest]

So these deadlocks keep going, long after the transaction completed? If the
transaction completes in 2 seconds, the previous request that was made
during the transaction, still continues to wait for 58 seconds, and then
says it timed out? That sounds odd to me.
Do you have a simple re-pro case of this?

Unfortantaley no I cannot reproduce the error since it is happening at most
twice a day. The sequence is such that it is a timing issue.