How do i fail when DELETE doesnt DELETE?

Question

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

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

Answer 1

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.

Answer 2

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

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.

Answer 3

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.