Question Copy replication trigger assignments.

TomBascom

Curmudgeon
You can FIND a changed (or newly created) record with a NO-LOCK read even while the transaction is uncommitted. This is called a “dirty read”.

So you can successfully read a record and have uncommitted content available to your program even if the record is eventually rolled back.

You can easily demonstrate this with two sessions open in two different windows by sprinkling PAUSE and MESSAGE statements around and stepping through some sample code.

Most of the time the chances of a problem arising from this are small and the potential impact is ignored. It is very common for application code to simply ignore this possibility.

But when you are doing something like tracking changes for replication purposes (or handling money) you tend to want a more careful approach.
 

TomBascom

Curmudgeon
To continue flogging the horse ;)

Suppose that the change_detail record stores ROWID and change type (CREATE, UPDATE, or DELETE). Now suppose that a DELETE is noted.

Let us also assume that this is "typical" application code that does things that it shouldn't, like prompt users while still in a transaction.

So, we have deleted a record and recorded that change. The user now has a "really delete this record?" prompt on their screen.

The chaser is looping along, notices a potential change but sees that the _Trans record exists, so it does nothing. So far, so good.

In an hour or so the user gets back from lunch just as the chaser decides to take another look. The chaser reads the change_detail record again and in the nano-seconds between that read and the check for _Trans the user says "oh crap! no! don't delete that!" and undoes the transaction.

_Trans goes away and the chaser thinks that the the transaction committed and ROWID has been deleted.

If the chaser is naive it is going to pass that information along and the target system is going to delete its equivalent record.

Later on an update to that record occurs in a perfectly normal manner and that update is forwarded to the target system. That system freaks out when it tries to find the proper record to update because we mistakenly deleted it above.

You avoid this possibility by being pessimistic with your chaser loop. Instead of the NO-LOCK read of change_details described previously you do something like this:
Code:
/* chaser.p
 */

do while true:

  find next change_details EXCLUSIVE-LOCK no-wait no-error.

  if available change_details then
    do:
       /* transaction has committed, process the change details */
    end.
  else
    do:
      pause 0.1.
    end.

end.

This approach completely avoids the possibility of a dirty read misleading you and eliminates any need to check _Trans.

The next tricky topic is ordering of changes (which is implied by the NEXT in my FIND statement above). If order matters, how do you ensure that things are done in the correct sequence? A timestamp is better than nothing but the granularity may not be good enough and you could have multiple changes with identical timestamps so you really probably want to use a sequence number. And you need to decide what to do if an uncommitted change is blocking a large queue of committed changes. Depending on your application and the purpose of all of this that could be a big problem or no problem at all.
 

tamhas

ProgressTalk.com Sponsor
I can't see how there is a chance of any problem.

Why does this seem like an invitation to a real head scratcher!?
 

ron

Member
I don't think that's correct, Tom.

If the user says "no" it initiates a roll-back. But the record that the "chaser" program holds is in the same transaction so it cannot "see" that the _Trans record is no longer present because the current pointer record is encumbered by the same transaction. Therefore, the roll-back cannot happen until the chaser program moves on to the next record.

Is that not so?
 

TomBascom

Curmudgeon
I am assuming that the “chaser” is a second process running in its own context looking for changes that need to be propagated. Is that not correct?
 

ron

Member
Yes it is a separate process -- but it accesses the pointer record with EXCL lock before taking the transaction# and seeing if the _Trans record exists. Since the pointer record was created as part of the trigger execution it is encumbered by the original transaction. If an undo occurred the pointer record would have been deleted. On the other hand, the fact that the pointer can be accessed with EXCL lock means, I think, that an undo has not happened. (Not yet, anyway.)

So -- the locked pointer record will prevent an undo during the time the chaser is testing _Trans.
 

TomBascom

Curmudgeon
The potential for a rollback causing issues like I described is an outcome of using NO-LOCK to read the pointer record.

If you are successfully getting an EXCLUSIVE-LOCK on the pointer record the transaction is over and has been committed. Checking _trans is pointless in that case.

If the transaction is still active an attempt to get an EXCLUSIVE-LOCK will fail.
 

ron

Member
Ah, yes, good point! :)

As you say, Tom, testing for _Trans existing is unnecessary. If the pointer record can be obtained with EXCL lock the original transaction must have ended with a commitment.

Sometimes the obvious is not obvious.
 

ron

Member
Hello !

Can I ask a very basic question, please?

At the time when a Replication-Write trigger executes it is my understanding that a transaction is open. Therefore the changed data may be captured but it should not be taken as "committed" until (at least) it is subsequently discovered that the transaction is no longer active.

Is that correct?
 
Last edited:

TomBascom

Curmudgeon
Assuming that we're all still on the same page about everything... yes.

If you can FIND EXCLUSIVE-LOCK the record from a different session then you can be confident that the transaction committed and is no longer active.
 

ron

Member
This is a comment, rather than a query, and it is probably a "Development" item, rather than "DBA" -- but it relates to this thread, so I put it here.

Table "A" is being replicated withe a write-trigger. The trigger copies the record from "A" to table "X". The fields in table "X" are identical to "A", except that a couple of new fields are added to facilitate the handling of the replication process. Two of these fields are "Seq" (a sequence number) and "RecType" (record type to indicate, for example 'new', 'update' or 'delete').

Table "X" is a regular database table. There is another table "Y" which is completely identical to "X" -- having all the same fields, including "Seq" and "RecType". The idea is that "x" only holds data for a short time until the associated transaction becomes committed.

A backgound process continually scans table "X" using EXCL lock. (Ability to EXCL lock being the indication that the update, or whatever, has been committed.) If it can be locked then the record is copied to table "Y" for further processing -- and deleted from "X".

Note that the only difference between an X record and a corresponding Y record created from it is the sequence number Seq. They are separately allocated. In X it is the sequence of creation as an X record. In Y it is the sequence of creation in Y -- and they could, of course, be very different no only in value but order. Table X and Y have a single index each -- on fields Seq and RecType (only)

The logic goes like this:

Code:
FOR EACH X NO-LOCK:
    FIND CURRENT X EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
    IF NOT AVAIL(X) THEN NEXT.
    ASSIGN ti-NextSeq = NEXT-VALUE(NextSeq_Y).
    CREATE Y.
    BUFFER-COPY X TO Y.
    ASSIGN Y.Seq = ti-NextSeq.
    DELETE X.
and so on.

During initial testing it all worked OK -- but when testing increased and data volumes increased I got errors to say I was trying to create duplicate records where the index was "unique". I found this very puzzling. I inserted debug statements that confirmed that the new record was, in fact, unique.

BUT -- it also confirmed something I was not expecting. The error was generated at the moment the BUFFER-COPY command executed. I was able to then fix the problem by inserting "EXCEPT x-Seq" in the BUFFER-COPY command.

I was expecting that Progress would not do anything regarding indexes until I attempted to end the transaction, which was several statement further on.

Ron.
 

TomBascom

Curmudgeon
Progress updates index fields as soon as it can. Both the BUFFER-COPY and the ASSIGN will immediately make new key values visible.

This is usually viewed as a helpful optimization ;)

You mention ending the transaction several statements further on. But I don't see any explicit start of the transaction so you might also have a transaction, record, and lock scope issues lurking in this code.

Just an observation; maybe I am missing something but the first 3 lines seem like an overly complicated way of saying FOR EACH X EXCLUSIVE-LOCK:
 

ron

Member
Fear not -- there were other instructions that I left out because I did want to distract from the main issue, which was the BUFFER-COPY.

I was just surprised by this because I have not encountered anything similar before. Just something to be aware of in the future. :)
 

Patrice Perrot

New Member
Hi Ron
You can also do the buffer-copy and the assign in 1 code line "BUFFER-COPY X TO Y ASSIGN Y.Seq = ti-NextSeq ." (without "." between them).
So all the fields are assigned in the same instruction.

Depending of your OE version , it could be faster . Only 1 index create instead of 1 index create (assign RecType in Buffer-copy) and 1 index delete + 1 index create ( Y.Seq = ti-NextSeq ).

Patrice
 

ron

Member
Thank you Patrice -- that's good to know. When dealing with high-volume replication, saving any time is important. The version of OE is 11.7.4.

Ron.
 
Top