search

LinqToDB4iSeries / Linq2DB4iSeries

LinqToDB provider for the DB2 database on IBM iSeries
MIT License
18 stars 5 forks source link

[Question] TransactionScope #26

Open RoyC-Retail-Assist opened 6 years ago

RoyC-Retail-Assist commented 6 years ago

Has anyone reliably managed to get TransactionScope working with the iAccess ADO.Net provider?

nsarris commented 6 years ago

I tried with the DB2Connect driver that supports it but is seems to esclate to XA transation from the first one. If the transaction is not commited or rolled back (process stop, debug stop crash) this leaves lingering transations on the i series that need to be stopped in console. An IBM advisor took a look but we never got a proper answer.

Can I ask what's the behaviour with the Client Access driver?

RoyC-Retail-Assist commented 6 years ago

Leaving aside the edge cases where a program abnormally terminates, If the inner transaction is rolled back then an exception is sometimes raised from the iAccess ADO.Net and it leaves the transaction active on the iSeries. It is very time sensitive and I think it is to do with a COM component being destroyed but whatever it is it isn't safe enough for production code.

It's interesting that DB2Connect also has issues as it is something that we have been considering.

nsarris commented 6 years ago

The iSeries only supports XA transations which MSDTC supports(its a transaction coordination service embedded in windows since 2000, and yes it's a COM component). XA transactions have to be enabled on the MSDTC service for it to work.

I haven't tested client access enough, but I suspect that when a second transation is added it escalates to a distributed XA transation (sql server for example doesn't escalate if the connections taking part are on the same server). With the DB2Connect driver we found out that it escalates even with a single connection. We didn't notice the problem you mentioned with rollbacks, but we did notice the problem with lingering transactions with abnormal program termination. There is a LocalTransaction property on the DB2Connection class but we didn't test it further.

The IBM engineer on site also told us other have been having similar unresolved issues with XA transations. We do need this functionality as well, so I'll try to pressure IBM more on this sometime in the future.