Re: ADO.NET Transaction Locking

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?

"scott" <scott@xxxxxxxxxxxxx> wrote in message
news:825A949E-D6DA-4FFC-9985-96A69FD1569A@xxxxxxxxxxxxxxxx


"Marina Levit [MVP]" wrote:

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.

"scott" <scott@xxxxxxxxxxxxx> wrote in message
news:2F64AACF-34DE-41EA-8987-70FF2A5905F2@xxxxxxxxxxxxxxxx
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.



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.


.

Relevant Pages

  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: controlling lock order in transactions
    ... Andrew J. Kelly SQL MVP ... > ROLLBACK TRANSACTION; ... > find in the clients sp that could be causing the dead lock is the join on ...
    (microsoft.public.sqlserver.programming)
  • Re: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: Using ADO for locking a row for Dpecific Time
    ... >using that connection, running some procedures etc & then unlock that row ... still wondering WHY you want to lock a row that you don't plan to update. ... It's impossible without transaction, as locks are always released at the ... -- Do all the updates and stored proc executions that need to be done. ...
    (microsoft.public.sqlserver.programming)
  • Re: Tansaction/locking MSSQL2000
    ... >transaction behavior in single statements [not a BEGIN TRANSACTION ... >tables I need to lock so there is not a race later]? ... If the table is already held by an exclusive lock ... When the transaction is finished (due to ROLLBACK/COMMIT, releasing the ...
    (microsoft.public.sqlserver.programming)