sqltransaction timeout issue

[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

 

[Daniel Marohn]

Hi

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).

I guess the IsolationLevel of the transaction is serialisable. Do you really
need this level? If yes, there is no solution for your problem, otherwise
set it to the really needed level.

Greets
Daniel

 

[samuelberthelot]

I was not specifying any level in particular so I guess the default is
readcommited ?
The problem is that whatever isolation level I specify in
BeginTransaction, while the transaction is being executed, no one can
access the table in the database. It should only be the case if I was
using SERIALIAZABLE as you mentionned it. So is there any chances that
SQL Server is actually overriding whatever values I use with another
value of its own (serializable most likely) ? How can I check that ?

This is giving me a really bad time, and I don't know how I'm going to
solve this