Using Bound Connections
Bound connections allow two or more connections to share the same transaction and locks. Bound connections can work on the same data without lock conflicts. Bound connections can be created from multiple connections within the same application, or from multiple applications with separate connections. Bound connections make coordinating actions across multiple connections easier.
To participate in a bound connection, a connection calls sp_getbindtoken or srv_getbindtoken (Open Data Services) to get a bind token. A bind token is a character string that uniquely identifies each bound transaction. The bind token is then sent to the other connections participating in the bound connection. The other connections bind to the transaction by calling sp_bindsession, using the bind token received from the first connection.
Bind tokens must be transmitted from the application code that makes the first connection to the application code making any of the subsequent bound connections. There is no Transact-SQL statement or API function that an application can use to get the bind token for a transaction started by another process. Some methods that can be used to transmit a bind token are:
- If the connections are all made from the same application process, bind tokens can be stored in global memory or passed into functions as a parameter.
- If the connections are made from separate application processes, bind tokens can be transmitted using interprocess communication (IPC), such as a remote procedure call (RPC) or dynamic data exchange (DDE).
- Bind tokens can be stored in a table in Microsoft® SQL Server™ that can be read by processes wanting to bind to the first connection.
Only one connection in a set of bound connections can be active at any time. 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 can access the server until the current connection finishes processing or cancels the current statement. If the server is busy, an error occurs indicating the transaction space is in use and the connection should retry later.
Types of Bound Connections
The two types of bound connections are local and distributed.
- Local bound connection
Allows bound connections to share the transaction space of a single transaction on a single server.
- Distributed bound connection
Allows bound connections to share the same transaction across two or more servers until the entire transaction is either committed or rolled back by using Microsoft Distributed Transaction Coordinator (MS DTC).
Distributed bound connections are not identified by a character string bind token; they are identified by distributed transaction identification numbers. If a bound connection is involved in a local transaction and executes an 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 to Use Bound Connections
Bound connections are useful in developing extended stored procedures that must execute Transact-SQL statements on behalf of the process that calls them. Having the calling process pass in a bind token as one parameter of the extended stored procedure allows the procedure to join the transaction space of the calling process, thereby integrating the extended stored procedure with the calling process.
Bound connections can be used to develop three-tier applications in which business logic is represented in separate programs that work cooperatively on a single business transaction.
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.