Multiple connection problem
-
I've an strange case where a second connection to a Sqlserver database hangs until time out has expired, the schema is below:
Connection1=New connection(database1) Connection1.Open Connection1.Begintrans ... Connection2=New connection(database1) Connection2.Open Command2.Connection=Connection2 Command2.ExecuteSelectionSQL **|-----------------------here it hangs** Connection2.Close ....... Connection1.Commit Connection1.CloseThe first connection remains opened and in transaction while connection2 is opened with the same querystring, it opens correctly but when I issue a command using connection2 it hangs. I use System.Data.SqlClient and I've tried different options in querystring (Enlist=false,.....) but I cannot solve the problem. Thanks for your help, :) Hzi. -
I've an strange case where a second connection to a Sqlserver database hangs until time out has expired, the schema is below:
Connection1=New connection(database1) Connection1.Open Connection1.Begintrans ... Connection2=New connection(database1) Connection2.Open Command2.Connection=Connection2 Command2.ExecuteSelectionSQL **|-----------------------here it hangs** Connection2.Close ....... Connection1.Commit Connection1.CloseThe first connection remains opened and in transaction while connection2 is opened with the same querystring, it opens correctly but when I issue a command using connection2 it hangs. I use System.Data.SqlClient and I've tried different options in querystring (Enlist=false,.....) but I cannot solve the problem. Thanks for your help, :) Hzi. -
seems like you have locking problems .. do you access the same tables ? in both connections ?
-
Yes, when I'm accessing the same table, it hangs, but when I access another table it not hangs, but SQLServer locks a whole table when I insert a single record ?. Thanks
No.. locking is not this simple..it differs according to how you access the table.. tell me the query so i can help !!
-
No.. locking is not this simple..it differs according to how you access the table.. tell me the query so i can help !!
Here is a trace of the steps shown in sqltrace tool:
**with the first connection** exec sp_reset_connection SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION sp_reset_connection update objectes set [Caducitat]=NULL, [DataValid]=NULL, [UltimaMod]=CONVERT(DATETIME, '2003-01-16T13:18:00', 126) where id=100225 **with the second connection** sp_reset_connection select * from objectes where idref=''BAINtiA000046'' and IM1=1' **<-------------It hangs here**It's all. Thanks. -
Here is a trace of the steps shown in sqltrace tool:
**with the first connection** exec sp_reset_connection SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION sp_reset_connection update objectes set [Caducitat]=NULL, [DataValid]=NULL, [UltimaMod]=CONVERT(DATETIME, '2003-01-16T13:18:00', 126) where id=100225 **with the second connection** sp_reset_connection select * from objectes where idref=''BAINtiA000046'' and IM1=1' **<-------------It hangs here**It's all. Thanks.