Re: Membership Provider - Updating the same row cause deadlock
Posted by: Fernando Gonzalez
Date: December 03, 2012 01:19PM

Hi,

Which Connector/NET version are you using?

Now, if I understand your problem description, you have two threads each updating with the sequence:
1. ValidateUser()
2. GetUser()

since both ValidateUser & GetUser update tables membership & users, a possible lock scenario would be as follows:

- Thread1 runs Verify user, query "UPDATE my_aspnet_membership m, my_aspnet_users u SET u.lastActivityDate = '2012-11-29 18:35:46.380', m.LastActivityDate='2012-11-29 18:35:46.380' WHERE m.userId=1 AND u.id=1 "
Internally mysql acquires lock for membership and before acquiring lock for user table thread 2 executes.
- Thread2, which runs inside a transaction executes "UPDATE my_aspnet_users SET lastActivityDate = '2012-11-29 18:35:46.384' WHERE id=1", then tries
"UPDATE my_aspnet_membership SET LastActivityDate='2012-11-29 18:35:46.384' WHERE userId=1"
But locks on it since thread1 acquired membership table row lock ago, however since thread2 has not finished its transaction cannot release lock in user table from previously succcessfully finished query "UPDATE my_aspnet_users SET lastActivityDate = '2012-11-29 18:35:46.384' WHERE id=1".
- So thread2 is waiting for Thread1 to release the lock on membership table & thread1 is waiting for Thread2 to release lock on users table.
- Since none of them can continue, the lock happens.

Some recommendations:
- Use Innodb instead of MyIsam, locking occurs in MyIsam at the table level while in Innodb occurs at the row level, thus locking issues are exacerbated in MyIsam.
- Using C# sync logic to properly serialize access, with something like
lock( typeof( <some class visible to both threads) )
{
ValidateUser();
GetUser();
}
in both threads.
- When the locking occurs you can also look at show processlist to see the offending queries.
- If you are using Innodb you can also see its transaction status with "show innodb status";

I think the C# sync fix should suffice (along with using Innodb).
Otherwise, let me know.

Options: ReplyQuote


Subject
Written By
Posted
Re: Membership Provider - Updating the same row cause deadlock
December 03, 2012 01:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.