Bound connections allow two or more connections to share the same transaction lock space and the same transaction, and to work on the same data without lock conflicts. Bound connections can be created from multiple connections within the same application, from multiple applications on the same computer with separate connections, or from multiple applications on different computers with separate connections.
By using bound connections, an extended stored procedure can share the transaction space of the connection that called it by invoking the extended stored procedure. Bound connections make coordinating actions across multiple connections easier.
To participate in a bound connection, the first connection gets a bind token, a character string that SQL Server uses to keep track of the bound transaction and that uniquely identifies each bound transaction, by using sp_getbindtoken or srv_getbindtoken (Open Data Services). The bind token is then transmitted or passed to the other connections that will participate in the same bound connection. The other participating connections call sp_bindsession by using the bind token received from the first connection.
Think of a bound connection as a conference call. Person P1 places a telephone call or connection to person P2. To add person P3 to the existing call or connection, P1 uses a conference mechanism. Only one person involved in the conference call can talk at a time. Now replace the people in the conference call with database connections where bound connections use bind tokens. Only one connection involved in a bound connection can interact with the server at a time.
The following example of bound connections illustrates how two connections can access the same transaction: A customer decides to purchase a product at a local department store. The salesperson accesses a sales transaction system that inserts a row into the sales transaction table, including a credit card authorization number. Two connections are made to the same server: connection C1 and connection C2. C1 begins a transaction that adds a product sale row to the sales table. A credit card authorization number must be added to the new sales transaction row. During the credit card authorization process, the extended stored procedure creates connection C2 to dial out across a telephone line to the credit card company and modifies the sales transaction row with the credit card authorization number. Only by using bound connections can both connections access the same row without locking conflicts.
Once the two or more connections are bound together by using bind tokens, only one connection at a time can actively use the bound connection. If one connection is executing a statement on the server or has results pending from the server, no other connections that share the same transaction space can access the server until the current connection finishes executing the current statement. If the server is busy, an error occurs indicating that the transaction space is in use and that the connection should retry later.
Note The application developer should add the appropriate recovery code for a server busy error.
There are two types of bound connections: local and distributed. A local bound connection allows two or more connections to share the same transaction space of a single transaction on a single server until the entire transaction is either committed or rolled back. A distributed bound connection allows two servers to participate in the same transaction until the entire transaction is either committed or rolled back by using Microsoft Distributed Transaction Coordinator (MS DTC). For more information about distributed transactions, see Distributed Transactions and the Guide to Microsoft Distributed Transaction Coordinator.
If a bound connection is involved in a local transaction and executes a remote procedure call (RPC) on a remote server with SET REMOTE_PROC_TRANSACTIONS ON, the local bound transaction is automatically promoted to a distributed bound transaction by MS DTC and an MS DTC session is started. When the transaction is promoted, the bind token changes to a distributed token identification number. This new token identification number should be used with any future calls to sp_bindsession. For more information about using RPC calls and the SET REMOTE_PROC_TRANSACTIONS ON statement, see the Guide to Microsoft Distributed Transaction Coordinator.
Bound connections are also useful in developing extended stored procedures that can use ODBC or DB-LIBRARY to call back to the server and share the same transaction lock space as the connection calling the extended stored procedure. This method allows an extended stored procedure more flexibility when performing database functions.
Bound connections can also be used to develop three-tier applications where business logic is represented in separate programs that can work cooperatively on a single business transaction.
For more information about bound connections, see sp_bindsession and sp_getbindtoken.
For more information about using bound connections with Open Data Services, see sp_getbindtoken.