问EntityFramework6事务中的MySQL更改值

EN

DROP PROCEDURE IF EXISTS UsePassword;

DELIMITER //
CREATE PROCEDURE UsePassword (QuizId INT(11), PasswordText VARCHAR(25))
BEGIN

  /* Get current state of password */
    SELECT RemainingUses, UnlimitedUses, Deleted INTO @RemainingUses, @UnlimitedUses, @Deleted FROM QuizPassword q WHERE q.QuizId = QuizId AND `Password` = PasswordText AND Deleted = 0 LIMIT 0,1 FOR UPDATE;

    IF FOUND_ROWS() = 0 OR @Deleted = 1 THEN

        /* Valid password not found for quiz */
        SET @Success = 0;   

    ELSEIF @UnlimitedUses = 1 THEN

        UPDATE QuizPassword SET UseCount = UseCount + 1 WHERE QuizId = QuizId AND `Password` = PasswordText;
        SET @Success = ROW_COUNT();

    ELSEIF @RemainingUses > 0 AND @UnlimitedUses = 0 THEN

        UPDATE QuizPassword SET UseCount = UseCount + 1, RemainingUses = RemainingUses - 1 WHERE QuizId = QuizId AND `Password` = PasswordText;
        SET @Success = ROW_COUNT();

    ELSE

        SET @Success = 0;

    END IF;

  /* Return rows changed rows */
  SELECT @Success AS Success;

END //
DELIMITER;

// Class to hold return values from stored procedure
public class UsePasswordResult
{
    public int Success { get; set; }

    // could have more fields...
}

using (var transaction = this.Context.Database.BeginTransaction())
{
    try
    {

        if (quiz.PasswordRequiredToTakeQuiz())
        {
            // Attempt to use password
            UsePasswordResult result = this.Context.Database.SqlQuery<UsePasswordResult>("CALL UsePassword({0}, {1})", quiz.Id, model.QuizPassword).FirstOrDefault();

            // Check the result of the password use
            if (result.Success != 1)
            {
                // Failed to use the password
                ViewData.ModelState.AddModelError("QuizPassword", "Sorry the password you provided has expired or is not valid for this quiz");
            }
        }

        // Is model state still valid after password checks?
        if (ModelState.IsValid)
        {
            // Do stuff
        }

        transaction.Commit();
    }
    catch(Exception)
    {
        transaction.Rollback();
    }
    finally
    {
        transaction.Dispose();
    }
}

BEGIN TRANSACTION

SELECT usecount, unlimiteduses FROM quizpassword WITH (UPDLOCK, HOLDLOCK) WHERE id = x;

// check usecount here

// only do this if unlimitedUses == false
UPDATE quizpassword SET usecount = usecount + 1 WHERE id = x;

UPDATE quizpassword SET remaininguses = remaininguses -1 WHERE id = x;


COMMIT TRANSACTION // (lock is released)