Ancient wisdom goes that you cannot have more than one write transaction going on at the same time in a single session in NAV.
This is absolutely true, in and out.
Some features, like Activity Log, will leave you wanting to be able to write to the database outside of the normally running transaction.
Wouldn’t it just be beautiful if you could:
- Write to the database, and then persist the change to the database even if an error happens during the transaction? (without cough! TryFunction, cough! cough!);
- Not cause any locks to remain at the target table for any longer than it takes to do the write itself, without having to call COMMIT on your “regular” transaction; and
- Not use temporary tables, because a system error (however unlikely) could cause the data to not be persisted if crash happens before temporary table is flushed to the physical one.
Well, in fact, you can do this.
Conceptually, you do this: encapsulate your write operation into a codeunit, then run this codeunit in a background session through STARTSESSION function. Whatever is done in a background session is done in a separate transaction, and if this session only does this write, then the lock will be immediately released.
To show this in practice, I’ll use the Activity Log table. It’s a perfect example, because this table can get pretty busy, and could cause locks if being written to from regular transaction, but won’t cause any locking issues whatsoever to those transactions that do nothing else but write to this table.
Writes to Activity Log are done by this function inside the Activity Log table itself:
The first thing I’ll do with this function is cut it from this table. Then, I’ll create a new codeunit, paste it into it, and then make these changes:
And that’s it. To test it, I wrote this:
On my slow laptop running in battery mode (meaning with processor clock rate reduced), I got this:
31 milliseconds per log entry may seam a lot, but it’s nothing when you think that all this is happening on a separate transaction that does not keep any locks for much longer. Actually, actual locking time is much shorter, because majority of these 31 ms per write actually goes onto session starting and ending overhead.
Cool, isn’t it? What do you think?
Didn’t have time to read this after you wrote it in the same room as I was sitting in, but… How do you do it ???? insane! ????
Nice idea. That is a really useful pattern (with your improvements 🙂 ).
We used a different method in the old NAV versions. We called a function in a SingleInstance codeunit, which stored the log entries in a temp table. At the same time it enabled the Timer component, which triggered the OnTimer event right after the user was given control after the COMMIT. We then used that trigger to move the temp entries to the actual table and disable the timer component again.
Worked like a charm in the classic client and didn’t cause any locks.
The approach you mention is an alternative, and Ciprian mentioned it today as well, but it still leaves theoretical possibility of data loss.
I am going to play a devil’s advocate here, but what if the session started to log the activity times out (say, because there’s a congestion on log table writes)? That could also potentially lead to having an unlogged error.
True. There may be ways to mitigate this, I am just totally immersed in something else right now to be able to think creatively about this 🙂
Everything changes quickly today and possibly this is not an actual post right now but there is another way. You can use combination of LinkedObject=Yes and LinkedInTransaction=No properties on the table to store log data. I have been using this method for several years and seems to be a very simple and efficient way to solve this problem.