Most applications have a deadly Achille’s Heel: Unless you write a lot of support code to prevent it, there’s no guarantee that your users don’t accidently destroy one another’s updates. Rarely ever is this ever discovered in beta testing. It’s not until the system goes live with large user-base does the problem rears its ugly head. The solution is not an easy one.

A major stumbling-block facing many applications developers is a complete lack of support for the proper locking of database tables and records (rows) beyond the normal record-locking that comes with database engines like SQL Server. This is not a fault of the database engines themselves. It is really the responsibility of the application code itself.

By default, database engines will automatically lock tables and rows (records) for the split-second intervals between the time applications make their connection, do their retrievals and updates, and the disconnect. It is important to note that the database engines do not provide anything to prevent problems that surface when an application is simultaneous used by multiple end-users — especially if they all start updating the same records.

An Example

Consider an Human Resources system. Employee X has just received a promotion. She gets a new office and a raise.

User A accesses employee X’s information with the intention of updating the employee’s telephone number. Several seconds later, user B accesses the same employee’s information with the intention of updating the employee’s salary. Both users A and B see the same information and begin typing.

Here’s where the problem begins:

  • If user A submits her update before user B, the server will update the employee’s telephone number. When user B submits their update, the employee’s telephone number will be updated again — back to it original value. User A’s update will be destroyed!
  • If User B submits his update before User A, User A will force the employee’s salary back to it its original value. User B’s update will be destroyed!

This is actually a classic problem that was solved by mainframe software developers but seemed to have been forgotten when we transitioned to the web.

The problem is that it’s a lot of tricky coding that must be performed on the applications and not the database engines. It’s a lot of complex coding rarely attempted by a lot of application developers using traditional 3GL languages.

The Solution

The 4GL Developer must first decide the scope of the lock. In 95% of the cases, it’s a single record that must be locked. In these cases the resource lock consists of the table name and the primary key. In a few cases, a subset of rows must be locked. Here, the lock would be the table name and some other field (usually a “foreign” key). Alternately, the developer might need to lock an entire table in which case the lock consists of the table name alone.

By default, locks are automatically set for 5 minutes (or whatever value is deemed appropriate).

When a user submits a request to the server (e.g. clicks a button), the 4GL system will attempt to create the lock for that particular user and, if successful, responds with the next screen (most likely an update). The lock is now in effect which means, should any other user attempt to access the same resource, will be told that another user already “owns” the resource must wait. So long as the first user, the “owner”, responds back within the allotted time, the lock is released and other users may now request the resource.

By this means, resources are given to the users in a way that prevents them from interfering with one another.