Re: Membership Provider - Updating the same row cause deadlock
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.
Subject
Written By
Posted
December 02, 2012 05:20AM
Re: Membership Provider - Updating the same row cause deadlock
December 03, 2012 01:19PM
December 05, 2012 01:19AM
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.