Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)

Remember that the issue of lock contention applies only between different SQL Server processes. A process holding locks on a resource does not lock itself from the resourceonly other processes are denied access. However, the same process doesn't always mean the same user or application. It is common for applications to have more than one connection to SQL Server. Although the user and the application are the same, every such connection is treated as an entirely different SQL Server process, and by default no sharing of the "lock space" occurs between connections, even if they belong to the same user and the same application. One connection from the application could select data from a table, and as it retrieves rows, it might decide that one of the rows needs to be updated. If another connection tries to update a row that the first connection still has a shared lock on, the application might block itself. By default, SQL Server does not recognize the two connections from the same application as related in any way. SQL Server 2005 gives us two solutions to this problem, which we'll examine next.

Using Bound Connections

Prior to SQL Server 2005, the solution to avoiding conflicts between multiple connections within an application was to use a feature called bound connections, which allows two or more connections to share a lock space and hence not lock each other out. With a bound connection, the first connection asks SQL Server to give out its bind token. The bind token is passed by the application (using a client-side global variable, shared memory, or another method) for use in subsequent connections. The bind token acts as a "magic cookie" so other connections can share the lock space of the original connection. Locks held by bound connections do not lock each other. (The sp_getbindtoken and sp_bindsession system stored procedures get and use the bind token.)

Here's an example of using bound connections between two query windows in SQL Server Management Studio. You must be inside of a transaction in order to get a bind token. We don't have a controlling application to declare and store the bind token in a client-side variable, so we have to actually copy it from the first session and paste it into the second. So, in your first query window, you execute this batch:

DECLARE @token varchar(255) BEGIN TRAN EXEC sp_getbindtoken @token OUTPUT SELECT @token GO

This should return something like the following:

QI9FL\B`QQOW-.GkN3N[6M5---._D]

Normally, you wouldn't have to look at this messy string; your application would just store it and pass it on without your ever having to see it. But for a quick example using SQL Server Management Studio queries, it's necessary to actually see the value. You use your keyboard or mouse to select the token string that you received and use it in the following batch in a second query window:

EXEC sp_bindsession 'QI9FL\B`QQOW-.GkN3N[6M5---._D]' GO

Now go back to the first query window and execute a command that locks some data. Remember that we have already begun a transaction to call sp_getbindtoken. You can use something like this:

USE AdventureWorks UPDATE Production.Product SET ListPrice = 180 WHERE Name = 'Chain';

This should exclusively lock a row in the Products table. Now go to the second query window and select the locked row:

SELECT * FROM Production.Product WHERE Name = 'Chain'; GO

You should be able to see the $180 price just as if you were part of the same connection as the first query. (A third connection that has not been bound to the first one would block when trying to execute the preceding SELECT statement.) Besides sharing lock space, the bound connection also shares transaction space. You can execute a ROLLBACK TRAN in the second window even though the first window began the transaction. If the first connection tries to then issue a ROLLBACK TRAN, it gets this message:

The transaction active in this session has been committed or aborted by another session. Msg 3903, Level 16, State 1, Line 1 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

You can actually see which connections are bound together in the DMV sys.dm_tran_session_transactions. Sessions that are bound together have the same transaction_id value, and all sessions that used the sp_bindsession procedure have a is_bound value of 1. Here are the results I get after binding the preceding two connections:

SELECT session_id, transaction_id, is_bound FROM sys.dm_tran_session_transactions; GO RESULTS: session_id transaction_id is_bound ----------- -------------------- -------- 52 13313 0 53 13313 1

There is no technical limit to the number of sessions that can be bound together, so you can have many sessions with the same transaction_id value. In practice, you should rarely need to have more than two or three sessions bound together. If a session executes sp_bindsession to bind to a different connection, its new binding will override the former binding.

Multiple Active Result Sets

If your goal in using bound connections is to make sure that two connections from the same application can interleave access to the same data, SQL Server 2005 provides an alternative. In fact, SQL Server Books Online states that sp_getbindtoken and sp_bindsession will go away in a future version of SQL Server. The recommended alternative is to use a feature called Multiple Active Result Sets, or MARS. This feature allows a single connection from an application to send a statement to SQL Server for execution while a previous statement has still not completely sent its results back to the client. As in the preceding example, you can execute a SELECT that returns a large number of rows, and at some point, the application determines that one of the rows needs updating. Without MARS, the update statement would have to be sent over a separate connection. But with MARS enabled, the application can send the UPDATE command even though the SELECT has not completed. Not all T-SQL statements can be interleaved this way, even with MARS enabled. Only when SELECT, FETCH, and RECEIVE have results pending can another request be sent over the same connection. Any other statements must run to completion before execution can be switched to other requests using MARS.

MARS is not enabled by default; you must be specifically enable it in the connection string through the application. It is primarily a client issue, so a complete discussion of using MARS is beyond the scope of this book. I will return to it briefly, g however, when discussing SQL Server 2005 features that use the new row versioning technology.