Question about transaction that spans MSS DataServer and Ope

Posted by dbeavon on 03-Jan-2018 14:32

I wrote my first ABL transaction against MSS dataserver just now.  The very next thing I tried after my SQL Server transaction worked was to update a record in an OE database during the same DO TRANSACTION block.  In other words, the "DO TRANSACTION" block was used to make updates to both the OE database and the SQL Server database.

That worked without any errors - which is a bit scary.

I am going to bet that there was little going on insofar as transaction coordination or two-phase commit.  And each database has its own transaction log which opens the possibility for one side to get rolled forward while the other rolls backward.

I'm assuming that there are best practices that tell people *not* to do this type of thing?  But given that developers aren't prevented from doing this, how will we manage it when these types of transactions generate an error?  Should we continually check the databases to look for committed data on one side and not on the other?  Can we assume one type of database always commits before the other (ie. OE before MSS)?  Any help would be appreciated.  I found a number of resources related to MSS dataserver but nothing that talked about transaction coordination between OE's DBMS and MSS.

Thanks in advance, David

Posted by gus bjorklund on 04-Jan-2018 09:46

while there is no standardized two-phase commit protocol between MS SQL Server and the OpenEDge database, there is some knowledge in the transaction coordinator of the nature of the transaction participants.

in your example, the MSS transaction would be committed first and if the commit fails, the OpenEdge transaction will be rolled back.

that isn’t the same as proper two-phase commit but it is better than nothing at all.

Posted by gus bjorklund on 08-Jan-2018 13:58

>

> So based on that, it sounds like a SQL Server error message is the "good" one to look for because it almost certainly means that updates on both the SQL and OE side of things will be properly rolled back.

>

> On the other hand, an error message from the OE database (ie. unique constraint violation for example) may be a much more scary one ... because there is a possibility that the error message could arise *after* the SQL Server update operation has already committed. (IE. the OE database update operation is to be rolled back, but the SQL Server update will not.)

>

> This is nice information to know. Have you ever seen this type of thing in documentation? Personally, I would rather that this would have just failed by default (ie. with an optional parameter, -feelingextremelylucky, to allow the two databases to participate in the same "transaction").

>

Errors that happen /before/ commit processing begins will cause all participants to be rolled back.

Constraint violations should occur before commit processing.

The two-phase commit protocols is what enabled multiple databases (and other things) to participate in the “same” global transaction. But each participant also has its own local transaction. Coordinating the several local transactions so that all achieve the same outcome is the purpose of two-phase commit protocols.

Not sure what you mean by "allow the two databases to participate in the same “transaction” “.

All Replies

Posted by gus bjorklund on 04-Jan-2018 09:46

while there is no standardized two-phase commit protocol between MS SQL Server and the OpenEDge database, there is some knowledge in the transaction coordinator of the nature of the transaction participants.

in your example, the MSS transaction would be committed first and if the commit fails, the OpenEdge transaction will be rolled back.

that isn’t the same as proper two-phase commit but it is better than nothing at all.

Posted by dbeavon on 04-Jan-2018 12:00

So based on that, it sounds like a SQL Server error message is the "good" one to look for because it almost certainly means that updates on both the SQL and OE side of things will be properly rolled back.  

On the other hand, an error message from the OE database (ie. unique constraint violation for example) may be a much more scary one ... because there is a possibility that the error message could arise *after* the SQL Server update operation has already committed.  (IE. the OE database update operation is to be rolled back, but the SQL Server update will not.)

This is nice information to know.  Have you ever seen this type of thing in documentation?   Personally, I would rather that this would have just failed by default (ie. with an optional parameter, -feelingextremelylucky, to allow the two databases to participate in the same "transaction").

Posted by gus bjorklund on 08-Jan-2018 13:58

>

> So based on that, it sounds like a SQL Server error message is the "good" one to look for because it almost certainly means that updates on both the SQL and OE side of things will be properly rolled back.

>

> On the other hand, an error message from the OE database (ie. unique constraint violation for example) may be a much more scary one ... because there is a possibility that the error message could arise *after* the SQL Server update operation has already committed. (IE. the OE database update operation is to be rolled back, but the SQL Server update will not.)

>

> This is nice information to know. Have you ever seen this type of thing in documentation? Personally, I would rather that this would have just failed by default (ie. with an optional parameter, -feelingextremelylucky, to allow the two databases to participate in the same "transaction").

>

Errors that happen /before/ commit processing begins will cause all participants to be rolled back.

Constraint violations should occur before commit processing.

The two-phase commit protocols is what enabled multiple databases (and other things) to participate in the “same” global transaction. But each participant also has its own local transaction. Coordinating the several local transactions so that all achieve the same outcome is the purpose of two-phase commit protocols.

Not sure what you mean by "allow the two databases to participate in the same “transaction” “.

Posted by dbeavon on 08-Jan-2018 19:08

I really appreciate the pointers.  I don't suppose there are any docs about this stuff?  I know that each database has its own local transaction, but beyond that it is just a matter of guessing how OpenEdge might coordinate the committing of them.

Having a consistent outcome is the goal and it sounds like the DataServer technology does a good job to try and make that happen.  It reminds me a little bit of the procedures we use to send a message out to the Sonic MQ broker at the very *end* of an OpenEdge DBMS transaction.  We procedurally try to make sure that it happens right before the ABL's "END. /*TRANSACTION*/.  And we also put some code before the beginning of the transaction to try to protect against the possibility that someone will one day wrap an even larger transaction around the whole mess, thereby circumventing the efforts to send/commit the Sonic message as the very last step.  

(IE. you can roll back the database transaction, but you can't un-send a Sonic MQ message after you've manually committed it).

I don't suppose Progress could incorporate this Sonic ABL "commit" operation, and make it a function of the DataServer product too?  I have always wished that ABL would coordinate the committing of our Sonic messaging operations.  It would make me sleep better at night. ;)

Posted by gus bjorklund on 10-Jan-2018 13:48

Sorry, there are no docs on two-phase commit other than what the dataserver docs and the database administration guide has. The 4GL does two forms of two-phase commit with OpenEdge databases and one (i think maybe not documented) with dataservers.

It is technically possible to make sonic messages transactional (i think sonic supports JTA (Java’s two-phase commit API)). This would mean that if a global transaction rolls back, messages are not sent and database changes are undone.

This is not an easy project - the devil is in the details. most of the interesting failure modes involve crashes and other things (e.g. network outages) that happen during commit processing. furthermore, if a failure occurs after all the participants are ready to commit, then ensuring that all the participants reach the same result is not easy and requires special-purpose maintenance tooling.

JTA is the Java Transaction API, which is supported by the OpenEdge database (careful what you wish for). Using it requires another component called a transaction manager. The TM is responsible for maintaining the association among global transaction identifiers generated by the TM and the local ones generated by the individual participants, orchestrating the details of successful transaction commitment, and dealing with any required transaction recovery in the event of a failure after commit processing starts. As far as I know, sonic does not provide a TM and the inbuilt OpenEdge one is not usable with JTA.

HTH. But perhaps it is more than you wanted to know.

Regards,

Gus

> On Jan 8, 2018, at 8:10 PM, dbeavon wrote:

>

> Update from Progress Community

>

> dbeavon

>

> I really appreciate the pointers. I don't suppose there are any docs about this stuff? I know that each database has its own local transaction, but beyond that it is just a matter of guessing how OpenEdge might coordinate the committing of them.

>

> Having a consistent outcome is the goal and it sounds like the DataServer technology does a good job to try and make that happen. It reminds me a little bit of the procedures we use to send a message out to the Sonic MQ broker at the very *end* of an OpenEdge DBMS transaction. We procedurally try to make sure that it happens right before the ABL's "END. /*TRANSACTION*/. And we also put some code before the beginning of the transaction to try to protect against the possibility that someone will one day wrap an even larger transaction around the whole mess, thereby circumventing the efforts to send/commit the Sonic message as the very last step.

>

> (IE. you can roll back the database transaction, but you can't un-send a Sonic MQ message after you've manually committed it).

>

> I don't suppose Progress could incorporate this Sonic ABL "commit" operation, and make it a function of the DataServer product too? I have always wished that ABL would coordinate the committing of our Sonic messaging operations. It would make me sleep better at night. ;)

>

>

> View online

>

>

> You received this notification because you subscribed to the forum. To unsubscribe from only this thread, go here.

>

> Flag this post as spam/abuse.

>

>

regards,

gus

“less is my favorite editor. too bad it can’t actually edit files.”

Chris Lesniewski-Laas

This thread is closed