SQL Server Deadlock resolution procedure

A live report of a SQL deadlock, then turned on 1222 tracking:

DBCC TRACEON (1222,-1)

After a period of time to copy the error file to find relevant information, more useful excerpts come out as follows:

Statement one:

Select Study_iuid,station_aet,modality,accession_no,patient_fk,item_attrs,start_datetime from  worklist W with ( Readpast), Mwl_item m  where W.tag_study_instance_uid=m.study_iuid and  isread= ' 1 ' and (Tag_sps_status is null or tag_sps_status= ' scheduled ' or tag_sps_status= ' discontinued ') and  tag_sps_start_date between @P0 and @P1   and Not  exists (select 1 from MPPs b where b.study_iuid=m.study_iuid)    

Statement two:

INSERT into Mwl_item (Created_time, Updated_time, sps_id, Start_datetime, Station_aet, Station_Name, modality, perf_ Physician, PERF_PHYS_FN_SX, PERF_PHYS_GN_SX, Perf_phys_i_name, Perf_phys_p_name, req_proc_id, Accession_no, Study_ Iuid, Item_attrs, Sps_status, PATIENT_FK) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13 , @P14, @P15, @P16, @P17);  

The associated deadlock resources are as follows:

Resource-list pagelock fileid=1 pageid=6996 dbid=8 objectname=worklist.dbo.mwl_item id=lock19825c100 Mode=IX associatedobjectid=72057594039697408  owner-list   owner id=process984d048 Mode=ix  waiter-list   Waiter id=process60e9708 mode=s requesttype=wait pagelock fileid=1 pageid=11086 dbid=8 objectname=Worklist.dbo.mwl_ Item id=lock1b087b100 mode=s associatedobjectid=72057594039697408  owner-list   owner id=process60e9708 Mode=S  waiter-list   Waiter id=process984d048 Mode=ix requesttype=wait

It is obvious to see that the SELECT statement and the INSERT statement produced a deadlock, competing resources of 6996 and 11086 of the two page.

The following optimizations are therefore determined based on the principle of the deadlock:

1. Optimize the SELECT statement so that it completes as soon as possible to reduce the deadlock frequency.

2. Use the NOLOCK option on the SELECT statement to avoid deadlock problems.

3. Inform development to optimize the execution order of related code to avoid deadlock problem.

Finally, the SELECT statement was optimized, and the other two articles were given to development for modification.

　

SQL Server Deadlock resolution procedure