Deadlock in online hour system

Question

I'm developing an online hour system for one online game. The problem is that sometimes the SQL throws an deadlock error:

40001 Transaction (Process ID 411) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The game has 5 rooms and each room is an application with their own SQL connection. The player cannot login in multiple rooms. Each application loops on online players every 10 minutes, sending the command to the SQL procedure to update their online minutes...

EXEC StoredProcedure 'PlayerName', 7

Follow the Stored Procedure code:

USE [DataBase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[StoredProcedure]
    @PlayerName varchar(10),
    @Minutes int
AS

BEGIN
    DECLARE @PlayerLogin varchar(10),
        @OnlineMinutes int

    SELECT @PlayerLogin = PlayerLogin FROM [dbo].[PlayerTable] WHERE PlayerName = @PlayerName

    UPDATE [dbo].[PlayerTable] SET OnlineMinutes = OnlineMinutes + @Minutes WHERE PlayerName = @PlayerName
    UPDATE [dbo].[AccountTable] SET OnlineMinutes = OnlineMinutes + @Minutes WHERE PlayerLogin = @PlayerLogin

    SELECT @OnlineMinutes = OnlineMinutes FROM [dbo].[AccountTable] WHERE PlayerLogin = @PlayerLogin

    IF ( @OnlineMinutes >= 60 )
    BEGIN
        UPDATE [dbo].[AccountTable] SET OnlineHours = OnlineHours + ( @OnlineMinutes / 60 ), OnlineMinutes = ( @OnlineMinutes % 60 ) WHERE PlayerLogin = @PlayerLogin
    END
END

The reason to store the online minutes in both table is because PlayerTable is used for ranking page and in the AccountTable the online hours it's used as an virtual cash to the player spend in the online hours game store.

Answer 1

I would suggest the following changes.

1) In your calling application, you keep track of @PLayerName and pass it to this procedure. You then use this to look up @PlayerLogin. You should also keep track of @PlayerName in the caller and pass both variables to this procedure, avoiding the lookup of @PlayerLogin inside the proc

2)Replace

SELECT @OnlineMinutes = OnlineMinutes FROM [dbo].[AccountTable] WHERE PlayerLogin = @PlayerLogin

IF ( @OnlineMinutes >= 60 )
BEGIN
    UPDATE [dbo].[AccountTable] SET OnlineHours = OnlineHours + ( @OnlineMinutes / 60 ),   
        OnlineMinutes = ( @OnlineMinutes % 60 ) WHERE PlayerLogin = @PlayerLogin
END

with

UPDATE [dbo].[AccountTable] SET 
    OnlineHours = OnlineHours + ( @OnlineMinutes / 60 ) 
 , OnlineMinutes = ( @OnlineMinutes % 60 )
WHERE PlayerLogin = @PlayerLogin AND OnlineMinutes >= 60

With these changes you eliminate the SELECT then UPDATE pattern on these 2 tables. This is a good thing for deadlock avoidance.

Finally, you should follow the advice of re-running the transaction. Put all three update statements inside a loop wrapped in a transaction, on error, rollback and let it try the transaction again, of course on success, you simply commit and exit the loop. You still need to make the first 2 changes as avoiding deadlocks is much better than retrying after the deadlock.

You need to place a reasonably small upper bound on the number of retry attempts (I would suggest no more than 5).