sqltransaction timeout

[samuelberthelot]

Hello,
I have a asp.net application in which users can create content and
then save everything to a SQLServer2000 database.
When they hit the save button on the form here is what I do :

Dim conn As SqlClient.SqlConnection = New
SqlClient.SqlConnection( myconnectionstring )
Dim tx As SqlClient.SqlTransaction

conn.Open()
tx = conn.BeginTransaction("Cutting Saving")

here I save stuff to the database (_adpCutting is my dataadapter)
_adpCutting.UpdateCommand.Transaction = tx
_adpCutting.InsertCommand.Transaction = tx
_adpCutting.Connection = c
_adpCutting.Update(_dtCutting)

if everything succeeded :
tx.Commit()

otherwise I rollback


I've noticed that when I'm debug mode and I stop just after
_adpCutting.Update(_dtCutting), then I can't access my SQL table from
anywhere (even from SQLServer Enterprise or any other applications).
It's like the transaction is holding the table, until I commit or I
rollback.

This wasn't an issue up until now. But with a growing number of users
and applications using this one table in the database, now I'm getting
loads of timeout errors. I'm not going to increase the timeout value
to be greater than 30 seconds, it doesn't make sense. Is there another
way to solve this issue ?

I'm not making progress on this, so I'd appreciate any help

thanks

 

[Mary Chipman [MSFT]]

What you are experiencing is expected behavior. Once you start any DML
operation, SQL Server places exclusive locks on all of the resources
involved in the transaction. These locks are then held until the
transaction either commits or rolls back. SQL Server does not even
allow locked data to be read until the locks are released, which is
why you're locked out in the middle of your tx and users are blocked
and timing out. For more information about what's happening on the
server, see Isolation Levels in the Database Engine
http://msdn2.microsoft.com/en-us/library/ms189122.aspx in SQL BOL. The
topic applies to SQLS 2000 as well as 2005 (just ignore the snapshot
isolation bits).

FWIW, if concurrency is important, put your explicit transactions in
stored procedures, not client code. You can use error handling to
manage concurrency problems in a way that is transparent to the users.

-mary