Entity Framework 6 MySQL在事务中更改值
本文关键字:事务 Framework MySQL Entity | 更新日期: 2023-09-27 18:25:59
我想将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();
}
}
事件转折:
- 最初,数据库中的UseCount=
0
- 我将代码运行到
SaveChanges()
之前 - 我进入数据库,将UseCount更改为
5
- 我允许调用SaveChanges()(如果不被阻止就不可能)
- 数据库中的UseCount值将为
1
通常情况下,我会使用SELECT FOR UPDATE
来临时锁定记录,但我最初使用的是PHP+MySQL。
我读到锁是不可能的,所以我想知道如何实现这一点。
这很重要,因为我不希望人们使用密码的次数超过设定的次数!如果有人可以在平均时间内更改该值,则不能保证使用次数正确。
一种解决方案是使用纯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)
我创建了一个存储过程,该过程返回了一个SELECT
语句,该语句是我想要返回的值,即Success
int
。
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();
}
}
从存储过程返回的值必须与它将作为结果生成的类中的值名称完全相同。
因为我在事务using
语句中调用procedure
,所以该语句会锁定记录,因为我已将其选择为SELECT... FOR UPDATE
,直到事务。Commit()/Rollback()/Dispose()在代码中被调用。。。从而防止任何人试图在其他人使用密码。