Sharing a Transaction

Congratulations to Marek Skotnica, PC programmer analyst at Nova Hut Ostrava in the Czech Republic, and Nikola Milic, manager of the statistical department at Cyprus-based Tradal. Marek won first prize of $100 for the best solution to the October Reader Challenge, "Sharing a Transaction." Nikola won second prize of $50. Here’s a recap of the problem and the solution to the October Reader Challenge.

Problem

Neil is the application architect for a company’s SQL Server 2000 and 7.0 installations. One of Neil’s tasks is to devise an application that consists of two components: a script that updates data, then invokes an export process, and the export process, which exports the data and tracks the update requirements in a table. This combined operation needs to be atomic and execute as one transaction. However, because the export process and the script use different server connections, they can’t work concurrently on the same data. Use the following information to help Neil outline the application steps necessary for efficiently updating the database and exporting each individual request while avoiding blocking. The method you devise should require minimal changes to the application steps below.

The structure of the database table that tracks requests is

CREATE TABLE Requests (RequestID int IDENTITY PRIMARY KEY CLUSTERED,Name varchar( 30 ) NOT NULL,Details text,Status varchar( 10 ) CHECK( Status IN ( 'Pending', 'Running', 'Ready', 'Canceled', ‘Completed’ ) ))

For each request, the script must

For each request, the export process must

Solution

Neil can create bound connections to let the script and export process share the same transaction and locks. Globally, bound connections let different connections from the same application or different applications share the same transaction and locks. When bound connections are in place, the two different connections can work on the same request without locking conflicts. To create a bound connection, the application requires a bind token that uniquely identifies the bound transaction. The application can obtain the bind token by calling the stored procedure sp_getbindtoken. Any other connection can subsequently use this token to bind to the transaction by calling stored procedure sp_bindsession.

Neil can modify the steps that the script performs for each request as follows:

Then, Neil can modify the steps that the export process performs as follows:

By using bound connections, Neil enables the script and the export process to work with the same data without causing locking conflicts. This solution also requires only minor changes to Neil’s application and provides an efficient mechanism for sharing a transaction.