SQL Server unvisible locks

Question

SQL Server unvisible locks

lifeisdao 65

I caught events blocked_process_report and tried to analyze what was blocked and why. The xml data I saw the following information:

<data name="blocked_process">
		<value>
			<blocked-process-report monitorLoop="797267">
				<blocked-process>
					<process id="process20758057c28" taskpriority="0" logused="20000" waittime="15874" schedulerid="2" kpid="25520" status="suspended" spid="650" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2023-05-22T16:43:25.330" lastbatchcompleted="2023-05-22T16:43:25.330" lastattention="1900-01-01T00:00:00.330" clientapp="83 Server" hostname="024" hostpid="39912" isolationlevel="read committed (2)" xactid="454142046462" currentdb="5" currentdbname="XXX" lockTimeout="20000" clientoption1="671230048" clientoption2="128056">
						<executionStack>
							<frame line="1" stmtstart="950" stmtend="17300" sqlhandle="0x02000000fd5a0839e4850791d608df1f6698c2cd53e6609a0000000000000000000000000000000000000000"/>
							<frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"/>
						</executionStack>
						<inputbuf>  (@P1 numeric(10),@P2 numeric(10),@P3 numeric(10),@P4 datetime2(3),@P5 nvarchar(4000),@P6 numeric(10),@P7 datetime2(3),@P8 nvarchar(4000),@P9 numeric(10),@P10 numeric(10),@P11 varbinary(16),@P12 varbinary(16),@P13 nvarchar(4000),@P14 varbinary(16),@P15 varbinary(16),@P16 varbinary(16),@P17 varbinary(16),@P18 varbinary(16),@P19 varbinary(16),@P20 varbinary(16),@P21 varbinary(16),@P22 varbinary(16),@P23 varbinary(16),@P24 varbinary(16),@P25 varbinary(16),@P26 varbinary(16))SELECT DISTINCT  ... </inputbuf>
					</process>
				</blocked-process>
				<blocking-process>
					<process status="runnable" spid="650" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2023-05-22T16:43:25.330" lastbatchcompleted="2023-05-22T16:43:25.330" lastattention="1900-01-01T00:00:00.330" clientapp="83 Server" hostname="024" hostpid="39912" isolationlevel="read committed (2)" xactid="454142046462" currentdb="5" currentdbname="XXX" lockTimeout="20000" clientoption1="671230048" clientoption2="128056">
						<executionStack>
							<frame line="1" stmtstart="950" stmtend="17300" sqlhandle="0x02000000fd5a0839e4850791d608df1f6698c2cd53e6609a0000000000000000000000000000000000000000"/>
							<frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"/>
						</executionStack>
						<inputbuf>  (@P1 numeric(10),@P2 numeric(10),@P3 numeric(10),@P4 datetime2(3),@P5 nvarchar(4000),@P6 numeric(10),@P7 datetime2(3),@P8 nvarchar(4000),@P9 numeric(10),@P10 numeric(10),@P11 varbinary(16),@P12 varbinary(16),@P13 nvarchar(4000),@P14 varbinary(16),@P15 varbinary(16),@P16 varbinary(16),@P17 varbinary(16),@P18 varbinary(16),@P19 varbinary(16),@P20 varbinary(16),@P21 varbinary(16),@P22 varbinary(16),@P23 varbinary(16),@P24 varbinary(16),@P25 varbinary(16),@P26 varbinary(16))SELECT DISTINCT ...</inputbuf>
					</process>
				</blocking-process>
			</blocked-process-report>
		</value>
	</data>

So as there two threads of process the one of them blocked other, because only difference between them is ecid.
How can I proceed 
investigation of this issue? I guess that it is some kind of latch but I don't know the best way to understand root cause for blocking

Anonymous

2023-05-26T08:33:05.9466667+00:00

Hi @lifeisdao

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
lifeisdao 65 Reputation points

2023-05-26T10:40:20.2366667+00:00
Hi , I am afraid not helped, maybe any suggestions how I can dig deeper to find out root cause of process blocking taking into account that it is not typical locks ?

<data name="lock_mode"> <value>0</value> <text><![CDATA[NL]]></text> </data>

Unfortunately could not find any data about lock_mode.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-05-26T20:58:04.8933333+00:00

Is that from the Blocked Process Report event or some other XEvent? In any case, it is difficult to comment from only a fragment.

NL sounds like like a NULL lock which is compatible with everything, I believe. But as I said, I don't see the full context.
Anonymous

2023-06-01T07:58:36.5133333+00:00

Hi @lifeisdao

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
lifeisdao 65 Reputation points

2023-06-01T12:26:20.8066667+00:00

Hi, I am sorry, I am going to collect more information to return with details for further analyze. I am afraid I need a bit more time to gather all details. If it is better to close this one and open a new discussion let me know please.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-06-01T20:53:26.13+00:00

I would say that I have answered your original question. I realise that you still don't have a solution for your actual production problem, but you have shared very few details on this problem. And if you would, yes, it would be better to ask a new Question.
lifeisdao 65 Reputation points

2023-06-02T14:44:05.9533333+00:00

Ok, then thank you very much for your, let close this quiz and I will be back with more informative question. Thank you!

Accepted answer

0 additional answers

Your answer

Anonymous

2023-05-26T08:33:05.9466667+00:00

Hi @lifeisdao

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
lifeisdao 65 Reputation points

2023-05-26T10:40:20.2366667+00:00

Hi , I am afraid not helped, maybe any suggestions how I can dig deeper to find out root cause of process blocking taking into account that it is not typical locks ?

<data name="lock_mode"> <value>0</value> <text><![CDATA[NL]]></text> </data>

Unfortunately could not find any data about lock_mode.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-05-26T20:58:04.8933333+00:00

Is that from the Blocked Process Report event or some other XEvent? In any case, it is difficult to comment from only a fragment.

NL sounds like like a NULL lock which is compatible with everything, I believe. But as I said, I don't see the full context.
Anonymous

2023-06-01T07:58:36.5133333+00:00

Hi @lifeisdao

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
lifeisdao 65 Reputation points

2023-06-01T12:26:20.8066667+00:00

Hi, I am sorry, I am going to collect more information to return with details for further analyze. I am afraid I need a bit more time to gather all details. If it is better to close this one and open a new discussion let me know please.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-06-01T20:53:26.13+00:00

I would say that I have answered your original question. I realise that you still don't have a solution for your actual production problem, but you have shared very few details on this problem. And if you would, yes, it would be better to ask a new Question.
lifeisdao 65 Reputation points

2023-06-02T14:44:05.9533333+00:00

Ok, then thank you very much for your, let close this quiz and I will be back with more informative question. Thank you!

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

Blocking can be due to other things than locking. In this case, since the spid is the same for both and only the ecid is different, it is a matter of a parallel plan where the work is unbalanced between the thread. The "blocked" process is waiting for the other thread to complete.

I think you should only investigate this issue, if there are real complaints about performance. It is quite likely that the query can benefit from tuning, but most workloads have query than can be tuned. And as long as users are happy, our employers or customers may prefer that we spend our time on other tasks.

lifeisdao 65 Reputation points

2023-05-23T09:28:14.2966667+00:00
Hi Erland!

Again great idea regarding "as long as users are happy", abosultely agree! I am searching only calmness as DBA). So, may be you have any thoughts or references why blocks? Latches, spinlocks, anything else? Need to find query plan for futher investigation?
Can it be for example multi-threads SORT opertaion when all 9 from 10 threads are waiting for one with CXPACKET wait and as a result we have blocked processes?

In example above the duration of block about 15 sec, it is crucial for our app where we have query timeout 20 sec.

<data name="duration"> <value>15874000</value> </data>
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-05-23T21:19:14.5933333+00:00

Spinlocks does not end up in sys.dm_os_waiting_tasks, since these processes are in fact not waiting in that sense. Nor do waits in latches, I believe. I can't recall I've seen any, at least.

I should add that sys.dm_os_waiting_tasks is just a representation of internal structures, but supposedly the blocked process report takes the information from the same source.

In this DMV you see all waiting tasks, but not all waiters have a blocking task. That's mainly lockers and other threads in case of parallelism.

Can it be for example multi-threads SORT opertaion when all 9 from 10 threads are waiting for one with CXPACKET wait and as a result we have blocked processes?

Yes.

In example above the duration of block about 15 sec, it is crucial for our app where we have query timeout 20 sec.

In such case, maybe it's time for tuning...

Share via

SQL Server unvisible locks

0 additional answers

Your answer