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

  1. start the transaction
  2. update the request details for export processing
  3. update the request status to ‘Running’
  4. call the export component with the RequestID
  5. commit the transaction

For each request, the export process must

  1. read details for the request
  2. export data for the request
  3. update the request status to ‘Ready’ or ‘Canceled’

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:

  1. start the transaction
  2. get the bind token by calling sp_getbindtoken
  3. update the request details for export processing
  4. update the request status to 'Running'
  5. call the export process with the RequestID and
  6. a bind token

  7. commit the transaction

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

  1. bind to the script's transaction by calling sp_bindsession
  2. read details for the request
  3. export data for the request
  4. update the request status to ‘Ready or ‘Canceled’
  5. unbind from the session by calling sp_bindsession with a NULL token

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.