Investigating Database Locks

Whenever a user initiates an action that asserts an exclusive lock on a table, or even a single row in a table, it will cause other users trying to change or save data to the locked table or row to be “blocked” — forced to wait until the first user concludes the action. While blocked, users will experience an ongoing “hourglass” or spinning circle icon. Often that condition will be misinterpreted as a program crash. To determine if there is a locking/blocking situation, the administrator can run a simple query in DBISQL while the blocking is still on-going to show the condition of the user connections.

SOS has worked hard to prevent locks that last more than a split-second, but even with mature software like SOS, it is possible that something slipped through. If you are able to identify a persistent locking situation, be sure to report it as soon as you can.

After opening DBISQL and logging in with a SUPER level account, execute the following query:

SELECT number, userid, blockedon, lockrowid, locktable FROM sa_conn_info()

The results will look something like the figure below:

We have highlighted the row that shows the user connection that is being blocked. The “blockedon” column value is the number of the connection that is responsible for the block. Matching that number to the row with the same value in the “number” column tells us that it is the user SUPER who is creating the lock that is blocking the highlighted user. Other columns in the highlighted row show us the id of the specific row that is locked, as well as the name of the database table in which that row is found. Until this lock is released, the blocked user “OM_MF” will not be able to proceed.

In practice, the user setting the lock will generally be in the form <module-prefix>_<UserID>. For example, “SC_MF” would indicate user MF connecting from module Scheduler (SC). Other prefixes are OM for Office Manager, CM for Case Manager, and AD for the Admin module. In our example above, we used DBISQL to create the lock, which is why there is no prefix.

Once you know the UserID of the responsible user, ask the user to complete the operation, return to the indicated module’s main menu, or exit that module altogether. Doing so will immediately remove the lock, and any blocked users will return to normal operation.

If you should repeatedly run into a scenario the you can identify and reproduce, please report your findings to SOS tech support as soon as possible, so that our developers can diagnose the problem and roll out a fix.