Help me understand the locking of transactions.

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
KW
Posts: 135
Joined: Tue 19 Feb 2008 19:12

Help me understand the locking of transactions.

Post by KW » Thu 07 Oct 2010 21:40

I am trying to understand why my entire mysql instance gets locked up when I start to use transactions.

Here is some code i'm using:

Code: Select all

TransactionOptions options = new TransactionOptions();
        options.IsolationLevel = IsolationLevel.ReadUncommitted;

        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
        {
            try
            {
                con.Open();
                con.EnlistTransaction(Transaction.Current);       

       user = System.Web.Security.Membership.CreateUser(this.NewCustomerConfirmEmail.Text.Trim(), this.Password.Text, this.NewCustomerConfirmEmail.Text.Trim());
                    System.Web.Security.Roles.AddUserToRole(user.UserName, "Customer");
        
     MySqlCommand updatecust = new MySqlCommand("Update Customer set `First Name` = :FirstName, `Last Name` = :LastName, UserID = :UserID where CustomerID = :CustomerID", con);
                    updatecust.Parameters.Add("FirstName", MySqlType.VarChar); updatecust.Parameters[0].Value = NewCustomerFirstName.Text;
                    updatecust.Parameters.Add("LastName", MySqlType.VarChar); updatecust.Parameters[1].Value = NewCustomerLastName.Text;
                    updatecust.Parameters.Add("UserID", MySqlType.VarChar); updatecust.Parameters[2].Value = user.ProviderUserKey.ToString();
                    updatecust.Parameters.Add("CustomerID", MySqlType.VarChar); updatecust.Parameters[3].Value = CustomerID;
                    var reslt3 = updatecust.ExecuteNonQuery();

}
I have a userid column in the Customer table. It has a foreign key constraint referencing the userid in the aspnet_membership table. First, when I try to execute this mysql just freezes. I think its waiting for me to commit the transaction in the statement:

Code: Select all

  user = System.Web.Security.Membership.CreateUser(this.NewCustomerConfirmEmail.Text.Trim(), this.Password.Text, this.NewCustomerConfirmEmail.Text.Trim());
                    System.Web.Security.Roles.AddUserToRole(user.UserName, "Customer");
Second, I've noticed that when this failed and I call scope.dispose without calling commit the transaction hangs and mysql hangs on other users executing aspnet_membership upate activity.

I think I understand why the first problem is occurring. I want to execute creating a user and updating the customer userid column to the created user to execute as a transaction. But these two statements are in different transactions because there is no way to set or get the transaction that the membership class is using.

However, the second problem I can't wrap my head around. I thought transactions locked rows not the entire table? It seems if I execute the above code, it will time out and fall into the finally statement of my try/catch. In here it will call scope.dispose() but mysql locks up even after this is called not letting anyone read from the aspnet_membership or at least thats what I'm being told by users.

Can you help me understand what is going on? I'm not sure if it is the membership provider class or if there is another way to set the transaction that I'm not thinking about.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 08 Oct 2010 08:35

Please upgrade to the 5.80.170 version of dotConnect for MySQL where the bug with enlisting ASP.NET provider's activity to distributed transaction is fixed. Notify us if the issues persist with 5.80.170 as well.

KW
Posts: 135
Joined: Tue 19 Feb 2008 19:12

Post by KW » Fri 08 Oct 2010 18:13

I do have the latest version. Before the latest build it wouldn't allow me to even move past the code:

Code: Select all

user = System.Web.Security.Membership.CreateUser(this.NewCustomerConfirmEmail.Text.Trim(), this.Password.Text, this.NewCustomerConfirmEmail.Text.Trim()); 
It could be my misunderstanding of transactions - for some reason it is locking up the system.

I think the code I pasted is verbatum. Basically, I'm just trying to create a user, and then assign the userid to the customer's userid column which does have a foreign key restraint.

What happens after this code is executed, I get a mysql lost connection to the server, then when the code falls into the finally block and the scope.dispose() is called, it says lock wait timeout for the transacation.

Is there anyway to get or set the exact transaction the membership provider is using so I can join that specific transaction?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 13 Oct 2010 17:17

I have tried the 5.80.170 version of dotConnect for MySQL with MySQL Server v 5.0.87 and reproduced such behaviour: there is no hanging with the following code but it creates user and adds it to role inspite of scope.Dispose():

Code: Select all

            TransactionOptions options = new TransactionOptions();
            options.IsolationLevel = IsolationLevel.ReadUncommitted;      
            
            System.Web.Security.Roles.CreateRole("Customer");
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) {
                System.Web.Security.MembershipUser user = System.Web.Security.Membership.CreateUser("userapp", "password!app", "[email protected]");
                System.Web.Security.Roles.AddUserToRole(user.UserName, "Customer");
                scope.Dispose();
            }
We are investigating this issue.

As for the problems you have encountered, please give us the following information:
1) the version of your MySQL Server;
2) are you using the database objects from default InstallWebTables.sql script that is located in the folder of provider installation (you modified it or not)? Check the engine of created tables - is it InnoDB?
3) the script of your Customer table (and its engine);
4) as I understood, there are no exceptions in your case, are they? On what line does your program hang? Please give us your call stack.
5) a test project will be appreciated (send us).

Hints:
1) please use the Default Command Timeout connection string parameter is you want to limit the time of command execution;
2) Try to debug your application to find out the query that hangs by using the dbMonitor tool that performs per-component tracing of database events such as commit, rollback, SQL statement execute etc.
Download link: http://www.devart.com/dbmonitor/dbmon3.exe
Documentation: http://www.devart.com/dotconnect/mysql/ ... nitor.html
In dbMonitor you will find the SQL statement that fails to execute and the values of parameters. Try executing the same statement with the same parameters for updating your table using another tool (e.g., dbForge Studio for MySQL http://www.devart.com/dbforge/mysql/studio/). Does the problem persist in this case as well?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 14 Oct 2010 07:35

Transactions (local and global) are possible only with tables which are created with engine that supports transactions. It is InnoDB in standard MySQL distribution. The talbes for web providers in my database were created with the MyISAM engine. That's why I have obtained the mentioned behaviour.

KW
Posts: 135
Joined: Tue 19 Feb 2008 19:12

Post by KW » Wed 20 Oct 2010 18:54

I am using mysql 5.0.51a

The membership tables use Innodb, and were generated by your membership script.

For now, we decided to take the transaction out of the code. We couldn't get it to work.

I wanted to get more light on the issue but I am having a hard time finding time :P

I think it has something to do with the foreign key. Remember, I am inserting a row into the aspnet_membership table and then I am updating a Column in the Customer table which has a foreign key contraint to the UserID column in the aspnet_membership table.

The following steps should reproduce the error hopefully:

1. Add a foreign key constraint. In another table, add a reference to the UserID in the aspnet_membership table.

2. Create a transaction scope.

3. Create a new user using the System.web.security.Createuser()

4. Update a row in the table which has the foreign key constraint. And reference the UserID that was returned from the Createuser function.

I think that should repeat the error. I'll think it over and post back with more detail if I get time.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 22 Oct 2010 15:10

We have reproduced the issue. I will notify you about the results of our investigation as soon as possible.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 26 Oct 2010 15:47

This is a designed behaviour: in scope of distributed transaction the second connection is waiting on commit of changes on the first connection untill command timeout expires. There will be a workaround starting from the next build (I will post here when it is available): we have implemented the Transaction Scope Local connection string parameter. All connections with the same connection string (which also includes the mentioned parameter) in scope of TransactionScope will be indeed a single connection in internal implementation and all changes on "one connection" will be visible for "all connections".

KW
Posts: 135
Joined: Tue 19 Feb 2008 19:12

Post by KW » Tue 16 Nov 2010 20:46

Shalex wrote:This is a designed behaviour: in scope of distributed transaction the second connection is waiting on commit of changes on the first connection untill command timeout expires. There will be a workaround starting from the next build (I will post here when it is available): we have implemented the Transaction Scope Local connection string parameter. All connections with the same connection string (which also includes the mentioned parameter) in scope of TransactionScope will be indeed a single connection in internal implementation and all changes on "one connection" will be visible for "all connections".
Thanks. I have been busy on a project and haven't been back here for awhile. Looking forward to the update and also looking forward to the new changes to the entity model. Keep up the good work.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 01 Dec 2010 17:03

The Transaction Scope Local connection string parameter is implemented in dotConnect for MySQL v 6.00.58 that is available for download now.

Post Reply