Devart Forums

Posted: **Tue 26 May 2015 07:42**

Hello,
We use dotConnect for Mysql. We also need to use replication and we want to keep STATEMENT binlog mode.
Is it possible to change default transaction isolation level for insert/update queries through data context? We set REPEATABLE READ as default for Mysql server. However, "SubmitChanges()" method still override this setting by executing statement to change it to "READ COMMITED".
Then we get exception:
"Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'"

I know that there is a workaround to wrap all queries to explicit transaction. However, it is really not possible for complex projects. We can't change thousands rows of a code. Is it anyhow possible to avoid changing isolation level in SubmitChanges method?
Thank you.

Posted: **Tue 26 May 2015 12:01**

I have tried override SubmitChange method and wrap each modyfing queries inside this method with own transaction.

Code: Select all

public override void SubmitChanges()
        {
            if (this.Transaction != null) 
            {
                base.SubmitChanges();
                return;
            }

            if (this.Connection.State == ConnectionState.Closed)
                this.Connection.Open();
            using (this.Transaction = this.Connection.BeginTransaction(IsolationLevel.RepeatableRead))
            {
                base.SubmitChanges();
                this.Transaction.Commit();
            }
        }

After a short testing it seems it is working. But I'm not sure if it will work correctly for each query. For example if datacontext will be re-used for multiple queries, etc.

Posted: **Tue 26 May 2015 13:10**

dilbert wrote:However, "SubmitChanges()" method still override this setting by executing statement to change it to "READ COMMITED".

This is an expected behaviour. A similar question and a possible solution have been discussed here: http://forums.devart.com/viewtopic.php?t=24987#p84724
Your solution looks correctly and there should be no problems with this. However, if you encounter any further issue with this, feel free to contact us.

Devart Forums

Changing isolation level for data context

Changing isolation level for data context

Re: Changing isolation level for data context

Re: Changing isolation level for data context