Entity Framework 6 MySQL изменить значение в транзакции

Question

Я хочу добавить 1 к значению в моей базе данных в рамках транзакции. Я хочу убедиться, что запись обновлена ​​должным образом и не была изменена кем-то еще в течение этого времени.

У меня есть следующий код, который, я думал, будет работать, но я все еще могу сделать паузу во время отладки, изменить запись в базе данных на что-то другое, и тогда это станет противоречивым.

Вот мой код:

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

        if (quiz.PasswordRequiredToTakeQuiz())
        {
            // Check password exists for quiz
            bool passwordIsValid = quiz.QuizPasswords.Any(x => x.Password.ToLower() == model.QuizPassword.ToLower() && !x.Deleted);
            QuizPassword quizPassword = quiz.QuizPasswords.Where(x => x.Password.ToLower() == model.QuizPassword.ToLower() && !x.Deleted).First();
            string passwordError = "Sorry the password you provided has expired or is not valid for this quiz";

            if (!passwordIsValid)
            {
                ViewData.ModelState.AddModelError("QuizPassword", passwordError);
            }
            else
            {
                // Password is valid for use with this quiz, but can it be used?
                if (quizPassword.RemainingUses < 1 && quizPassword.UnlimitedUses != true)
                {
                    // Password cannot be used
                    ViewData.ModelState.AddModelError("QuizPassword", passwordError);
                }
                else
                {
                    // Password CAN be used
                    if (!quizPassword.UnlimitedUses)
                    {
                        quizPassword.RemainingUses--;
                    }
                    // Increase use count
                    quizPassword.UseCount++;

                    this.Context.EntitySet<QuizPassword>().Attach(quizPassword);
                    this.Context.Entry(quizPassword).State = EntityState.Modified;

                    // I can change the record UseCount value in the database at this point
                    // then when it saves, it becomes inconsistent with other's use of
                    // the password

                    this.Context.SaveChanges();
                }
            }
        }

        // Commit the changes
        transaction.Commit();

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

Поворот событий:

1. Изначально UseCount = 0 в базе данных
2. Я запускаю код до SaveChanges()
3. Я захожу в базу данных и меняю UseCount на 5
4. Я разрешаю вызывать SaveChanges() (не должно быть невозможно без блокировки)
5. Значение UseCount в базе данных будет 1,

Обычно я достиг бы этого, используя SELECT FOR UPDATE временно заблокировать запись, но я изначально использовал PHP + MySQL.

Я читал, что блокировка невозможна, поэтому мне интересно, как этого добиться.

Это важно, потому что я не хочу, чтобы люди могли использовать пароль более определенного количества раз! Если кто-то может изменить значение за это время, это не гарантирует правильное количество использований.

Answer 1

Я создал хранимую процедуру, которая вернула SELECT заявление о значении, которое я хотел бы вернуть, который является Successint,

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();
    }
}

Значения, которые вы возвращаете из хранимой процедуры, должны быть точно такими же, как и имена в классе, который она собирается создать в результате.

Потому что я звоню procedure в рамках моей транзакции using оператор, оператор блокирует запись, потому что я выбрал ее как SELECT... FOR UPDATE до тех пор, пока в коде не будет вызвана транзакция.Commit()/Rollback()/Dispose()... что не позволяет никому пытаться использовать пароль, пока кто-либо еще.

Answer 2

Одним из решений было бы сделать это с простым sql (ado.net) и пессимистической блокировкой.

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)