MysqlMembership Membership.ValidateUser Deadlock issue when under load

Question

I have a WCF Webservice with a custom httpModule which I use to do UserName/Password authentication against the MysqlMembership.

public void Init(HttpApplication application)
    {
        application.AuthenticateRequest += new EventHandler(this.OnAuthenticateRequest);
        application.EndRequest += new EventHandler(this.OnEndRequest);
    }

    public void OnAuthenticateRequest(object source, EventArgs eventArgs)
    {
            ...
            ...

            if (Membership.ValidateUser(username, password))
            {

When I started loadtesting my service, I came across a perplexing problem. Requests randomly fail the Membership.ValidateUser call. I am using the same username/login for each thread in my load tester. Here is the error:

  [MySqlException (0x80004005): Deadlock found when trying to get lock; try restarting transaction]
  MySql.Data.MySqlClient.MySqlStream.ReadPacket() +506
  MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId) +450
  MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) +131
  MySql.Data.MySqlClient.MySqlDataReader.NextResult() +1216
  MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) +2191
  MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() +27
  MySql.Web.Security.MySQLMembershipProvider.ValidateUser(String username, String password) +1092

  [ProviderException: An exception occurred. Please check the Event Log.]
  MySql.Web.Security.MySQLMembershipProvider.ValidateUser(String username, String password) +1481
  MyApplication.UserAuthenticator.UserNameAuthenticator.OnAuthenticateRequest(Object source, EventArgs eventArgs) in C:\Develop\MyProject\MyProject.UserAuthenticator\UserNameAuthenticator.cs:123
  System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +80
  System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)      +270

Any ideas?

Thanks, AFrieze

Answer 1

The validate user locks the row in order to increment the invalid password attempts, if the password turns out to be invalid:

If an incorrect password is supplied to the ValidateUser method, the internal counter that tracks invalid password attempts is incremented by one. This can result in the user being locked out and unable to log on until the lock status is cleared by a call to the UnlockUser method. If the correct password is supplied and the user is not currently locked out, then the internal counters that track invalid password and password-answer attempts are reset to zero. For more information, see the MaxInvalidPasswordAttempts and PasswordAttemptWindow properties.

http://msdn.microsoft.com/en-us/library/system.web.security.sqlmembershipprovider.validateuser.aspx

Since you are using the same user for all request you can get a deadlock.