I've discovered a problem I'm hoping someone can advice on an easy solution for. In short, the replication processor dosen't appear to handle record locks on ReplQueue. I'm guessing it's by design, as it should only ever be locked by the processor itself, but I've discovered a situation where it could remain locked by the front end GUI....this is occurring on a database with ~2000 tables, and every one of them is replicated to an MS SQL DB, running real time.
1. Transaction started.
2. A record is created or updated on TableA
3. The program which made this change has a "Release TableA." command.
4. Then a period of time occurs before the transactions is committed.
The 'Release' command in step 3 is instigating the triggers attached to TableA, which is creating the ReplQueue record, but leaving all records locked, including the ReplQueue record. The replication engine detects the ReplQueue entry, performs the push to SQL, then tries to delete the ReplQueue record, but can't because it's still locked by the Users session, leaving the replication engine stuck until it either crashes due to wait time expire or the transaction is committed.
There are 2 problems here, (although I'm thinking there's one solution) the first is that the replication engine is then hanging until the user clears the lock or it crashes, and the second is that if the transaction is never committed, it will actually leave replicated record in the SQL database, until either (if it was a create event) a new record is created with the same RECID, or (if it was an update event) a subsequent event occurs on that record which then refreshes the SQL version of the record.
Ideally I'd like to see the Replication engine skip records that are locked, as this would prevent both problems. Unfortunately I can't change the GUI coding to avoid pending transactions from staying open, which regardless of this problem isn't a great idea.
Any advice greatly appreciated,
What version of Pro2 are you using?
Russian Progress User Group
Pro2 is taking care of the locking because in the logfiles of the thread you can find things like this:
06-06-2018 15:08:22 - Replication Queue Finished (ReplBlock). Processed a total of 216 records. Total time to process these items was: 00:00:01
06-06-2018 15:08:22 - 3 records were locked, and
3 transactions were open during this run.
Every run it checks whether the records are available and unlocked yet or not. If not, they are not processed and re-checked every run till they are.
But I guess we have the same issue as you. In the past 2 months it happened 3 times. The thread crashes and after a restart, it crashes immediately. I guess, it is not the replqueue-record that is locked because I'm able to change the replqueue.qthread-field of that record (I change it to 6 so no other thread will pick it up). After changing the thread of the first replqueue-record that is not yet applied, pro2 works fine.
I have no clue how this can happen, I'm still in "investigation phase"..
Pro2 version 5.0.2.
Apparently my issue is caused by what's written in this article: knowledgebase.progress.com/.../PRO2-Bulk-load-failed-due-to-data-exceeding-32000
In our sql-database there are 3 records where then content of 1 field is longer then 32000 and now and update of this sql-record is not possible because it can't be read anymore from within Progress. I did an update of the sql-record (in sql mgmt studio) and now progress can access the records and update them with what's is the replqueue-table...
Maybe it will help you too...