Bound Connections

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 resource—only other processes are denied access. But any other process (or connection to SQL Server) can actually execute as the same application and user. It is common for applications to have more than one connection to SQL Server. 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.

However, two or more different connections can share a lock space and hence not lock each other out. This capability is known as a bound connection. 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 that 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.)

Bound connections are especially useful if you're writing an extended stored procedure, which is a function written in your own DLL, and that extended stored procedure needs to call back into the database to do some work. Without a bound connection, the extended stored procedure collides with its own calling process's locks. When multiple processes share a lock space and a transaction space by using bound connections, a COMMIT or ROLLBACK affects all the participating connections. If you are going to use bound connections for the purpose of passing the bind token to an extended stored procedure to call back into the server, you must use a second parameter of the constant 1. If no parameter of 1 is passed, the token cannot be used in an extended stored procedure.

Here's an example of using bound connections between two different windows in SQL Query Analyzer. In SQL Server 2000, you must be inside of a transaction in order to get a bind token. Since we don't have a controlling application to declare and store the bind token in a client-side variable, 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:

 -----------dPe---5---.?j0U<_WP?1HMK-3/D8;@1 

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 Query Analyzer, 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 SQL Query Analyzer window:

 EXEC sp_bindsession 'dPe---5---.?j0U<_WP?1HMK-3/D8;@1' 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 in order to call sp_getbindtoken. You can use something like this:

 USE pubs UPDATE titles SET price = $100 GO 

This should exclusively lock every row in the titles table. Now go to the second query window and select from the locked table:

 SELECT title_id, price FROM titles GO 

You should be able to see all the $100 prices in the titles table, just as if you were part of the same connection as the first query. 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 one began the transaction. If the first connection tries to then issue a ROLLBACK TRAN, it gets this message:

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

SQL Server keeps track of bound connections in an internal structure called the XCB (transaction control block), which is used to relate multiple connections in a bound session. You can actually see which connections are bound using SQL Query Analyzer and an undocumented DBCC command. First, you need the spid (Server Process ID) of any connections you're interested in. You can get this value using the function @@spid:

 SELECT @@spid 

The DBCC command is called DBCC PSS. Most of the information returned is meaningful and relevant only to Microsoft Product Support Services (PSS) engineers. It is really just a coincidence that Microsoft's support service is called PSS; in this case, PSS stands for Process Status Structure. As you saw earlier with DBCC PAGE, for most of the undocumented stored procedures you must first enable them using trace flag 3604. So suppose we've determined that the Process ID value of the first connection above, which is updating the prices in the titles table, is 51, and the connection we've bound to it is 54. The following command will print the PSS from both connections:

 DBCC TRACEON(3604) GO DBCC PSS(1,51) DBCC PSS(1,54) 

Here's some partial output:

 PSS: ---- PSS @0x193731D8 --------------- pspid = 51 m_dwLoginFlags = 0x03e0 plsid = 422666344 pbackground = 0 pbSid ----- 01 . sSecFlags = 0x12 pdeadlockpri = 0 poffsets = 0x0 pss_stats = 0x0 ptickcount = 16806736 pcputickcount = 3363174526155 ploginstamp = 17 ptimestamp = 2000-08-13 12:31:47.793 prowcount = 21 plangid = 0 pdateformat = 1 pdatefirst = 7 Language = us_english RemServer = UserName = sa poptions = 0x28480860 poptions2 = 0x3f438 pline = 1 pcurstepno = 0 prowcount = 18 pstatlist = 0 pcurcmd = 253 pseqstat = 0 ptextsize = 64512 pretstat = 0 pslastbatchstarttime = 2000-08-13 13:13:03.423 pmemusage = 19 hLicense = 0 tpFlags = 0x1 isolation_level = 2 fips_flag = 0x0 sSaveSecFlags = 0x0 psavedb = 0 pfetchstat = 0 pcrsrows = 0 pslastbatchtime = 2000-08-13 13:13:03.473 pubexecdb = 0 fInReplicatedProcExec = 0 pMsqlXact = 0x19373f88 presSemCount = [0]9951900 presSemCount = [0]9951900 pcputot = 180 pcputotstart = 170 pcpucmdstart = 170 pbufread = 32 pbufreadstart = 31 plogbufread = 533 plogbufreadstart = 428 pbufwrite = 5 pbufwritestart = 4 pLockTimeout = 4294967295 pUtilResultSet = 0x0  PSS: ---- PSS @0x195B51D8 --------------- pspid = 54 m_dwLoginFlags = 0x03e0 plsid = 422666344 pbackground = 0 pbSid ----- 01 . sSecFlags = 0x12 pdeadlockpri = 0 poffsets = 0x0 pss_stats = 0x0 ptickcount = 18289118 pcputickcount = 3658807285722 ploginstamp = 24 ptimestamp = 2000-08-13 12:56:30.107 prowcount = 21 plangid = 0 pdateformat = 1 pdatefirst = 7 Language = us_english RemServer = UserName = sa poptions = 0x28480860 poptions2 = 0x3f438 pline = 1 pcurstepno = 0 prowcount = 18 pstatlist = 0 pcurcmd = 253 pseqstat = 0 ptextsize = 64512 pretstat = 0 pslastbatchstarttime = 2000-08-13 13:13:56.380 pmemusage = 6 hLicense = 0 tpFlags = 0x1 isolation_level = 2 fips_flag = 0x0 sSaveSecFlags = 0x0 psavedb = 0 pfetchstat = 0 pcrsrows = 0 pslastbatchtime = 2000-08-13 13:13:56.420 pubexecdb = 0 fInReplicatedProcExec = 0 pMsqlXact = 0x195b5f88 presSemCount = [0]9951900 presSemCount = [0]9951900 pcputot = 40 pcputotstart = 30 pcpucmdstart = 30 pbufread = 3 pbufreadstart = 2 plogbufread = 56 plogbufreadstart = 33 pbufwrite = 0 pbufwritestart = 0 pLockTimeout = 4294967295 pUtilResultSet = 0x0 

I won't even try to describe each of the values returned, mainly because I don't know them all. Some of them are pretty self-explanatory, such as pslastbatchstarttime, Language, and UserName. You might be able to guess the meaning of some of them. The value for the isolation level can be 0 through 4, with the numbers 1 through 4 mapping to the four transaction isolation levels. The value of 2 in my output means Read Committed. You will sometimes get a 0 for this value, which means the default isolation, which is Read Committed for SQL Server.

I have shown the Process ID value and a value called plsid in boldface. The plsid value is the transaction space identifier, although it doesn't look at all like the value returned in SQL Query Analyzer for the bind token. You can see that the plsid value is the same for these two connections. If you run DBCC PSS and look at other connections besides these two that have been bound together, you should notice that they all have different plsid values.



Inside Microsoft SQL Server 2000
Inside Microsoft SQL Server 2000
ISBN: 0735609985
EAN: 2147483647
Year: 2005
Pages: 179
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net