Concurrency Control

Concurrency is an issue in database systems where two users update the same record in the database, one after the other.  If both users are viewing the same record, and then make independent changes, how the application handles concurrency determines which user’s data is updated in the record.  In some applications, the second user to update a record will overwrite the changes made by the first.  In others, the application will warn the second user that the record they are viewing has just been updated.  In still others, the second user’s view is automatically updated when the first user updates the record.

Implementing concurrency handling is typically very tedious and must be applied carefully to nearly every transaction in an application.  There are two commonly used concurrency detection and handling mechanisms: optimistic concurrency control and pessimistic concurrency control.  Iron Speed Designer implements the optimistic concurrency model.

For more detail, see:

What is Pessimistic Concurrency?

What is Optimistic Concurrency?

Concurrency Handling in Applications

What is Pessimistic Concurrency?

Pessimistic, or negative, concurrency control is when a record is locked at the time the user begins his or her edit process.  In this concurrency mode, the record remains locked for the duration of the edit.  The primary advantage is that no other user is able to get a lock on the record for updating, effectively informing any requesting user that they cannot update the record because it is in use.

There are several drawbacks to pessimistic concurrency control.  If the user goes for a coffee break, the record remains locked, denying anyone else the ability to update the record, even if it has been untouched by the initial requestor.  Also, in order to maintain record locks, a persistent connection to the database server is required.  Since web applications can have hundreds or thousands of simultaneous users, a persistent connection to the database cannot be maintained without having tremendous resources on the database server.  Moreover, some database tools are licensed based on the number of concurrent connections.  As such, applications that use pessimistic concurrency would require additional licenses for use.  Because of this drawback, almost all applications use optimistic concurrency control.

What is Optimistic Concurrency?

With optimistic, or positive, concurrency, the record is not locked during the edit phase.  Instead, the record is locked for a split second just before the new changes are committed to the database and immediately unlocked after the changes have been made.  Optimistic concurrency is generally used in environments with a low contention for data, such as web applications, where there are minimal chances of two users needing to update a record in close proximity to each other.

Of course, with optimistic concurrency handling, it becomes incumbent upon every requestor to check if the record has been updated before they commit any changes.  There are several techniques for testing for an optimistic concurrency violation.  One involves including a timestamp column in the table.  In a test for optimistic concurrency violations, the Update Date and Time are compared with the original value returned when the edit began.  If they are the same, the new changes are committed to the database.  If the Update Date and Time have changed, because another user may have updated the record, the second user to have completed the editing receives an error.

Concurrency Handling in Applications

Iron Speed Designer implements optimistic concurrency handling in applications using stored procedures.  Applications created with in-line SQL are not created with concurrency handling.

Specifically, Iron Speed Designer calculates a checksum for each record.  If the checksum is different when the application prepares to update a record, then the application assumes record was changed by another user.  A warning message is displayed on the application page alerting the second user.

Caveats

Several scenarios can thwart the concurrency handling mechanism:

(1) One or more fields are computed in the database.  If your table has a field that is "computed in the database" by a database trigger or stored procedure, then the record will be changed causing the concurrency handling signature to be different.

(2) Code customization changes the record outside of the data access layer.  A code customization that updates the database outside of your application’s data access layer will cause the concurrency handling signature to be different.

(3) Database triggers.  Database triggers that make changes to the underlying table will cause the concurrency signature to be different.