It is very interesting to know how a single transaction can be shared between two or more SQL Server connections? Now you must be wondering when is this needed and how is this useful? Say, you have a script where you are inserting a million rows into a table and then updating around 100 records of another table in a single transaction. So, the update statement will wait for the insert statement to complete the operation since they are in a single batch.
Fortunately a transaction can be multiplexed , having two or more different statements executed at the same time by sharing the single transaction until a ROLLBACK TRANSACTION or COMMIT TRANSACTION is issued. Here, I am going to discuss about the same using two system extended stored procedures ( sp_getbindtoken and sp_bindsession ). Microsoft has announced that these two procedures will be obsoleted in future versions of SQL Server but it’s worth exploring for knowledge update purpose only and I see these two extended procs are still available in SQL Server 2014 however I would not recommend to use these procedures anywhere in your application. Multiple Active Results Sets (MARS) is a new feature introduced in SQL Server 2005 version to do the same job, sharing a transaction between two or more connections. I will discuss about MARS in my future blog post.
Open a transaction → Get the transaction token using sp_getbindtoken
BEGIN TRANSACTION UpdatePrice DECLARE @out varchar(255) exec sp_getbindtoken @out = @out OUTPUT SELECT @out --Copy the token UPDATE eBooks SET price = 0.00 WHERE price IS NULL
Open a new session or connection → Bind the transaction using sp_bindsession
SELECT @@trancount EXEC sp_bindsession '`=R1.W<D3A7[BPVC=7HT1M5---0E@M--'; --@out value SELECT @@trancount
Let’s have a look at sys.dm_tran_session_transactions DMV, we could see that the session id 53 and 56 shares the same transaction 112833. Which means any statement executed inside session “56”, will be considered as a part the “UpdatePrice” transaction, initiated in “53”. We can also COMMIT or ROLLBACK the same transaction anywhere across the sessions of shared transaction.