DELETE不删除时如何失败？

The problem i have is i could DELETE but then when i hit refresh and send the post data it will try to delete again. Which isnt a problem but now the second statment is a problem since it decreases when it shouldnt.

我有的问题是我可以删除但是当我点击刷新并发送帖子数据时它将尝试再次删除。这不是问题,但现在第二个问题是一个问题,因为它不应该减少。

What is a concurrent safe way to decrease but only if delete has removed an entry? note msgId is a PK so i'll either delete 0 or 1

什么是并发安全方式减少但只有删除删除条目?注意msgId是一个PK所以我要么删除0或1

public void removeMediaMsg(long userId, long msgId)
{
    using (var dbTrans = connection.BeginTransaction())
    {
        command.CommandText = "DELETE FROM user_media_subscription "
            + "WHERE msgId=@msgId AND recipientId=@recipientId;";
        command.Parameters.Add("@msgId", DbType.Int64).Value = msgId;
        command.Parameters.Add("@recipientId", DbType.Int64).Value = userId;
        command.ExecuteNonQuery();

        command.CommandText = "UPDATE user_data SET mediaMsgCount=mediaMsgCount-1 WHERE userId=@userId;";
        command.Parameters.Add("@userId", DbType.Int64).Value = userId;
        command.ExecuteNonQuery();
        dbTrans.Commit();
    }
}

3 个解决方案

#1

ExecuteNonQuery() returns the numbers of rows affected, so something like this might work

ExecuteNonQuery()返回受影响的行数,因此这样的事情可能会起作用

public void removeMediaMsg(long userId, long msgId)
{
    using (var dbTrans = connection.BeginTransaction())
    {
        command.CommandText = "DELETE FROM user_media_subscription "
            + "WHERE msgId=@msgId AND recipientId=@recipientId;";
        command.Parameters.Add("@msgId", DbType.Int64).Value = msgId;
        command.Parameters.Add("@recipientId", DbType.Int64).Value = userId;
        int affected = command.ExecuteNonQuery();
        if (affected == 1) {
            command.CommandText = "UPDATE user_data SET mediaMsgCount=mediaMsgCount-1 WHERE userId=@userId;";
            command.Parameters.Add("@userId", DbType.Int64).Value = userId;
            command.ExecuteNonQuery();
        }
        dbTrans.Commit();    
    }
}

That said, you should program your app to avoid replaying a command when refreshing. One way to do that is by using redirect or just rendering a different view after a successful remove.

也就是说,您应该对应用进行编程,以避免在刷新时重播命令。一种方法是使用重定向或仅在成功删除后呈现不同的视图。

#2

I'm guessing from the "when I hit refresh" statement that you're executing this from ASP.NET. What I've found useful is to follow the transaction with a Response.Redirect to the summary page. That way hitting refresh does not repeat the Delete command.

我从“当我点击刷新”语句中猜测你是从ASP.NET执行的。我发现有用的是使用Response.Redirect跟踪事务到摘要页面。这样点击刷新不会重复删除命令。

#3

It would be a good idea to do a select query before the delete to check it's in table. If it's not, you can just return the function and not do anything else.

在删除之前执行选择查询以在表中检查它是一个好主意。如果不是,您可以返回该功能而不做其他任何事情。

#1