SqlDbx Forum

ptoniolo

Member

Login timeout

Scenario:
- executing a procedure that takes a log time to complete, under a transaction (begin tran)
- after some time I decide to check what is happening using some select with(nlolck) on some log tables
- so I try to open a new connection to the same database

Result:
- my new connection tries to connect, but hangs (probably because I have checked the "show row count option")
- the new connection request blocks the UI of sqldbx
- even if the original connection terminates, I have no way to issue a commit, because the UI is waiting for the transaction of the original connection to close the transaction, in order to complete the login attempt
- the only way to exit from this situation is killing the sqldbx app, and so the original transaction is rolled back

I know that specifying another database (say... master) will solve the problem, or maybe deselecting the option "show row count", but you realize that this is a probolem only when you are already in this catch-22 situation, when it is too late!

This is not the first time I fall in this trap, and it is very annoying baecause you must redo an operation that has been running maybe for one hour or so, with the customer waiting.

I believe that this could be easily avoided if only sqldbx applies some sort of timeout in the login process: if this is complicated, no need to add a config option, if the new connection attempt times out even after an absurd amount of time, say 10 minutes (no connection should require this time), you have the opportunity to realize that this is a problem, but you are not stuck in this situation impossible to solve!

Thanks
Pietro

geo

Member

Re: Login timeout

A simpler solution perhaps: adding with(nolock) to internal queries that populate the UI.

In ptoniolo's scenario above, the first lock is held by
   SELECT name 'Name', USER_NAME(uid) Owner, ..... FROM the_db..sysobjects ORDER BY name

Adding with(nolock) solves the problem and advances to the next query. Setting the isolation level to read uncommitted works too. Please note that some functions (like object_name()) may not  obey the isolation level and thus need to be rewritten as joins.

Unfortunately, Options > Servers > MS SQL Server > Startup Script
is not executed until after populating the object tree, otherwise this would be a great workaround.
I keep a set lock_timeout 5000 there.