Manage Learn to apply best practices and optimize your operations.

Dealing with concurrent access

Methods for avoiding performance problems when more than one user is accessing the same data at the same time in a surrogate key architecture.

Any multi-user database application has to have some method for dealing with concurrent access to data -- when...

more than one user is accessing the same data at the same time. A problem occurs when user X reads a row for editing, user Y reads the same row for editing, user Y saves changes, then user X saves changes. The changes made by user Y are lost unless something prevents user X from blindly overwriting the row. This problem may not be handled by the database alone. The solution usually involves the application as well.

One way to deal with concurrent access is to use a counter column incremented by the application with each modification to the row. In the example above, user X would be alerted by the application that the row had been modified between the time of reading and the time of attempting to save changes. This situation is noticed by the database and/or by the application because the counter had changed. The application should allow user X to start over with the current row contents or overwrite the changes made by user Y. No changes would be unknowingly lost.

As mentioned, the database and application must work together to take advantage of this column. The application reads a row for editing. It increments the counter column by one when saving changes. The database (using a stored procedure) checks the stored counter before updating. If the stored counter is not less than the incremented counter, then a concurrency violation has taken place. In other words, another user changed the row between reading and attempting to save changes. The database denies the requested update and informs the application that the update was not performed. The application offers the user options to proceed. If the user chooses to overwrite the row, the application simply increments the counter by one and tries to save again. As an alternative to incrementing the counter by one, the application could increment the counter by a very large number (such as a million) to indicate that a concurrency violation has taken place and to force the save to succeed. In this case, the counter would be used to track concurrency violations as well as updates.

The RecordMask column is used to record a status indicator for the row. In some situations, rows in selected tables may have several different states of existence. This column could be used to mark rows as active, inactive, pending, disabled or deleted -- or any other state business needs may dictate. If rows would be marked as either active or deleted only, it may be better to consider row auditing functionality (watch for my future article about a robust audit trail system).

The proposed database architecture suggests using triggers to populate the CreateDate column and the ModifyDate column. A default value would work for the CreateDate column, but it would allow row insertions using supplied dates, potentially reducing the validity of the dates. A trigger would cause supplied dates to be replaced with system-generated dates. Triggers are also very flexible because they can be written to fire under certain conditions. For example, you may want triggers to fire only when a single row is being handled or when the application doing the handling is not the primary application. This arrangement would maximize performance for the primary application by allowing it to supply dates for bulk row operations and avoid the additional work of updating the dates within the trigger.

Columns and objects to support a surrogate key architecture

 Home: Introduction
 Part 1: Overview of proposed surrogate key architecture
 Part 2: Dealing with concurrent access
 Part 3: Standard routines for each table
 Part 4: Stored procedures: Create and manage standard objects

Brian Walker
Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
Copyright 2006 TechTarget

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.