Like probably many of us, every now and then I sit down to search and browse through some of the old postings on dynamicsuser.net or mibuso.com. To learn new things, to get inspiration for a new blog post or just to see what’s going on. That’s what I did this snowy Sunday morning sitting at the kitchen table with children busy preparing an apple pie [pi] and my wife baking pancakes. [Lucky me. [:D]]
Today I was looking at various posts regarding locking issues. A subject of great importance to any multiuser database installation (NAV!). Specifically these three raised my attention:
- Navision SQL Locking
- NAS for posting invoices and payments
- What if i remove Locking From Sales Table
… as, among others, they included the subject posting queue.
Olden days revived when we had introduced a 2-days performance course as part of our Dutch Navision Solution Developer curriculum, having this subject as one of its topics. As the listed forum postings only touched the subject I thought I might spend a blog post (or two) and go into details including some sample coding (see Part 2).
Posting Transactions
As soon as multiple concurrent users post transactions (i.e. journals and documents) to the same tables they most probably will be confronted with the down side of table locking; i.e. one or the other finds himself waiting as – paraphrasing the NAV message shown – the …
… table cannot be changed because it is locked by another user. Wait until the user is finished and then try again.
Like for example multiple users posting sales invoices almost at the same time.
Posting Queue
If this waiting for locks to be released becomes a nuisance, in other words a real performance issue, it might be time to reorganize the way transactions are posted. A possible solution is to introduce a job queue, i.e. the posting queue, to which each user ‘publishes’ the transactions that need to be posted. Once the transaction has been queued the user can continue with her next task.
The basic principle of this posting queue is illustrated by the schema above. Different concurrent users or processes (#1, #2 and #3) insert their transaction into the queue from where one single (scheduled) job posts them to the relevant table(s) one after the other.
As this job is the only one doing the posting no other process will be locking the table(s) and no user will be waiting for a lock.
Note
- there might be various reasons that a posting queue is not the right solution for a company, likewise, as:
- the immediate result of a posting is needed, thus the user cannot wait for the posting queue to finish posting the transaction
- the volume of postings is to big for (one?) posting queue to keep pace with
- way before starting to implement a posting queue you should have determined whether the application under investigation has not been badly configure and needs to be optimized