应用详情
c# web api(隐式并发)dapper for SQL execution
要求当四个用户被添加到"休息室"时,这意味着游戏准备开始了。第四个添加的用户负责启动游戏。一旦游戏开始,用户必须从"休息室"中移除。
表UserInLounge (LoungeId, UserId)注意,还有一个休息室,
var tran = connection.BeginTransaction();
connection.Execute("insert into UserInLounge(LoungeId, UserId, TimeEntered) values (@loungeId, @userId, @timeEntered)", new { loungeId, userId = user.Id, timeEntered = DateTime.UtcNow }, tran);
var userIds = connection.Query<string>("select UserId from UserInLounge where LoungeId = @loungeId", new { loungeId }, tran).ToList();
if (userIds.Count == 4)
{
connection.Execute("delete from UserInLounge where LoungeId = @loungeId and UserId IN @userIds", new { loungeId, userIds }, tran);
}
tran.Commit();
var tran = connection.BeginTransaction();
connection.Execute("insert into UserInLounge(LoungeId, UserId, TimeEntered) values (@loungeId, @userId, @timeEntered)", new { loungeId, userId = user.Id, timeEntered = DateTime.UtcNow }, tran);
var userIds = connection.Query<string>("select UserId from UserInLounge where LoungeId = @loungeId", new { loungeId }, tran).ToList();
if (userIds.Count == 4)
{
connection.Execute("delete from UserInLounge where LoungeId = @loungeId and UserId IN @userIds", new { loungeId, userIds }, tran);
}
tran.Commit();
使用多个用户运行(我使用4个用户进行测试)将导致事务中的死锁。我使用事务是因为我需要在插入之后确切地知道现在是否有四个人,如果是,则删除他们。
二尝试var tran = connection.BeginTransaction();
var userIds = connection.Query<string>("select UserId from UserInLounge where LoungeId = @loungeId", new { loungeId }, tran).ToList();
if (userIds.Count == 3)
{
connection.Execute("delete from UserInLounge where LoungeId = @loungeId and UserId IN @userIds", new { loungeId, userIds }, tran);
tran.Commit();
}
else
{
connection.Execute("insert into UserInLounge(LoungeId, UserId, TimeEntered) values (@loungeId, @userId, @timeEntered)", new { loungeId, userId = user.Id, timeEntered = DateTime.UtcNow }, tran);
tran.Commit();
Log.InfoFormat("{0} added to Lounge: {1}; Now {2} users", user.UserName, ret.Name, userIds.Count);
}
第二次尝试时,if语句永远不会为真。下面是日志输出-
|INFO |FiveHundred.Web.Stores.DapperLoungeStore - user3@fivehundred.com added to Lounge: Main; Now 0 users
|INFO |FiveHundred.Web.Stores.DapperLoungeStore - user2@fivehundred.com added to Lounge: Main; Now 0 users
|INFO |FiveHundred.Web.Stores.DapperLoungeStore - user1@fivehundred.com added to Lounge: Main; Now 0 users
|INFO |FiveHundred.Web.Stores.DapperLoungeStore - user4@fivehundred.com added to Lounge: Main; Now 0 users
如果我按建议添加HOLDLOCK,我会得到一个死锁
"select UserId from UserInLounge with (holdlock) where LoungeId = @loungeId"
我从这里得到了解决方案- http://michaeljswart.com/2011/09/mythbusting-concurrent-updateinsert-solutions/
和上面的基本相同,只是加了-
var tran = connection.BeginTransaction(IsolationLevel.Serializable);
var userIds = connection.Query<string>("select UserId from UserInLounge with (updlock) where LoungeId = @loungeId", new { loungeId }, tran).ToList();