Optimize Update-Concurrency Tests
Compare alternative approaches to SQL Server 2000 concurrency management.
by Roger Jennings
October 2003 Issue
Technology Toolbox: VB.NET, SQL Server 2000, XML, ADO.NET
Minimizing active database connections is a primary objective of scalable data-access architectures. Maintaining a live connection to the source database while users browse, update, or insert the data behind complex business documents isn't practical for more than a few simultaneous users. ADO's disconnected Recordset objects let you maintain a local copy (snapshot) of the data of interest, disconnect, update the snapshot values, reconnect, and attempt to update the affected tables. ADO.NET's DataSet objects extend the original disconnected-recordset approach to create an in-memory representation of data tables with client-side relationship definitions, primary- and foreign-key constraints, and other data-consistency-checking features. Both object types permit offline editing by persisting the original and edited data versions to a local XML file and attempting an update after reconnecting to the network. The local-persistence scenario also applies to long-running transactions in automated business-to-business (B2B) processes, such as supply-chain management (see Additional Resources).
The probability that another client or component has altered the underlying data increases as the time between the initial snapshot data access and updates made to the records grows. Updates based on noncurrent data snapshots often generate concurrency conflicts. Adopting the "last in wins" approach and overwriting prior changes that another person or process made is dangerous at best and might have fatal consequences. For example, two physicians altering a medical record simultaneously could prescribe different drugs or dosage based on conflicting patient-status data. Your data front ends or components must perform concurrency tests before an update to verify that no others have made changes to the underlying data prior to your updates. Bob Beauchemin's article in the VSM August 2003 issue covers the basics of single-table DataSet concurrency tests (see Additional Resources). I'll compare the performance of alternative approaches to SQL Server 2000 optimistic concurrency testing of transacted multitable updates by stored procedures. I'll also describe an often-overlooked concurrency test element for multirow updates.
The two most common concurrency-checking techniques are row-level timestamp values and column current-value comparisons. SQL Server timestamp values are an eight-byte binary representation of a row identifier that's guaranteed to be unique within a table; the timestamp value increments when any column value changes. Your initial SELECT query returns the timestamp value with the snapshot data. You add an equality test for the original timestamp value as a WHERE clause criterion to the stored procedure for the UPDATE operation. If the timestamp values don't match, your stored procedure sends a return-value parameter code to indicate that the update didn't occur.
Back to top
|