Are we supposed to be using Transaction Guard? What's the alternative for Thin mode?

[mkmoisen]

The Oracle Concepts documentation has a section on Transaction Guard where it states that if a client submits a transaction, there is a possibility that the transaction could succeed in Oracle but the result would fail to be delivered to the client for example due to a network disconnect, and if the client resubmits the transaction it could result in duplicate transactions or other forms of logical corruption.

That sounds pretty bad.

I've never heard of transaction guard and have never seen anyone use it at any of the companies I have worked in.

Are we supposed to be using it?

However, I see in the oracledb documentation that this is only available in thick mode. I've already converted all my code over to thin mode.

What should we do in thin mode if the commit fails?

[anthony-tuininga]

If the commit fails you would normally examine the ltxid attribute of the connection and then call dbms_app_cont.get_ltxid_outcome() to find out if the commit succeeded but the database couldn't respond or if the commit itself failed. This is shown in the transaction_guard.py sample. Unfortunately, this is not currently available in thin mode. You can make a request for this to be added by creating an enhancement issue out of this discussion. I'm not sure at the moment how much effort is required. Until then, the only other option is to perform a query to look to see if the relevant rows were inserted/updated/deleted. This, of course, requires knowledge of the transaction whereas the ltxid approach does not -- but it will work today. Whether this is necessary or adequate depends on your application architecture and whether you automatically resubmit transactions on failure or not!

[mkmoisen]

@anthony-tuininga

Thank you. I'm trying to determine if this is something I need to consider for a standard CRUD REST API. Would you please let me know your thoughts?

The vast majority, perhaps even all, of my APIs would prevent duplicate inserts because I have a natural primary key. I don't have any APIs that perform updates such as SET foo = foo - 1, so duplicate updates wouldn't be a problem either.

Due to that reason, it seems totally fine to fail with a 500 to the user and let the user retry.

Does that make sense to you? I'm not sure if I am missing any crucial piece that would warrant a deeper look into Transaction Guard for a CRUD REST API.

Even if I had an API without a natural primary key, and I was using Transaction Guard, the REST API could always itself fail with a network disconnect, even if Transaction Guard successfully retried/did not retry the transaction. In this case, if the user retries on the UI it would still result in a duplicate insert anyways. I suppose I could write my own transaction guard by having the UI submit some idempotent key that I cache in the server with the result of the transaction.

Do you happen to know of a good example use case where it would make a lot sense to have Transaction Guard?

[cjbj]

A better overview than I could write is the Transaction Guard documentation.