Is there a way to Isolation Mode on global interactions?

Question

Question

#Database Transaction Processing #Globals #Caché #Ensemble

Hi all,

I have a situation where I am writing to global from different processes - let's call it an Event Queue.
There is then a different process that uses $order to read through the data - the PubSub processing BP.

Sometimes the writing of this global will form part of a transaction that also insert data into other tables, using tstart and tcommit.
If there is some issue, it will roll back, and the entry written to the global will also roll back.

The problem is that the process that reads from this global, picks it up, regardless of the IsolationMode I set for the process, as IsolationMode is part of SQL.
I have considered locking, but logically the lock needs to release before the commit is done, which means this can still happen.

Is there a way or functionality I am not aware of, that can assist in not reading uncommitted global entries?

Thanks.

Discussion (6)1

Log in or sign up to continue

score 1 · Answer 1 · 2024-04-05T03:15:36-04:00

LOCK would be indeed the best equivalent to Isolation Modes
just IRISTEMP doesn't react to COMMIT or ROLLBACK (there is no related Journal)
so instead of writing to your ^EVENT Global you may write to a PPG ^||EVENT acting as your private temp
after Commit or Rollback you decide if you MERGE ^||EVENT to public ^EVENT or not.
from your description, I assume you do it anyhow

score 1 · Answer 2 · 2024-04-05T03:21:19-04:00

depending on the actual long time use you may of course also
use ^mtemp.EVENT (in IRISTEMP)
instead of a journaled ^EVENT in your actual namespace to escape transactions

score 0 · Answer 3 · 2024-04-05T04:24:41-04:00

I do want it in the Transaction. If the commit of the records related to this event being published fails, this event should not be rolled back too.

Problem is that it may have been picked up by the other process already.

I like the idea of using the PPG and merge after the commit. This does however add more complexity to the code in "remembering" to do the merge after the commit.

score 0 · Answer 4 · 2024-04-05T04:44:45-04:00

agreed.
But Switching on/off Journal by process every time you write ^EVENT seems rather uncomfortable.

though it's compact as you know exactly when you need it,

score 0 · Answer 5 · 2024-04-05T06:39:01-04:00

I have also been playing around now with SQL inserts and updates to see how the DB reacts to uncommitted data.

If I insert a record after a tstart and it has not been committed yet, then in another session I set IsolationMode to 1 and do a select on the table for that specific record.

It does not give me an SQL 100 as I would have expected. It is a new record and hasn't been committed yet.
What it did give is -114 (Unable to get Shared Lock), BUT the values were still put into the binded variables I selected into.

With an update the same thing. I got a SQLCODE of -114, but the new values were actually put into the binded variables.

I guess there is no real uncommitted data functionality then.

score 0 · Answer 6 · 2024-04-05T10:55:54-04:00

I have no background information about your problem, but somehow I have the feeling, your have an organizational problem and not an isolational.

If one or more processes perform certain work under transaction and at the same time they feed a queue too ( I call it the WorkQueue) BEFORE the transaction is finished AND the queue is processed in parallel by an third process, then:
- either the third process (that with $order()) has to wait for the transaction to be finished or
- the process under transaction has to inform that third process about the outcome (i.e. rollback) of the transaction by feeding an second queue (I call this as the RolbackQueue).

The above implies, that
- before I put an item into the RollbackQueue, I would check, if it's already processed (from the WorkQueue), and if not, remove it from the WorkQueue and do not put it into the RollbackQueue
- in that third process, before processing an item, I would check, if that item is in the RollbackQueue. If yes, just do not process it (and remove from both queues)

As simple as that, merely I don't know your requirements.