4 Key Factors to Implement a Queue Based Database Application

by Philipp Stiefel, originally published December 12th, 2017

Photo by Michal Parzuchowski. Used here under CC0 licensing

Recently I read (and answered) several questions regarding the implementation of queue based applications. - That sent me on a trip down memory lane. One of the very first applications I ever wrote for a production environment was a queue based application. So, I thought it might be interesting to write down my take on that.

In this article I describe strategies to implement a solid queue management.

What is a Queue Based Database Application?

A queue based database application is an application (or part of one) where multiple users need to work on a pool of records in a queue to process them. The records in the queue are in an "unprocessed" state. After the user worked on any record, that record is in "completed" state and is removed from the queue.

One user needs to select or is assigned to one of the records and works with that data. Any record must never be assigned to more than one user. 

Practical examples for such type of applications are:

• A helpdesk application. - There is a pool of new support cases. Whenever a technician is available, he is assigned one of those cases and solves it. - Rinse, repeat.
• A fulfillment center application. - There is a pool of orders that need to be processed. Each worker is assigned an order, gets the ordered goods from the warehouse, packs them in a parcel, and puts that somewhere to be collected by a delivery service. - Rinse, repeat.
• A data import application with manual data verification. - Records from external sources are imported into a queue table and users need to look at each record to correct spelling errors or invalid data, check for duplicates, or even clarify ambiguities by calling the person who entered the data on the phone.  Rinse, repeat. - (This was actually the type of application that was the very first queue based application I wrote.)

While all these examples are for (human) user interaction, all the same principles apply if there are automated processes working on the items in the queue instead of human users.

How to Implement a Queue Based Database Application

The strategies describe here use only mechanisms available on all relational database systems. There might be a more sophisticated mechanism available on the one system or a simpler mechanism, like the SELECT … FOR UPDATE-clause on SQL Server, on another. But those would tie you to one specific system. The approach described here should work everywhere with only minor adjustments.

The sample code fragments here are written in Microsoft Access, but the key concepts can be easily implemented on Microsoft SQL Server, Oracle, MySQL, and probably every other RDBMS as well.

1. The Queue and/or Locking Table

You only need minimal additions to your data model to handle the locking required to assign the items from your queue to the user working on them.

Keep the queue logic as simple as possible. There should be only one table relevant for managing the queue. You can either put a LockUser- and LockTime-Field (more on the latter in a minute) right into the facts table (queue table) or you can create an additional table for managing the locks. That table would need those two fields and the primary key of the locked record. - There is not much difference between these two approaches.

Whenever possible, go for one of these simple data structures for your locks. However, if you’ve got more complex structures for your data and absolutely need to lock multiple different entities (records in different tables), you should wrap all write operations into one transaction. That will prevent your application from acquiring only "half a lock".

Here is a simple queue table with a LockUser and LockTime field added.

The most common mistake I see in such kind of applications is in the acquisition of the lock. To make this work, you need to make sure only one user can assign any one particular item to himself. Immediately afterwards this items needs to be locked for other users.

Any number of users can read the same data from a database at the same time. So, when querying the next free record first and then locking the record, it is possible that two users query the same, not yet locked, record. There will be some latency between reading the record and writing the lock, so this might happen more often than you think.

To prevent this, you need to write the lock information first. That forces the database to serialize the operation. No two user will ever be able to lock the same record. Only read the record from the queue after locking it.

This applies regardless whether your queue is a black box invisible to the user or a visible list where the user can pick an item for himself to work on.

Here is an update query to write a lock to my sample queue table from above. If you use a separate lock table you would insert a lock-record into that table instead.

(The GetCurrentUserName-Function should return the user-id or user-name of the current user. I show a very simple solution to get the Windows user name in my Video on How to log the Windows Username.)

If the user selects the record he wants to lock from a visible list, you should adjust the SQL above to use the Id of the user-selected record instead of the minimum Id as criteria.

If you implement your application in Microsoft Access you can execute this via the DAO.Database.Execute-Method and check the RecordsAffected-Property afterwards to find out if you succeeded in acquiring a lock. . Here is VBA sample code for this. (qryLockItem is the above query.)

Now, that you have got the lock, you can query the (first) locked record.

This SQL allows any user to lock as many records from the queue as he likes. If there is only one locked record allowed per user you need to adjust the SQL to reflect the desired behavior. (e.g. add criteria to check if there already is a lock for the current user.)

With the queue logic implemented, you’ve got the most critical part of the queue application in place already.

2. Implement a Clear Action to Complete the Work and Remove the Record from the Queue

Depending of the scope and complexity of the work required on each queue item, it might be sensible to allow the user to save his changes to the record(s) while still working on the item.

Still, your application's user interface needs to communicate clearly to user which action marks his work on the queue item complete and removes the item from the pool.

If the transitioning of a record from “unprocessed” to “processed” state requires write operations to multiple tables, it is once again recommended to wrap those write operations into a single transaction.

3. Anticipate Hardware and/or Software Failure

Bad things do happen. There might be a power outage, a disconnection of the network link between database backend and the user’s application, or the application might simply crash. If something like that happens, the user needs to be able to access the record(s) in the queue he was working on before the unfortunate event happened as soon as normal operations can proceed.

In fact, the application should support the user and actively inform him about his incomplete items in the queue and direct him to the unfinished work. It is annoying for the user to be prevented from accessing his own records in the queue due to the lock on the queue record and the application not recognizing the fact that he is the one owning the lock.

4. Anticipate Abandoned Locks

Not only might the technical components of the process fail. The user might fail as well.

Imagine someone working on an item in the queue and then something comes up, urgently requiring his attention.  He just postpones his work on his current queue item and does something else. Later he goes home without remembering his unfinished work on the queue.

It would be really embarrassing for your clients business, if some important case is left incomplete for days or even weeks because the person working on it forgot to complete it before leaving for their well-earned vacation.

All locks on the queue should expire after a certain amount of time. Then the application should automatically release the lock of any user and return the locked item to the pool of open items.

This is where the LockTime field mentioned previously comes into play. If you write the time the lock was acquired to this field, your application is able to detect and resolve such kind of issues. It is easy to calculate and display the time since the lock was acquired. Furthermore the application itself or a separate companion process can regularly look for stale locks and release them automatically.

We can modify the original statement for acquiring the lock for a user to take this into account. Here is an extended SQL Statement that does not only look for open items in the queue but also for locked items whose lock should expire.

The custom MaxLockAgeMinutes-Function will just return the desired maximum age from a configuration table or a hard-coded constant in the application.

Alternatively you can put an automated process in place (e.g. via Windows Task Scheduler) that executes a simple SQL Statement to release expired locks every couple of minutes. The SQL for that would look like this:

If a user returns to a records he locked earlier whose lock expired and was removed in the meantime, he should be made aware of this fact. That is a bit tricky and I have no suggestion of a universal solution to this.

An idea to deal with this is, the form/application displaying the record could itself track the timeout of the lock and inform the user. Or it could query the queue to detect lost locks. - Both options require a timer triggering this check every couple of minutes. - I'm not particularly fond of polling timers, but I can't think of another simple alternative here.

Wrap up

If you don't consider the possible issues, you can easily create a queue application that works perfectly under ideal conditions during development and testing. However, in the production environment this application may exhibit frustrating, hard-to-debug errors.

Pay attention to the key factors I described above. If you take good care to get these key steps right, it is not difficult to implement queue logic correctly.

There might be better solutions available to the key issues, than those I outlined above. But there is no way around thinking about these issues and finding suitable solutions for your particular scenario.

Share this article: