none
Merge for Insert and Update causing deadlock

    Question

  • I have a merge statement that inserts or updates from a record set of around 10,000 records. The problem is that the same table is getting hit with another merge statment from another job in exactly the same way. But the rows are different, so it cant be a rowlevel lock.

    Hitting the same table at the same time from two different jobs and doing an insert or update in this table is cauing the deadlock on that table. How can I work on getting this resolved. Pls help.

    Thanks

    -Sarah

     

    Wednesday, November 09, 2011 6:56 PM

All replies

  • Really need help with this, ANYONE??
    Thursday, November 10, 2011 9:08 PM
  • I think you should change the job schedule so that they dont override.
    Thursday, November 10, 2011 9:19 PM
  • I have a feeling that table level locks are happening on the table when the merge statement is called.

    Can I use table hints to do update and Inserts on the table. Since the rows are different each time Merge is called. I dont want to do dirty reads on the table.

     

    Pls help with what kind of table level locking hints I can use.

    Does the merge statement locks the table?

    Friday, November 11, 2011 12:03 AM
  • Hi Sarah,

    Firstly, please enable SQL Server Profiler to trace the process when doing the merge so as to understand the root cause. Additionally, this article http://weblogs.sqlteam.com/mladenp/archive/2007/08/03/60277.aspx might be helpful to resolve the problem.

    thanks,
    Jerry

    Saturday, November 12, 2011 6:51 AM
    Moderator
  • Thanks much Jerry

    I looked up that link, looks like page level locks are happening because lots of processes are hitting the same code but are working on different rows

    Is there a way to avoid page level locks with merge and just have the row level locks. If only row level is enabled then the data can still be read (Select) and other rows can be updated too with the other processes.

    I am just trying to see how can I avoid these dead locks, may be have the transaction wait or something like that.

     

    Thanks much, look forward to your help on getting this resolved.

    Thanks

    -Sarah

    Monday, November 14, 2011 9:29 PM
  • So here it is

    SPID DatabaseName              LockedObjectName                     LockedObjectId                       LockedResource              LockType Transaction Name
    75 MeterData                       HourlyAggregatItemInfo  930102354                     PAGE                                IS              Merge
    75 MeterData                        HourlyAggregate         1602104748                      PAGE                                   S               Merge

    Monday, November 14, 2011 10:06 PM
  • It is extremely helpful posting the deadlock graph and table schema, to be able to analyze the problem.

    check this articles / blog posts about how to get deadlock information.

    http://blogs.msdn.com/b/bartd/archive/tags/sql+deadlocks/

    Retrieving Deadlock Graphs with SQL Server 2008 Extended Events
    http://www.sqlservercentral.com/articles/deadlock/65658/

     


    AMB

    Some guidelines for posting questions...

    Wednesday, November 16, 2011 7:49 PM
  • Fundamentally, I would suggest you to check the MERGE statement that you wrote. Ensure that you have appropriate filter conditions. Try to have appropriate indices so that the data access is faster and then lock duration is reduced. Have transactions implemented only when they are supposed to be there. As rightly mentioned by Hunchback, check the deadlock graph to understand which process is creating the deadlock scenario on top of which object and then try to address that piece of code. IT is pretty much required for you to figure out what kind of locks are happening on the tables and then see how to reduce the locking duration.

    Regards,


    Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.
    Monday, November 21, 2011 10:17 AM