MySQL Transactions

traufeisen · Post by **traufeisen** » Fri 03 Apr 2009 08:53

Hi,
I´m using the Entity Framework with the latest dotconnect driver (5.00.26).
I´ve noticed, that changes are not applied in a transaction.
Even if I create a MySqlConnection "by hand" and starting a transaction, all changes are applied on a new connection.
When there are 3 changes, the mysqld-querylog looks like this:

connection-id command

1000 an insert command
1001 another insert
1002 an update

How to fix this?
Regards

AndreyR · Post by **AndreyR** » Fri 03 Apr 2009 14:51

Could you please post the code you use for updates and inserts?
If you call the SaveChanges() method in the different instances of your ObjectContext, connection objects should be different.

traufeisen · Post by **traufeisen** » Fri 03 Apr 2009 15:03

Hello,
First of all, I´m talking about Linq to Sql and not the Entity Framework (that was another Test-Run).

My Code:

customerDataContext dataContext = new customerDataContext(connectionString);
Contract contract = new Contract();
contract.CustomerDataId = 1;
contract.ContractStatusId = 1;
contract.ProductMainId = 1;
dataContext.Contracts.InsertOnSubmit(contract);

Contract contract2 = new Contract();
contract2.CustomerDataId = 1;
contract2.ContractStatusId = 1;
contract2.ProductMainId = 1;
dataContext.Contracts.InsertOnSubmit(contract2);

dataContext.SubmitChanges();

The SQL-Queries are ok, but they are on different Connections and without any Transaction.
Even if I create the connection manually

MySqlConnection mySqlConnection = new MySqlConnection(connectionString);
mySqlConnection.Open();
mySqlConnection.BeginTransaction();

customerDataContext dataContext = new customerDataContext(mySqlConnection);

This starts a Transaction on the first connection, but the queries are executed on others.

I´ve also noticed, that accessing dataContext.Connection throws a NotImplementedException

var foo = dataContext.Connection; // <- Exception

AndreyR · Post by **AndreyR** » Mon 06 Apr 2009 08:55

We have no technical possibility to provide full support for the scenario with single local transaction.
It is related to the fact that MySQL connections do not support multiple opened readers simultaneously (that's why a new connection is created).
We are working on implementing the TransactionScope class support for MySQL, this will enable the behaviour you describe.
But if you are sure that your code does not use multiple readers you can work with the DataContext.Connection.Transaction property.
This is implemented in dotConnect for MySQL 5.20.24 Beta.

traufeisen · Post by **traufeisen** » Tue 07 Apr 2009 10:09

I´ve installed 5.20.24 beta, but public System.Data.Common.DbConnection Connection { get; } doesn´t contain a property named Transaction.
So I´ve tried casting the Connection back to a MySqlConnection and invoke BeginTransaction/Commit/Rollback as needed, but the queries are still executed on different connections.

AndreyR · Post by **AndreyR** » Tue 07 Apr 2009 12:32

Sorry, I made a mistake. I meant the DataContext.Transaction property.

traufeisen · Post by **traufeisen** » Wed 08 Apr 2009 07:43

Thanks. Transactions works fine.
But inserting a new object now results in an Exception:

Cannot save object with state: a.

bei Devart.Data.Linq.i.a(e A_0)
bei Devart.Data.Linq.i.e()
bei Devart.Data.Linq.DataContext.SubmitChanges()

I know, 5.20.24 is still beta. Just wanted to report this

AndreyR · Post by **AndreyR** » Wed 08 Apr 2009 11:07

Thank you for the report.
However, I still cannot reproduce the described error.
Could you please send me (support * devart * com, subject "LINQ: Transaction exception") a small test project reproducing it?

traufeisen · Post by **traufeisen** » Wed 08 Apr 2009 12:06

I´ve looked further and found the problem.
I´ve created a Class which inherits from the DataContext and override the SubmitChanges-Method to perform various validations.
This works fine so far, but if one of the validators fetches the database, the error occurs.

For example:
I´ve a Contract object which represents a Customer contract.
This object holds a CustomerId.
The Validator tries to fetch the associated Customer for additional logic.
If everything is ok, my SubmitChanges-Method simply calls base.SubmitChanges()
This works fine with the stable version.
The beta runs the Insert-Query for the Contract and then throws the exception.
If I comment out the part in the validator, that fetches the customer-object, it works again.
I´ve added a "ChangeSet changeSet = GetChangeSet();" right before base.SubmitChanges() to look if the Customer Object is in the changeset or something like this, but there is only the new Contract Object.

AndreyR · Post by **AndreyR** » Thu 09 Apr 2009 12:20

I'm still unable to reproduce the problem.
Could you try installing the upcoming Beta 2 when it is available and try to reproduce the problem?

traufeisen · Post by **traufeisen** » Thu 09 Apr 2009 15:30

I´ve finally found the problem.
This error occurs, if the class doesn´t implement INotifyPropertyChanging and INotifyPropertyChanged.
After adding these interfaces, everything works fine again.
Even if the events PropertyChanging and PropertyChanged never get fired.