Cause analysis and resolution of orphaned transactions due to connection pooling enabled when you connect to SQL Server

The source of this article: http://www.cnblogs.com/wy123/p/6110349.html

You've encountered a situation like this before:

Some of the sessions connected to the database will be blocked at irregular intervals, which can be short, sometimes longer, and have a short duration and no regularity.
After parsing the related stored procedures and code, it was found that the transaction was opened in the stored procedure, and the application did not perform special processing (commit or rollback) after the call to the stored procedure exception.
Then, after the execution method exception, the connection is closed, but the database is left with an active transaction (DBCC OPENTRAN corresponds to the SessionID is the sleeping state), resulting in blocking.
The key is that the activity of the transaction will not be scheduled to disappear, it is a bit strange, this is the focus of this article.

This mechanism is associated with connection pooling:

When the application connects to the database, the connection pool is opened, and if the application calls a stored procedure that opens a transaction operation,
When an exception occurs, it is possible that the database connection is closed, and the transaction in the stored procedure is neither committed nor rolled back.
In this case, an "orphaned transaction" is generated, that is, because the data volume of the open transaction is disconnected and the transaction is still active,
In fact, when the connection pool is turned on, the database connection is closed, not physically shut down, but the database connection is returned to the connection pool.
If there is no external intervention, including no connection to the database is not reused, or the connection is not physically fractured, or the application is not restarted, or there is no database server, the transaction will continue.
Because the active transaction blocks the other session from exclusive access to the related table, it is represented as blocking.

How to tell if connection reuse has occurred in a connection pool

First, is there a connection in the connection pool that is not reused in the process of connecting to the database, and what are the differences in SQL Server?
For example, if you add Pooling=false to the connection string, the connection pool is not enabled.
As follows, two consecutive database accesses are performed, and two database accesses are added to the connection string Pooling=false; Indicates that connection pooling is not enabled

　　

The following is the observation of the connection action in profile, note that after the first disconnection here, there is a logout, the second time to access the data, there is a login

If the above two methods in the connection string pooling=false, change to Pooling=true, and then execute two consecutive methods,
Before the second connection data is discovered, that is, after the first logout, there is an exec sp_reset_connection action before the second login.
EXEC Sp_reset_connection's execution marks the connection being reused from the connection pool, and the role of this action

What happens when a database connection is closed and the transaction remains active

First, refer to the following, write a transactional stored procedure, with WAITFOR delay ' 00:00:50 ' way to prolong its transaction commit time, resulting in a connection timeout (default ADO 30 seconds)

Call this stored procedure in ADO, the connection exceeds 30 seconds after the timeout exception, the current execution method of the database connection is closed, the visual Studio is not closed at this time, the simulation application does not terminate

The active transaction in the query data is then found to have an active transaction, which was caused by the last execution of "timeoutfunction".
At this point, "Timeoutfunction" has an exception, the database connection is closed gracefully,
At this point, the execution of this method is caused by the transaction is still active, as follows

While observing SessionID = 57 of the state, he is sleeping Ah, has begun to sound asleep.

If you perform a query operation on a table in a transaction at this point, you will find that it is blocked, in fact T1 This table does not have a single row of data before the above method executes

Database connection is reused, the first connection to a transaction left in the database is rolled back

After executing the first method, the debug state of VS is not interrupted, and we proceed to the second method, at which point the second method reuses the database connection of the first method.
As for why he used the first method of the database connection, said from the beginning.
When exec sp_reset_connection is executed, the active transaction is rolled back. The query will execute normally. As follows

The query ends normally after exec sp_reset_connection because the transaction is rolled back, so the T1 table has no data

The example above simulates a scenario like this when a connection pool is opened in the connection string
When a method execution timeout connection is closed, the transaction in its calling stored procedure does not explicitly commit or rollback, causing the connection to close and the transaction to remain active
For example, a Web application, when a method executes, the connection times out but gracefully shuts down (returns the connection pool), the transaction remains active,
At this point the Web server does not stop, that is, the application does not close directly, which is similar to Visual Studio continues to maintain the debug state,
At this point, the transaction remains active until the connection is reused (or the application is closed), and the other session initiates an object that locks on the active transaction, and the blocking occurs.
The problem here is that, subjectively, there is no guarantee that the connection pool will be reused, and there is no guarantee as to how long the active transaction lasts.
If the active transaction is maintained, then the blocking remains, which is obviously unacceptable

On the role of Sp_reset_connection, I'm too lazy to type, refer to the Microsoft SQL Server Enterprise Platform Practice page No. 316

How to prevent a connection from shutting down while the transaction remains active

　　1, because the connection is closed because the stored procedure execution time exceeds the time of the connection, you can start by analyzing the cause of the transactional operation timeout.

2, you can catch in the code of the application, in the event of exception processing, to ensure that the connection is closed before the active thing is finally committed or rolled back (to make explicit processing)

3. Close the connection pool, in which case any database connections that are closed will be rolled back, but the connection pool is also not feasible to submit data performance.

4, from the performance and connection pooling mechanism analysis, can only alleviate this problem, and escape this problem,
In fact, it is useless to add a try catch in a transactional operation of a stored procedure, in the case of a connection time-out disconnection but a transaction continuing to remain active.
You can then use the SET XACT_ABORT on command to make sure that the transaction is rolled back in any exceptional circumstances. Refer to Books Online for Xact_abort.

Cause analysis and resolution of orphaned transactions due to connection pooling enabled when you connect to SQL Server