Connection timeout when using transactions

[Natan Vivo]

I'm getting a timeout error while trying to select data from SQL server
2005 after opening a transaction in asn asp.net application.

It goes like this:

SqlConnection conn1 = new SqlConnection(...);
conn1.Open();
SqlTransaction trans = conn1.Open();

// do some inserts and updates using the transaction

SqlConnection conn2 = new SqlConnection(...);
conn2.Open();

// select using conn2 and decide what to do

conn2.Close();

// do more inserts and updates based on that decision

trans.Commit();
conn1.Commit();

It probably has something to do with the connection pool returning the
already open conn1 to connection2.

In the actual process, the select using the 2nd connection is being
opened in another method, and I don't want to overload the method to
receive the transaction.

Is there any other option? Is there any way to really open another
connection, or the connection pool to not return open connections being
used in transactions?

Thanks.

 

[Tracy McKibben]

I'm getting a timeout error while trying to select data from SQL server
2005 after opening a transaction in asn asp.net application.

It goes like this:

SqlConnection conn1 = new SqlConnection(...);
conn1.Open();
SqlTransaction trans = conn1.Open();

// do some inserts and updates using the transaction

SqlConnection conn2 = new SqlConnection(...);
conn2.Open();

// select using conn2 and decide what to do

conn2.Close();

// do more inserts and updates based on that decision

trans.Commit();
conn1.Commit();

It probably has something to do with the connection pool returning the
already open conn1 to connection2.

In the actual process, the select using the 2nd connection is being
opened in another method, and I don't want to overload the method to
receive the transaction.

Is there any other option? Is there any way to really open another
connection, or the connection pool to not return open connections being
used in transactions?

Thanks.

Have you changed the default CommandTimeout from the default? I think
the default for ASP/ASP.NET is 30 seconds.

 

[Natan Vivo]

Have you changed the default CommandTimeout from the default? I think
the default for ASP/ASP.NET is 30 seconds.

No, and it is not the problem. The whole process takes less than 1 second.

The thing is exactly what I told. If I open an second connection and
select something, while connection one is in transaction, it hangs and
gives me a connection timeout.

Can anyone reproduce this?

 

[Tracy McKibben]

No, and it is not the problem. The whole process takes less than 1 second.

The thing is exactly what I told. If I open an second connection and
select something, while connection one is in transaction, it hangs and
gives me a connection timeout.

Can anyone reproduce this?

Not knowing what selects you're running in connection 2, I'd have to say
you're probably deadlocking against the open transaction in connection 1.

http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock

 

[Neil B]

No, and it is not the problem. The whole process takes less than 1 second.

The thing is exactly what I told. If I open an second connection and
select something, while connection one is in transaction, it hangs and
gives me a connection timeout.

Can anyone reproduce this?

Yes, I had the exact same problem in an application of mine. In the
absence of finding a solution, I coded around it. Sorry I can't be of
more help, but you're not alone.

Neil B

 

[Damien]

I'm getting a timeout error while trying to select data from SQL server
2005 after opening a transaction in asn asp.net application.

It goes like this:

SqlConnection conn1 = new SqlConnection(...);
conn1.Open();
SqlTransaction trans = conn1.Open();

// do some inserts and updates using the transaction

SqlConnection conn2 = new SqlConnection(...);
conn2.Open();

// select using conn2 and decide what to do

conn2.Close();

// do more inserts and updates based on that decision

trans.Commit();
conn1.Commit();

It probably has something to do with the connection pool returning the
already open conn1 to connection2.

No, that's not how the connection pool works. If you have two open
connections in your code, you'll be using two connections from the
connection pool.

In the actual process, the select using the 2nd connection is being
opened in another method, and I don't want to overload the method to
receive the transaction.

Is there any other option? Is there any way to really open another
connection, or the connection pool to not return open connections being
used in transactions?

From your description, it's almost certainly a deadlock on sql server.

Without seeing the database code, it's going to be difficult to help
you out.

To confirm it's a deadlock, open a connection using query analyser,
start your code running, then run sp_who2. It'll show your second
connection being blocked by your first.

Damien

 

[Natan Vivo]

Not knowing what selects you're running in connection 2, I'd have to say
you're probably deadlocking against the open transaction in connection 1.

http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock

Thanks. It was really a deadlock, the select was selecting data from the
same table I had already update.

It seems I can solve this by using the right IsolationLevel when
begining the transaction.

I'll test it later. Thanks!

 

[Tracy McKibben]

Thanks. It was really a deadlock, the select was selecting data from the
same table I had already update.

It seems I can solve this by using the right IsolationLevel when
begining the transaction.

I'll test it later. Thanks!

Be careful that you're not reading "dirty" data, meaning data that is
inconsistent because it's been partially altered by a transaction...