Problem with transaction in TxQuickstart

[vuttruong]

Hello,

I use TxQuickstart application.

If I configure Credits uses MySQL 4.1.20 and Debits uses SQL Server 2005, I got an exception "Specified method is not supported." when it tried to open the second connection (Debits connection).

If I change Credits uses SQL Server 2005and Debits uses MySQL 4.1.20, it works well.

Certainly, I set ThrowException=false in DTCAppContext.xml. I build TxQuickstart with .NET 2.0.

Any helps would be appreciated.

Regards,

vuttruong

[pathakn]

I think there is problem in the DB provider id name - it should be DbProvider2 for Debits. Hope that helps.

[Mark Pollack]

Hi,

If you could post your configuration file and a stack trace, that would help alot to diagnose the problem.

Cheers,
Mark

[vuttruong]

Hello Mark,

I just change in DTCAppContext.xml as following

<db:provider id="DebitDbProvider"
provider="System.Data.SqlClient"
connectionString="Data Source=mssqlserver;Initial Catalog=Debits;User ID=sa; Password=sa"/>

<db:provider id="CreditDbProvider"
provider="MySql.Data.MySqlClient"
connectionString="server=mysqlserver;uid=root;pwd=www123;database=Cr edits;"/>

and the exception


Unhandled Exception: Spring.Data.CannotGetAdoConnectionException: Could not get ADO.NET connection. ---> System.NotSupportedException: Specified method is not supported.
at MySql.Data.MySqlClient.MySqlPromotableTransaction. System.Transactions.ITransactionPromoter.Promote()
at System.Transactions.TransactionStatePSPEOperation. PSPEPromote(InternalTransaction tx)
at System.Transactions.TransactionStateDelegatedBase. EnterState(InternalTransaction tx)
at System.Transactions.EnlistableStates.Promote(Inter nalTransaction tx)
at System.Transactions.Transaction.Promote()
at System.Transactions.TransactionInterop.ConvertToOl etxTransaction(Transaction transaction)
at System.Transactions.TransactionInterop.GetExportCo okie(Transaction transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.Enlist NonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist (Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Act ivate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.Acti vateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Spring.Data.Support.ConnectionUtils.DoGetConnectio n(IDbProvider provider) in C:\Spring.NET\src\Spring\Spring.Data\Data\Support\ ConnectionUtils.cs:line 147
at Spring.Data.Support.ConnectionUtils.GetConnectionT xPair(IDbProvider provider) in C:\Spring.NET\src\Spring\Spring.Data\Data\Support\ ConnectionUtils.cs:line 102
--- End of inner exception stack trace ---
at Spring.Aspects.Exceptions.ExceptionHandlerAdvice.I nvoke(IMethodInvocation invocation) in C:\Spring.NET\src\Spring\Spring.Aop\Aspects\Except ions\ExceptionHandlerAdvice.cs:line 147
at Spring.Aop.Framework.AbstractMethodInvocation.Proc eed() in C:\Spring.NET\src\Spring\Spring.Aop\Aop\Framework\ AbstractMethodInvocation.cs:line 282
at Spring.Aop.Framework.DynamicProxy.AdvisedProxy.Inv oke(Object proxy, Object target, Type targetType, MethodInfo targetMethod, Object[] args, IList interceptors) in C:\Spring.NET\src\Spring\Spring.Aop\Aop\Framework\ DynamicProxy\AdvisedProxy.cs:line 222
at CompositionAopProxy_614abc78cded4036a682a9d604e516 fd.DoTransfer(Single creditAmount, Single debitAmount)
at Spring.TxQuickStart.AccountManagerTests.Main(Strin g[] args) in C:\Spring.NET\examples\Spring\Spring.TxQuickStart\ test\Spring\Spring.TxQuickStart.Tests\TxQuickStart \AccountManagerTests.cs:line 80

Please help me to review it.

Thanks,

vuttruong

[Mark Pollack]

Hi Vuttruong

I see it now, MySql doesn't support Promotable Single Phase Enlistment which is required if you are going to use distributed transactions through System.Transactions. AFAIK only Sql Server libraries support this functionality. You can try to switch to using ServiceDomainPlatformTransactionManager which uses .NET Enterprise Services to perform distributed transactions. See this section (http://www.springframework.net/doc-latest/reference/html/transaction.html#key-abstractions) of the docs for an overview. This is likely to be supported by MySql but you should check beforehand. (Let me know what you find). The transaction example has a configuration file named DTC1.1AppContext.xml that uses this transaction manager instead.

Cheers,
Mark

[vuttruong]

Hello Mark,

Thanks for your quick help.

But could you give me some advices in 2 questions below?

1)
In previous message, I told that If I changed Credits used SQL Server 2005 and Debits used MySQL 4.1.20, it worked well.

In source code,
creditDao.CreateCredit(creditAmount) is called before
debitDao.DebitAccount(debitAmount). I don't know why if I change db, it works. Do you have any idea?

2)
Moreover, in the case I keep Credit with MySQL and Debits with SQL Server, as your suggest, I change transaction manager in DTCAppContext.xml as following


<object id="transactionManager" type="Spring.Data.Core.ServiceDomainPlatformTransactionM anager, Spring.Data"> </object>

I catch exception below:

Unhandled Exception: Spring.Transaction.CannotCreateTransactionExceptio n: ServiceDomain failure on begin of transaction ---> System.ArgumentException: Requested value 'Unspecified' was not found.
at System.Enum.Parse(Type enumType, String value, Boolean ignoreCase)
at System.Enum.Parse(Type enumType, String value)
at Spring.Data.Core.ServiceDomainPlatformTransactionM anager.CreateServiceConfig(ITransactionDefinition definition) in C:\Spring.NET\src\Spring\Spring.Data\Data\Core\Ser viceDomainPlatformTransactionManager.cs:line 161
at Spring.Data.Core.ServiceDomainPlatformTransactionM anager.DoServiceDomainBegin(ITransactionDefinition definition) in C:\Spring.NET\src\Spring\Spring.Data\Data\Core\Ser viceDomainPlatformTransactionManager.cs:line 103
at Spring.Data.Core.ServiceDomainPlatformTransactionM anager.DoBegin(Object transaction, ITransactionDefinition definition) in C:\Spring.NET\src\Spring\Spring.Data\Data\Core\Ser viceDomainPlatformTransactionManager.cs:line 88
--- End of inner exception stack trace ---
at Spring.Aspects.Exceptions.ExceptionHandlerAdvice.I nvoke(IMethodInvocation invocation) in C:\Spring.NET\src\Spring\Spring.Aop\Aspects\Except ions\ExceptionHandlerAdvice.cs:line 147
at Spring.Aop.Framework.AbstractMethodInvocation.Proc eed() in C:\Spring.NET\src\Spring\Spring.Aop\Aop\Framework\ AbstractMethodInvocation.cs:line 282
at Spring.Aop.Framework.DynamicProxy.AdvisedProxy.Inv oke(Object proxy, Object target, Type targetType, MethodInfo targetMethod, Object[] args, IList interceptors) in C:\Spring.NET\src\Spring\Spring.Aop\Aop\Framework\ DynamicProxy\AdvisedProxy.cs:line 222
at CompositionAopProxy_66c27288e2444de098c0a479821463 e8.DoTransfer(Single creditAmount, Single debitAmount)
at Spring.TxQuickStart.AccountManagerTests.Main(Strin g[] args) in C:\Spring.NET\examples\Spring\Spring.TxQuickStart\ test\Spring\Spring.TxQuickStart.Tests\TxQuickStart \AccountManagerTests.cs:line 80

I wonder I made some thing wrong.

Many thanks for your idea.

vuttruong

[Mark Pollack]

Hi Vuttruong,

I don't have a good idea why that is going on in the case you mention but I'm quite curious. With credits=sqlserver and debits=mysql, the tx that is getting promoted is the sqlsever based one... I don't know what is happening with the mysql tx in this case. Is there a way for you to monitor distributed transactions in mysql? It would be useful to know if it is infact performing a distributed transaction or just 'faking it' in some way. If you could post a question on the mysql mailing list asking what is the general support for distributed transactions using System.Transaction, that would be a big help.

As for #2, this looks like a genuine bug to me...let me run some of my own code, write some unit tests later today, and get back to you. There needs to be a translation between the enum System.Data.IsolationLevel and the enum System.EnterpriseServices.TransactionIsolationLeve l that takes into account enum values on 'IsolationLevel' enum but not on the 'TransactionIsolationLevel' enum. These are Unspecified (the default for System.Transactions), Snapshot, and Chaos. Unfortunately, now I see that somehow the 'Any' 'TransactionIsolationLevel' can't be specified. I'll have to figure out how to sneak that in.

As a workaround for now, if you selet ReadCommitted, ReadUncommitted, RepeatableRead, or Serializable as the isolation level then the current enum translation code will work. This can be done on the attribute.

If that doesn't work, for the time being, you open the .sln file, comment out the line that is parsing the isolation level (161), rebuild, and it should work using default isolation level...

Cheers,
Mark

[vuttruong]

Hello Mark,


I don't have a good idea why that is going on in the case you mention but I'm quite curious. With credits=sqlserver and debits=mysql, the tx that is getting promoted is the sqlsever based one... I don't know what is happening with the mysql tx in this case. Is there a way for you to monitor distributed transactions in mysql? It would be useful to know if it is infact performing a distributed transaction or just 'faking it' in some way. If you could post a question on the mysql mailing list asking what is the general support for distributed transactions using System.Transaction, that would be a big help.

You are right. There is a problem with mysql tx when using System.Transaction. I try to put a throwing exception after "debitDao.DebitAccount(debitAmount)" like this


[Transaction]
public void DoTransfer(float creditAmount, float debitAmount)
{
creditDao.CreateCredit(creditAmount); // SQL Server 2005

if (ThrowException)
{
throw new ArithmeticException("Couldn't do the math....");
}

debitDao.DebitAccount(debitAmount);

throw new Exception("Force to rollback"); // MySQL 4.1.20
}

The tx on SQL Server 2005 works well (no record is inserted), but mysql tx doesn't work. A record is still inserted into mysql database. Before I didn't try the failure case in inserting mysql, so I thought it works.


As for #2, this looks like a genuine bug to me...let me run some of my own code, write some unit tests later today, and get back to you. There needs to be a translation between the enum System.Data.IsolationLevel and the enum System.EnterpriseServices.TransactionIsolationLeve l that takes into account enum values on 'IsolationLevel' enum but not on the 'TransactionIsolationLevel' enum. These are Unspecified (the default for System.Transactions), Snapshot, and Chaos. Unfortunately, now I see that somehow the 'Any' 'TransactionIsolationLevel' can't be specified. I'll have to figure out how to sneak that in.

As a workaround for now, if you selet ReadCommitted, ReadUncommitted, RepeatableRead, or Serializable as the isolation level then the current enum translation code will work. This can be done on the attribute.

I try to change



[Transaction]
public void DoTransfer(float creditAmount, float debitAmount)
..........


by



[Transaction(Spring.Transaction.TransactionPropagat ion.Required,
System.Data.IsolationLevel.Serializable)]
public void DoTransfer(float creditAmount, float debitAmount)
..........


and I catch the old exception



Unhandled Exception: Spring.Data.CannotGetAdoConnectionException: Could not get ADO.NET connection. ---> System.NotSupportedException: Specified method is not supported.
at MySql.Data.MySqlClient.MySqlPromotableTransaction. System.Transactions.ITransactionPromoter.Promote()
at System.Transactions.TransactionStatePSPEOperation. PSPEPromote(InternalTransaction tx)
at System.Transactions.TransactionStateDelegatedBase. EnterState(InternalTransaction tx)
at System.Transactions.EnlistableStates.Promote(Inter nalTransaction tx)
at System.Transactions.Transaction.Promote()
at System.Transactions.TransactionInterop.ConvertToOl etxTransaction(Transaction transaction)
at System.Transactions.TransactionInterop.GetExportCo okie(Transaction transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.Enlist NonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist (Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Act ivate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.Acti vateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Spring.Data.Support.ConnectionUtils.DoGetConnectio n(IDbProvider provider) in C:\Spring.NET\src\Spring\Spring.Data\Data\Support\ ConnectionUtils.cs:line 147
at Spring.Data.Support.ConnectionUtils.GetConnectionT xPair(IDbProvider provider) in C:\Spring.NET\src\Spring\Spring.Data\Data\Support\ ConnectionUtils.cs:line 102
--- End of inner exception stack trace ---
at Spring.Aspects.Exceptions.ExceptionHandlerAdvice.I nvoke(IMethodInvocation invocation) in C:\Spring.NET\src\Spring\Spring.Aop\Aspects\Except ions\ExceptionHandlerAdvice.cs:line 147
at Spring.Aop.Framework.AbstractMethodInvocation.Proc eed() in C:\Spring.NET\src\Spring\Spring.Aop\Aop\Framework\ AbstractMethodInvocation.cs:line 282
at Spring.Aop.Framework.DynamicProxy.AdvisedProxy.Inv oke(Object proxy, Object target, Type targetType, MethodInfo targetMethod, Object[] args, IList interceptors) in C:\Spring.NET\src\Spring\Spring.Aop\Aop\Framework\ DynamicProxy\AdvisedProxy.cs:line 222
at CompositionAopProxy_fb819d7ea5284ab7bec7b9a7cb4cb0 3e.DoTransfer(Single creditAmount, Single debitAmount)
at Spring.TxQuickStart.AccountManagerTests.Main(Strin g[] args) in C:\Spring.NET\examples\Spring\Spring.TxQuickStart\ test\Spring\Spring.TxQuickStart.Tests\TxQuickStart \AccountManagerTests.cs:line 80


However, I think the issue with mysql tx should be resolved at first of all.

Anyway, many thanks for your helps of worth.

Regards,

vuttruong

[Mark Pollack]

Hi,

Interesting regarding #1. As for case #2, the stack trace still shows System.Transactions and 'PSPE', which would seem odd if you are using ServiceDomainPlatformTransactionManager. Can you make sure you are using this transaction manager and *not* TxScopeTransactionManager. Do you know what the support is for distributed transactions with MySql generally speaking?

Cheers,
Mark

[vuttruong]

Hello Mark,



As for case #2, the stack trace still shows System.Transactions and 'PSPE', which would seem odd if you are using ServiceDomainPlatformTransactionManager. Can you make sure you are using this transaction manager and *not* TxScopeTransactionManager.


I use following tx manager configuration in DTCAppContext.xml



<object id="transactionManager" type="Spring.Data.Core.ServiceDomainPlatformTransactionM anager, Spring.Data">
</object>


instead of



<object id="transactionManager" type="Spring.Data.Core.TxScopeTransactionManager, Spring.Data">
</object>


About


Do you know what the support is for distributed transactions with MySql generally speaking?

I think distributed transaction on my version of MySQL is not good. I try with MySQL 5.0, and unfortunately, it is not better also. I don't know what I should do in this case.

However, now we have 2 db servers which use SQL Server 2005 and SQL Server 2000. TxScopeTransactionManager works well with these systems.

Thanks for your helps.

vuttruong