BUG: Error 3910, "Transaction Context in Use by Another Session" (279857)
The information in this article applies to:
- Microsoft SQL Server 7.0
- Microsoft SQL Server 2000 (all editions)
This article was previously published under Q279857
BUG #: 58513 (SQLBUG_70)
BUG #: 210763 (SHILOH_BUGS)
SYMPTOMS
Transferring data from one table to another in the same database with a Data Transformation Services (DTS) package with the Join Transaction If Present option enabled in the Workflow Properties generates error 3910:
Transaction context in use by another session.
CAUSE
This problem is caused when both of the following occurs:
- DTS enlists the package in a Distributed Transaction Coordinator (DTC) transaction.
-and- - The first "SELECT * FROM <source_table> statement is not stopped when the enlist operation is triggered.
NOTE: The package automatically performs this SELECT query on both the source and destination tables.
Thus, the enlisting transaction is affected by a new system process ID (SPID).
When this occurs, there are two different SPIDs in a single DTC transaction, and both connections/SPIDs are connected to the same server. In SQL Server, it is not permitted for two connections in the same transaction to execute a query against the same SQL instance concurrently.
If the number of rows returned by the first SELECT is small enough to fit within a single network packet (4096 bytes by default), SQL Server can send the entire resultset immediately and does not need to wait for the client (DTS) to fetch any rows. Because the whole resultset was sent in a single packet, SQL can clean up and release all resources associated with the query before any queries are attempted on the second connection. It is therefore by design and fully expected that the error does not occur when the rowcount is small.
In SQL Server 7.0, the error is:
- Transform data task: undefined
- Transaction context in use by an other session
- In SQL Server Profiler you will see: Exception, Severity=25, Event Subclass=67, IntegerData=3617
In SQL Server 2000, the error is:
- An exception occurred when handling the control request.
- Connection 'MySecondConnection' for Task DTSDataPump does not support joining distributed transactions or failed when attempting to join. New transactions cannot enlist in the specified transaction coordinator.
- In SQL Server Profiler you will see: Exception State=2, Severity=16, Error=3910, Integer Data: <empty>
WORKAROUND
To work around this problem, do any one of the following:
- Do not enlist the Data Pump in a transaction.
- Transfer data by using Transact-SQL scripts rather than DTS. Moving data without transformation is much faster with a query.
- Place the source and destination databases on different servers.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0 and 2000.
MORE INFORMATION
In SQL Server, many database connections may be enlisted in the same transaction; however, only one of those SPIDs may be executing a query at a given time. This is by design.
| Modification Type: | Major | Last Reviewed: | 10/16/2003 |
|---|
| Keywords: | kbbug kbpending KB279857 |
|---|
|