Share a transaction using sp_getbindtoken sp_bindsession

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.

Step 01:
Open a transaction Get the transaction token using sp_getbindtoken

sp_getbindtoken

Step 02:
Open a new session or connection Bind the transaction using sp_bindsession

sp_bindsessionWe have observed that the transaction name “UpdatePrice” was successfully bound to session id “56” from session id “53”.

single-transaction-shareLet’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.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: