none
Deadlock issue when running multiple threads...

    Question

  • I've been receiving this error randomly during some unit tests:

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

    I did some Googling and it seems that this issue crops up when running simultaneous transactions in SQL, but to my knowledge, the entity framework doesn't even use transactions when calling SaveChanges, right?

    I've narrowed down the issue in my code... I have an asynchronous WCF endpoint that I'm saving data through... The funny part is, I'm not even saving the same data, but I suspect the same rows are being used in relationships being saved.  This is my debug output (you can see the two threads (14 and 22)):

    2009-04-03 16:21:02,063 [22] DEBUG [DataService] Saving 1 PublicationSubscriptions for Publication dfe9ec10-e79a-4eb2-b259-e53292713caa
    2009-04-03 16:21:02,063 [14] DEBUG [DataService] Saving 1 PublicationSubscriptions for Publication e5b6af82-2650-43f2-861c-a95888778320
    2009-04-03 16:21:02,329 [22] DEBUG [DataService]  - Saving PublicationSubscription: 3c3cbce6-0baa-48d5-9be1-2a8f006f3b43 (dfe9ec10-e79a-4eb2-b259-e53292713caa)
    2009-04-03 16:21:02,360 [14] DEBUG [DataService]  - Saving PublicationSubscription: 38f317b6-6afd-437e-900b-2238c35d4421 (e5b6af82-2650-43f2-861c-a95888778320)

    I profiled it as well, and I saw both inserts show up, but only the second one (38f317b6*) actually made it into the database:

    RPC:Completed    exec sp_executesql N'insert [dbo].[PublicationSubscription]([PublicationSubscriptionUUID], [PublicationID], [SubscriptionID])
    values (@0, @1, @2)
    select [PublicationSubscriptionID]
    from [dbo].[PublicationSubscription]
    where @@ROWCOUNT > 0 and [PublicationSubscriptionUUID] = @0',N'@0 uniqueidentifier,@1 int,@2 int',@0='3C3CBCE6-0BAA-48D5-9BE1-2A8F006F3B43',@1=2510,@2=695    .Net SqlClient Data Provider    oliverlw    INNOVASI\oliverlw    0    9    0    4990    4348    57    2009-04-03 16:21:02.373    2009-04-03 16:21:07.373   

    RPC:Completed    exec sp_executesql N'insert [dbo].[PublicationSubscription]([PublicationSubscriptionUUID], [PublicationID], [SubscriptionID])
    values (@0, @1, @2)
    select [PublicationSubscriptionID]
    from [dbo].[PublicationSubscription]
    where @@ROWCOUNT > 0 and [PublicationSubscriptionUUID] = @0',N'@0 uniqueidentifier,@1 int,@2 int',@0='38F317B6-6AFD-437E-900B-2238C35D4421',@1=2511,@2=695    .Net SqlClient Data Provider    oliverlw    INNOVASI\oliverlw    0    13    0    4990    4348    58    2009-04-03 16:21:02.373    2009-04-03 16:21:07.373   

    ....

    A simple fix is to change the WCF endpoint to use InstanceContextMode.Single, but this will only slow the entire enterprise down.

    Do I have any other options?

    Thanks in advance!
    • Edited by langdonx Friday, April 03, 2009 8:28 PM clarity
    Friday, April 03, 2009 8:26 PM

Answers

  • The only real solution is to use pessimistic update. Unfortunately the Entity Framework does not support it yet. You need to add (UPDLOCK) hint to the generated SQL select command. There is an easy workaround. Create new function in SSDL file and write the T-SQL command directly to CommandText element, see http://msdn.microsoft.com/en-us/library/dd296754.aspx  The function return can be mapped to an Entity object(s).
    • Marked as answer by langdonx Thursday, April 09, 2009 5:37 PM
    Saturday, April 04, 2009 6:50 PM

All replies

  • The only real solution is to use pessimistic update. Unfortunately the Entity Framework does not support it yet. You need to add (UPDLOCK) hint to the generated SQL select command. There is an easy workaround. Create new function in SSDL file and write the T-SQL command directly to CommandText element, see http://msdn.microsoft.com/en-us/library/dd296754.aspx  The function return can be mapped to an Entity object(s).
    • Marked as answer by langdonx Thursday, April 09, 2009 5:37 PM
    Saturday, April 04, 2009 6:50 PM
  • Thanks, that did the trick!

    It's really strange though that a deadlock was even being created.  I noticed the entity framework uses sp_executesql() for all its queries, and does an insert followed by a select (I'm guessing to ensure that the insert was successful).

    Does sp_executesql use transactions?  I tried to replicate the deadlock using query analyzer (two separate windows with sp_excutesql('insert; select') running in two separate windows, but I couldn't get it to happen.

    Thanks again.
    Thursday, April 09, 2009 5:28 PM
  • Hi *,

    the select is there to get server generated columns. If the insert fails, the DB will throw error.

    The sp_executesql is, of course, running inside transaction.
    Jiri {x2} Cincura
    Thursday, April 09, 2009 5:32 PM
  • Thanks Jiri!
    Thursday, April 09, 2009 5:37 PM