A deadlock caused by a query statement

Program error Log a large number of reported deadlock errors, go to the database error log view There are indeed many deadlocks (should be performed when the DB instance starts with DBCC TRACEON (1222,-1) to turn on deadlock tracing):

04/29/2016 14:07:51,spid33s,δ?,waiter id=process71da6bb88 mode=ix requesttype=wait04/29/2016 14:07:51,spid33s,δ?, waiter-list04/29/2016 14:07:51,spid33s,δ?,owner id=process5b534c8 mode=s04/29/2016 14:07:51,spid33s,δ?, owner-list04/29/2016 14:07:51,spid33s,δ?,pagelock fileid=1 pageid=22056721 dbid=5 objectname=ADCDB.dbo.NA_ Trafficmemberorderrelation id=lock1263c7180 mode=s associatedobjectid=7205759518198988804/29/2016 14:07:51,spid33s , Δ?,waiter id=process5b534c8 mode=s requesttype=wait04/29/2016 14:07:51,spid33s,δ?,waiter-list04/29/2016 14:07:51, Spid33s,δ?,owner id=process71da6bb88 mode=ix04/29/2016 14:07:51,spid33s,δ?,owner-list04/29/2016 14:07:51,spid33s,δ ?, Pagelock fileid=1 pageid=22075507 dbid=5 objectname=adcdb.dbo.na_trafficmemberorderrelation id=lock115c85a80 mode= IX associatedobjectid=7205759518198988804/29/2016 14:07:51,spid33s,δ?,resource-list04/29/2016 14:07:51,spid33s,δ? , (@p0 int<c/> @p1 int<c/> @p2 int<c/> @p3 int<c/> @p4 nvarchar (4000) <c/> @p5 NvarcHar (4000) <c/> @p6 nvarchar (4000) <c/> @p7 nvarchar (4000) <c/> @p8 nvarchar (4000) <c/> @p9 nvarchar (4000) <c/> @p10 Decimal (28<c/>5) <c/> @p11 Decimal (28<c/>5) <c/> @p12 nvarchar ( 4000) <c/> @p13 nvarchar (4000) <c/> @p14 datetime<c/> @p15 datetime<c/> @p16 int<c/> @p17 datetime<c/> @p18 datetime<c/> @p19 datetime<c/> @p20 int) INSERT into na_trafficmemberorderrelation ( applysource<c/> ordermode<c/> ordersource<c/> orderstate<c/> ECCode<c/> ECPrdCode< c/> usermobile<c/> memberordernum<c/> mainproductcode<c/> TrafficPackageCode<c/> billprice<c/> flowvalue<c/> memberapplycode<c/> crmapplycode<c/> EffectTime<c/> exprietime<c/> userid<c/> createtime<c/> ordertime<c/> ModifyTime<c/> DiscountRate) VALUES (@p0 <c/> @p1 <c/> @p2 <c/> @p3 <c/> @p4 <c/> @p5 <c/> @p6 <c/> @p7 <c/> @p8 <c/> @p9 <c/> @p10 <c/> @p11 <c/> @p12 <c/> @p13 <c/> @p14 <c/> @ p15<c/> @p16 <c/> @p17 <c/> @p18 <c/> @p19 <c/> @p20); Select Scope_identity () 04/29/2016 14:07:51,spid33s,δ?,inputbuf04/29/2016 14:07:51,spid33s,δ?,unknown04/29/2016 14:07:51,spid33s,δ?,frame Procname=unknown line=1 sqlhandle=0x00000000000000000000000000000000000000000000000004/ 29/2016 14:07:51,spid33s,δ?,insert into na_trafficmemberorderrelation (applysource<c/> OrderMode<c/> ordersource<c/> orderstate<c/> eccode<c/> ecprdcode<c/> UserMobile<c/> MemberOrderNum <c/> mainproductcode<c/> trafficpackagecode<c/> billprice<c/> flowvalue<c/> memberapplycode<c/> crmapplycode<c/> effecttime<c/> exprietime<c/> UserId<c/> createtime<c/> ordertime<c/> modifytime<c/> discountrate) VALUES (@p0 <c/> @p1 <c/> @p2 <c/> @p3 <c/> @p4 <c/> @p5 <c/> @p6 <c/> @p7 <c/> @p8 <c/> @p9 <c/> @p10 <c/> @p11 <c/ > @p12 <c/> @p13 <c/> @p14 <c/> @p15 <c/> @p16 <c/> @p17 <c/> @p18 <c/> @p19 < C/> @p20); 04/29/2016 14:07:51,spid33s,δ?,frame procname=adhoc line=1 stmtstart=626 stmtend=1494 sqlhandle= 0x020000003e44d0396c586db1098e79558ceb28c112e75ea804/29/2016 14:07:51,spid33s,δ?,executionstack04/29/2016 14:07:51,spid33s,δ?,process id=process71da6bb88 taskpriority=0 logused=2964 waitresource=page:5:1:22056721 waittime =3741 ownerid=43666495205 transactionname=user_transaction lasttranstarted=2016-04-29t14:07:45.920 XDES= 0x3023d7950 lockmode=ix schedulerid=15 kpid=21872 status=suspended spid=518 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-04-29t14:07:47.827 lastbatchcompleted=2016-04-29t14:07:47.823 Clientapp=.Net SqlClient Data Provider HOSTNAME=ADC-INTFE hostpid=10192 Loginname=daemon_adcuser Isolationlevel=read committed (2) XACTid=43666495205 currentdb=5 locktimeout=4294967295 clientoption1=671088672 clientoption2=12805604/29/2016 14:07:51, Spid33s,δ, (@p0 int<c/> @p1 nvarchar (4000) <c/> @p2 nvarchar (4000) <c/> @p3 int<c/> @p4 nvarchar ( 4000)) Select TOP (@p0) trafficmem0_. Trafficmemberorderrelationid as Trafficm1_433_<c/> trafficmem0_. Applysource as Applysou2_433_<c/> trafficmem0_. Ordermode as Ordermode433_<c/> trafficmem0_. Ordersource as Ordersou4_433_<c/> trafficmem0_. Orderstate as Orderstate433_<c/> trafficmem0_. Eccode as Eccode433_<c/> trafficmem0_. Ecprdcode as Ecprdcode433_<c/> trafficmem0_. Usermobile as Usermobile433_<c/> trafficmem0_. Memberordernum as Memberor9_433_<c/> trafficmem0_. Mainproductcode as Mainpro10_433_<c/> trafficmem0_. Trafficpackagecode as Traffic11_433_<c/> trafficmem0_. Billprice as Billprice433_<c/> trafficmem0_. Flowvalue as Flowvalue433_<c/> trafficmem0_. Memberapplycode as Membera14_433_<c/> trafficmem0_. Crmapplycode as Crmappl15_433_<c/> trafficmem0_. Effecttime as Effecttime433_<c/> trafficmem0_. Exprietime as Exprietime433_<c/> trafficmem0_. UserId as Userid433_<c/> trafficmem0_. Createtime as Createtime433_<c/> trafficmem0_. Ordertime as Ordertime433_<c/> trafficmem0_. Modifytime as modifytime433_<c/> trafficmem004/29/2016 14:07:51,spid33s,δ?,inputbuf04/29/2016 14:07:51, spid33s,δ?,unknown04/29/2016 14:07:51,spid33s,δ?,frame Procname=unknown line=1 sqlhandle= 0x00000000000000000000000000000000000000000000000004/29/2016 14:07:51,spid33s,δ?,select TOP (@p0) trafficmem0_. Trafficmemberorderrelationid as Trafficm1_433_<c/> trafficmem0_. Applysource as Applysou2_433_<c/> trafficmem0_. Ordermode as Ordermode433_<c/> trafficmem0_. Ordersource as Ordersou4_433_<c/> trafficmem0_. Orderstate as Orderstate433_<c/> trafficmem0_. Eccode as Eccode433_<c/> trafficmem0_. Ecprdcode as Ecprdcode433_<c/> Trafficmem0_. Usermobile as Usermobile433_<c/> trafficmem0_. Memberordernum as Memberor9_433_<c/> trafficmem0_. Mainproductcode as Mainpro10_433_<c/> trafficmem0_. Trafficpackagecode as Traffic11_433_<c/> trafficmem0_. Billprice as Billprice433_<c/> trafficmem0_. Flowvalue as Flowvalue433_<c/> trafficmem0_. Memberapplycode as Membera14_433_<c/> trafficmem0_. Crmapplycode as Crmappl15_433_<c/> trafficmem0_. Effecttime as Effecttime433_<c/> trafficmem0_. Exprietime as Exprietime433_<c/> trafficmem0_. UserId as Userid433_<c/> trafficmem0_. Createtime as Createtime433_<c/> trafficmem0_. Ordertime as Ordertime433_<c/> trafficmem0_. Modifytime as Modifytime433_<c/> trafficmem0_. DiscountRate as discoun22_433_ from Na_trafficmemberorderrelation traffic04/29/2016 14:07:51,spid33s,δ?,frame Procname=adhoc line=1 stmtstart=148 sqlhandle=0x0200000043e4d6202dd7a9505f76a2897ed4c6cada41102104/29/2016 14:07:51,spid33s,δ?,executionstack04/29/2016 14:07:51,spid33s,δ?,process id=process5b534c8 taskpriority=0 logused=0 waitresource=page:5:1:22075507 waittime= 3659 ownerid=43666495407 transactionname=select lasttranstarted=2016-04-29t14:07:46.007 XDES=0x58b16cc90 LockMode=S Schedulerid=16 kpid=18860 status=suspended spid=653 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted= 2016-04-29t14:07:46.007 lastbatchcompleted=2016-04-29t14:07:46.007 clientapp=.net SqlClient Data Provider hostname= ADC-INTFL hostpid=8820 Loginname=daemon_adcuser Isolationlevel=read committed (2) xactid=43666495407 currentdb=5 locktimeout=4294967295 clientoption1=671088672 clientoption2=12805604/29/2016 14:07:51,spid33s,δ?,process-list04/ 29/2016 14:07:51,spid33s,δ?,deadlock victim=process5b534c804/29/2016 14:07:51,spid33s,δ?,deadlock-list

From the process-list portion of the deadlock log, there are two processes:

Process ID=PROCESS5B534C8 is performing a query on table na_trafficmemberorderrelation

Process id=process71da6bb88 is performing an insert to table na_trafficmemberorderrelation

Because the deadlock log only records the last sentence of SQL that the current thread is executing, you must trace or read the program code through SQL Sever Profiler to get a complete list of SQL statements for the transaction. The answer is:

The query statement is a separate transaction

Select TOP (1) *from na_trafficmemberorderrelation trafficmem0_ where [email protected] and [email protected] and [email p Rotected] and [email protected]

The transaction for the insert operation is presumably

Begin Traninsert into na_trafficmemberorderrelation values (...) INSERT into na_trafficmemberorderrelation values (...) Commit

And then looking down at the resource-list part, the deadlock process is probably

1, insert thread process71da6bb88 has obtained the intent exclusive Lock IX on page (page) 22075507 (after the first INSERT statement is executed successfully, the IX lock on the page is added),

2. Query thread PROCESS5B534C8 get the shared lock on page 22056721

3, insert thread process71da6bb88 need to get IX on page 22056721 to execute the second INSERT statement, but the query process Process5b534c8 has obtained the S lock on page 22056721, and IX and S lock is incompatible, So the insert thread is blocked by the query process when it executes the second sentence of the INSERT statement

4, query thread Process5b534c8 need to get the S on page 22075507 to continue execution, but the insert thread has obtained the IX lock on the page, this time combined with the 3rd step, two processes into a deadlock wait

Then the problem comes, table na_trafficmemberorderrelation in the Usermobile field is indexed, according to the normal index lookup, only need to add S lock on the index key, the page is added is lock can be, and is lock and IX lock is compatible, No deadlock will occur. And here the query statement does add a lock on the page, that can only indicate that the query uses an index scan instead of index lookup (SELECT statement will request which locks, please refer to (click Open link).

So through DBCC Page view page 22056721 of the content discovery, some pages belong to the table na_trafficmemberorderrelation on the Ecprdcode field index, Therefore, it is concluded that the execution plan of this query is to get the result by scanning the Ecprdcode index, and the execution plan is generated according to the statistic information, so this is probably the case:

1, table na_trafficmemberorderrelation Insert a large amount of data, but has not yet reached the threshold of Automatic Update statistics (table Na_trafficmemberorderrelation now 65 million records).

2. SQL Server query optimizer selected the execution plan to scan the Ecprdcode index for the query statement (SQL Server should have a bug here)

3, through SQL SERVER Profiler tracking the statement lock application and release process found that the first page of the S lock, then apply for the second page of the S lock, and then release the first page of the S lock (I do not know what Microsoft is about), so loop, causing the query statement to hold a page s lock, Again to apply for the next page (S lock on the previous page has not been released) of the S lock, eventually forming a deadlock

Knowing the ins and outs, the solution is simple, add forceseek hint for query statement:

Select TOP (1) *from na_trafficmemberorderrelation trafficmem0_ with (forceseek) where [email protected] and [email Protec Ted] and [email protected] and [email protected]

Deadlock caused by a query statement