Sql-server – Can a SELECT with SELECT Subquery cause deadlock with Update query in SQL Server

cdeadlocksql server

I am using entity framework with table per type. I have a table called ins_GameAppInstances that has a discriminator column for the type of object I am returning. There is another table that has some settings that have a foreign key to ins_GameAppInstances called ins_DefaultBankAccountInstances.

When I have high throughput to the database a deadlock is happening between a select with a select subquery and an update to the same ins_GameAppInstance table. The sql server is choosing to kill the select of course as a victim.

I am wracking my brain to this point. If you look at the page locks, there is Page ID 6122 and then Page ID 4537. Could this be happening because the first select locks on S, the update gets inbetween the subquery select and locks on IX and then the subquery locks a later page? This causes the deadlock? It's really the subquery select that is causing this mess?

I know that the update is not updating anything to do with the select query results. Would it be safe to just allow READ_COMMITTED in SQL Server. I don't really want to do that but I also don't want to change the way my domain logic works because of Entity Framework and entity data mapping.

UPDATE:
I was thinking that as a last resort I could just deal with it by grabbing in a try catch sql exception. Throw the exception up the stack. When I run my integration test with a console app just smashing the db there is significant wait though for the deadlock to happen.

catch (SqlException ex)
        {
            if (ex.Number == 1205)
            {
                if (LogManager.Enabled)
                    LogManager.GetLogger(GetType()).Debug("Deadlock");
            }
            else
                throw;
        }

Here is the ins_GameAppInstance table

GameAppInstances

Here is the DefaultBankAccountInstance table. The GameAppInstanceID is the foreign key to ins_GameAppInstances.

DefaultBankAccountInstanceTable

Here is the deadlock that is happening. Request and owner S on the left and IX Request and owner on the right.

Deadlock

Here is the select and select subquery that entity framework is building:

exec sp_executesql N'SELECT 
[Join1].[CurrentWeek] AS [CurrentWeek], 
[Join1].[Discriminator] AS [Discriminator], 
[Join1].[ID1] AS [ID], 
[Join1].[Name] AS [Name], 
[Join1].[WeekHasStarted] AS [WeekHasStarted], 
[Join1].[Created1] AS [Created], 
[Join1].[GameAppID] AS [GameAppID], 
[Join1].[GameInstanceID] AS [GameInstanceID], 
[Join1].[ID2] AS [ID1]
FROM  [dbo].[ins_GameAppInstances] AS [Extent1]
INNER JOIN  (SELECT [Extent2].[ID] AS [ID1], [Extent2].[Name] AS [Name], [Extent2].[CurrentWeek] AS [CurrentWeek], [Extent2].[WeekHasStarted] AS [WeekHasStarted], [Extent2].[Created] AS [Created1], [Extent2].[Discriminator] AS [Discriminator], [Extent2].[GameAppID] AS [GameAppID], [Extent2].[GameInstanceID] AS [GameInstanceID], [Extent3].[ID] AS [ID2]
    FROM  [dbo].[ins_GameAppInstances] AS [Extent2]
    LEFT OUTER JOIN [dbo].[ins_DefaultBankAccountInstances] AS [Extent3] ON [Extent2].[ID] = [Extent3].[GameAppInstanceID] ) AS [Join1] ON [Extent1].[ID] = [Join1].[ID1]
WHERE ([Extent1].[GameInstanceID] = @EntityKeyValue1) AND ([Join1].[Discriminator] IN (N''BankingAppInstance'',N''BillsAppInstance'',N''CashboxAppInstance'',N''CreditCardsAppInstance'',N''EmailsAppInstance'',N''ExpensesAppInstance'',N''RandomEventsAppInstance'',N''TextMessagesAppInstance'',N''GameAppInstance''))',N'@EntityKeyValue1 uniqueidentifier',@EntityKeyValue1='55E2A02B-73AF-4CC9-BC09-7BD46473CF4F'

Here is the update that is being sent at the same time:

    exec sp_executesql N'UPDATE [dbo].[ins_GameAppInstances]
SET [CurrentWeek] = @0, [WeekHasStarted] = @1
WHERE ([ID] = @2)
',N'@0 int,@1 bit,@2 uniqueidentifier',@0=4,@1=0,@2='FD00CB51-A8DD-4705-87E9-B1DD34322264'

UPDATE
XML DEADLOCK FILE

http://pastebin.com/mRNm0Rje

Best Answer

From my experience, when you use READ COMMITTED and the concurrency is high, deadlocks will happen. The frequency of deadlocks will grow with concurrency and length of transactions.

Reducing the transaction isolation level could improve things to the point that you will not have deadlocks due to SELECT statements, but still, there might be a chance to have deadlocks due to the UPDATE statements.

The best solution is to just assume that deadlocks are possible and will happen and to allow for a certain number of retries.