May 2, 2012 at 10:48 pm
Hello all,
This is my first post so please tolerate me for all my silly mistakes.
I am facing issues while troubleshooting Deadlock issues, we are getting data from some source using web services and putting it up in a Queue. This data is then feed into SQL server database tables using window services created on the server. We primarily used only one service but later increased them to 8 services that run all at the same time and execute same piece of code. Here, in the code we are trying to insert data in to 16 different tables. That means all 8 services try to insert data in same 16 tables all at the same time. Now, during this we are facing deadlocks. We have run SQL profiler with template: TSQL_Locks and found that there are deadlocks while insertions. I am pasting the deadlock details here:
Deadlock graph <deadlock-list>
<deadlock victim="process559ae08">
<process-list>
<process id="process559ae08" taskpriority="0" logused="0" waitresource="OBJECT: 8:740197687:9 " waittime="11030" ownerId="1864028770" transactionname="user_transaction" lasttranstarted="2012-04-24T07:31:00.043" XDES="0x159dee3b0" lockMode="X" schedulerid="14" kpid="19016" status="suspended" spid="130" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-24T07:31:00.100" lastbatchcompleted="2012-04-24T07:31:00.043" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="4828" loginname="sqluser" isolationlevel="read committed (2)" xactid="1864028770" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="760" sqlhandle="0x02000000758b67241f8bad4a6c289d3a8cbdb950255d73cb">
INSERT INTO [ABC]([BatchRecordID],[DateOfBirth],[Disability],[Email],[LEACode],[SITSLastName],[Country_Birth],[FeeStatus],[LEAName],[Nationality],[BeenInCareValue],[ParentsHigherEducationCode],[BeenInCareCode],[ParentsHigherEducationValue],[UCASID],[Gender],[HomeEmail],[SITSStudentNumber],[Title],[SITSFirstName],[StudentNo],[Country]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22) </frame>
<frame procname="adhoc" line="1" stmtend="1176" sqlhandle="0x0200000058ff67308d69296547143dae303a7e80b9dd8617">
INSERT INTO ABC (BatchRecordID , DateOfBirth, Disability, Email, LEACode, SITSLastName, Country_Birth, FeeStatus, LEAName, Nationality, BeenInCareValue, ParentsHigherEducationCode, BeenInCareCode, ParentsHigherEducationValue, UCASID, Gender, HomeEmail, SITSStudentNumber, Title, SITSFirstName, StudentNo, Country) VALUES(--------------------------Values cleared out----------) </frame>
</executionStack>
<inputbuf>
INSERT INTO ABC(BatchRecordID , DateOfBirth, Disability, Email, LEACode, SITSLastName, Country_Birth, FeeStatus, LEAName, Nationality, BeenInCareValue, ParentsHigherEducationCode, BeenInCareCode, ParentsHigherEducationValue, UCASID, Gender, HomeEmail, SITSStudentNumber, Title, SITSFirstName, StudentNo, Country) VALUES(--------------------------Values cleared out----------)
INSERT INTO DEF(BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, APFSeqNo, Decision, SeqNum) VALUES(--------------------------Values cleared out----------)
INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Decision) VALUES(--------------------------Values cleared out----------)
</inputbuf>
</process>
<process id="process4875048" taskpriority="0" logused="0" waitresource="OBJECT: 8:740197687:0 " waittime="11029" ownerId="1864028790" transactionname="user_transaction" lasttranstarted="2012-04-24T07:31:00.060" XDES="0x8cca09950" lockMode="X" schedulerid="10" kpid="9492" status="suspended" spid="125" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-24T07:31:00.120" lastbatchcompleted="2012-04-24T07:31:00.060" clientapp=".Net SqlClient Data Provider" hostname="" hostpid="8272" loginname="sqluser" isolationlevel="read committed (2)" xactid="1864028790" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="580" sqlhandle="0x020000005b791e049c815fb4512ce3b249070e0b3f6635df">
INSERT INTO [ABC]([BatchRecordID],[UCASID],[UKEntryDate],[FeeStatus],[SITSStudentNumber],[Title],[SITSLastName],[Email],[SITSFirstName],[Country_Birth],[Nationality],[Country],[Gender],[HomeEmail],[StudentNo],[Disability],[DateOfBirth]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17) </frame>
<frame procname="adhoc" line="1" stmtend="898" sqlhandle="0x02000000d7b55c16df84264b7965df66ab75e47cda870abb">
INSERT INTO ABC(BatchRecordID , UCASID, UKEntryDate, FeeStatus, SITSStudentNumber, Title, SITSLastName, Email, SITSFirstName, Country_Birth, Nationality, Country, Gender, HomeEmail, StudentNo, Disability, DateOfBirth) VALUES(--------------------------Values cleared out----------)
</frame>
</executionStack>
<inputbuf>
INSERT INTO ABC(BatchRecordID , UCASID, UKEntryDate, FeeStatus, SITSStudentNumber, Title, SITSLastName, Email, SITSFirstName, Country_Birth, Nationality, Country, Gender, HomeEmail, StudentNo, Disability, DateOfBirth) VALUES(--------------------------Values cleared out----------)
INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Decision) VALUES(--------------------------Values cleared out---------)
INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Response, Decision, OfferSummary) VALUES(--------------------------Values cleared out---------)
INSERT INTO DEF (BatchRecordID , UCASCourseCode, InstituteCode, InstituteName, Decision </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="9" objid="740197687" subresource="FULL" dbid="8" objectname="A.dbo.ABC" id="lock246aa1f80" mode="IX" associatedObjectId="740197687">
<owner-list>
<owner id="process4875048" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process559ae08" mode="X" requestType="wait"/>
</waiter-list>
</objectlock>
<objectlock lockPartition="0" objid="740197687" subresource="FULL" dbid="8" objectname="A.dbo.ABC" id="locke6668a300" mode="X" associatedObjectId="740197687">
<owner-list>
<owner id="process559ae08" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process4875048" mode="X" requestType="wait"/>
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
</deadlock-list>
The deadlock occurs on table ABC. This table has a primary key on ApplicantID and thus has a clustered index on this, Applicantid is the identity column. We have a non clustered index on BatchRecordID that is non unique. This is created as after insert we need to perform some update and select statements.
After insertions, we have a few(2-3) update statements on tables ABC and another table GHI. We then will perform select on these 16 tables and move data to our final tables i.e. will perform insert again. All this need to be either get successful and reach to final tables or get rolled back.
We have tried to drop non clustered index here that resulted in successful insertions but the deadlocks started occurring at the next level i.e. at update in ABC table as there is now only clustered index(we deleted the non clustered index as mentioned earlier).
We cannot reduce isolation level also. We may not be able to use non overlapping as we are using 8 processes to run the same code.We are doubtful to DISABLE LOCK ESCALATION here as we fear from memory issue on the server. Is memory a concern if we are disabling lock escalation?
Please suggest how to avoid deadlocks and maintain concurrency. We may want to increase concurrency to 16 services/streams if we are able to resolve deadlock situation but at this point we would like to restrict ourselves to 8 services only.
Thanks a lot.
May 2, 2012 at 11:16 pm
Hi
I would like to answer your question fully, but I am afraid that despite your exemplary effort to describe the problem I still do not have enough info.
I would like to know whether there is a risk that two front ends will try to insert the same info into one of the tables at the same time.
If, for example, one applicant can talk to one front end at a time and the ID is part of the key, you can eliminate locks by WITH (NOLOCK). (That may be the scenario of, say, customer service, where is it unlikely that two reps of the same customer will try to open support cases at the same time.)
If there is a possibility that one applicant (several reps of a major company, if that is possible) can try to insert with the same primary key, you may use the MS Queue or your queuing mechanism to assure that updates are done one at a time but with maximum efficiency. For that, you might use WITH (TABLOCK), betting that the performace gain of locking the entire large (?) table with sparse logging will give you a better throughput.
This mechanism is one of the pillars of the architecture of Windows Azure with front ends (Web machines) talking to the back ends (SQL Azure machines) via an internal queue.
That, however, is just a general outline. If you can provide more detail on how does the data pushed in by the front ends relate to the tables, I will be glad to look into it and help.
I am on PDT (GMT -8), so I am heading to bed,but I will be back online in about 8 hours.
May 8, 2012 at 12:03 am
Thanks guys,
I found that we had row locks and page level locking disabled in our non clustered indexes disabled. Once i changed the options ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON. Setting this resolved the deadlock issue. I have increased the no of processes/queue listeners and now performance has also improved.
Please refer:
http://stackoverflow.com/questions/8352859/sql-server-deadlock-on-the-same-table
http://stackoverflow.com/questions/8387024/sql-server-insert-one-row-locks-whole-table
Cheers!!!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply