Bound Connections

During the course of a transaction, the process that initiated the transaction acquires exclusive locks on the data that is modified. These locks prevent other user processes or connections from seeing any of these changes until they are committed. However, it is common for some SQL Server applications to have multiple connections to SQL Server. Even though each connection might be for the same user , SQL Server treats each connection as an entirely separate SQL Server process, and by default, one connection cannot see the uncommitted changes of another, nor modify records locked by the other connection.

Bound connections provide a means of linking multiple connections together to share the same lock space and participate in the same transaction. This can be useful, especially if your application makes use of extended-stored procedures. Extended-stored procedures, although invoked from within a user session, run externally in a separate session. The extended-stored procedure might need to call back into the database to access data. Without bound connections between the original process and the extended-stored procedure, the extended-stored procedure would be blocked by the locks held on the data by the originating process.

Bound connections are of two types: local and distributed. Local bound connections are two or more connections within a single server that are bound into a single transaction space. Distributed bound connections make use of the Microsoft Distributed Transaction Coordinator (described in more detail in the later section "Distributed Transactions") to share a transaction space across connections from more than one server. This section will discuss how to set up and use local bound connections.

Creating Bound Connections

Binding connections together is actually fairly simple and requires the acquisition of a token by the first process that can be passed to another connection that identifies the lock space to be shared.

A bind token is acquired using the stored procedure sp_getbindtoken . This stored procedure creates a bound connection context and returns the unique identifier for this through an output parameter:

 sp_getbindtoken  @TokenVariable  OUTPUT [, @for_xp_flag] 

The @TokenVariable is a variable defined as a varchar(255) and is used to receive the bind token from the stored procedure. If you pass the @for_xp_flag argument a 1 , the stored procedure will create a bind token that can be used by extended-stored procedures to call back into SQL Server.

NOTE

Only the owner of a connection can gain the bind token for it.

After you have the bind token, you have to pass it to the intended co-client, who then uses a different stored procedure, sp_bindsession , to participate in your transaction context:

 sp_bindsession [  @TokenVariable  NULL] 

The @ TokenVariable is the value created in the previous step. The NULL value is used to unbind a connection from another. You can also unbind a connection by executing sp_bindsession without arguments.

CAUTION

Some users have reported that attempting to unbind from a connection while a transaction is still active might cause all the involved connections to hang. It is recommended that after a connection is bound to another, you should wait until the completion of the transaction before attempting to unbind it.

To illustrate the use of these procedures together, consider the following code:

 DECLARE @token VARCHAR(255)  EXECUTE sp_getbindtoken @token OUTPUT 

This results in the following value for @token :

 NQ9---5---.Q>Z4YC:T>1F:N-1-288TH 

Each call to sp_getbindtoken results in a different value. Depending on who the intended recipient is, you must find some way to programmatically communicate this value to them, which they then use in the call:

 EXEC sp_bindsession 'NQ9---5---.Q>Z4YC:T>1F:N-1-288TH' 

In addition to sharing lock space, bound connections also share the same transaction space. If you execute a ROLLBACK TRAN from a bound connection, it will roll back the transaction initiated in the orginating session. It is recommeded that all transaction control statements be kept in the initial connection. If an error occurs in a bound connection, it should return an error code to the originating session so that it can perform the appropriate rollback.

Binding Multiple Applications

If you bind connections across applications, you have to find a way of communicating the bind token so that it can be used with sp_bindsession . SQL Server does not provide a simple solution to this problem; you can consider mechanisms like these:

• Using an IPC such as Remote Procedure Calls (RPC), Dynamic Data Exchange (DDE), or Net-DDE.

• Placing the bind token in a file that is accessible by each application.

• Storing the bind token in a SQL Server table. You might also create a stored procedure to manage the assignment of the token to the requesting applications.

• If the applications are local, you might be able to pass the token through global or shared memory, or directly with a function call.

An important downside exists to using bound connections: sequential processing. Only one connection out of all the connections bound together can actually be doing any work at any given time. This means that during a result set retrieval, the entire result set must be retrieved or the command canceled before any other work can be done by a participating connection. Any attempt to perform an operation while another operation is in process results in an error that should be trapped so that you can resubmit the work after a certain time interval.