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.
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?
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 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.
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.
Retrieving Deadlock Graphs with SQL Server 2008 Extended Events
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.
Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.