About DEADLOCK by SELECT

Question

Hello.
Regarding deadlock, I posted it because there is a phenomenon that I do not understand why deadlock occurs.

It occurs in the WEB application developed by SQLSERVER2017 + JAVA.
The transaction isolation level is set to the default "READ COMMITTED".

The content of the error is
"Transaction (process ID XX) deadlocks with other processes on locked resources,
This transaction was subject to the deadlock. Please re-execute the transaction.
※"XX" is the process ID value. "

After investigating the cause, two SELECT collided and an error occurred.

1. Transaction: A
   SELECT i_uid
   　FROM tableU
   　WHERE i_uid IN (SELECT i_uid FROM tableI WHERE r_uid = 5)
   　ORDER BY i_uid ASC, u_o_id ASC
2. Transaction: B
   SELECT s_uid, tableS.i_uid
   FROM tableS
   　LEFT JOIN tableI ON tableS.i_uid = tableI.i_uid
   　WHERE tableI.r_uid = 3 AND tableS.active = 'true'

So I have a question.
I don't understand why I'm getting IX even though it's a SELECT.
Originally I think it is S or IS, but why is IX acquired?
What kind of specification method is there to prevent it from becoming IX?

I'm in trouble because I can't find a solution.
Please let me know if you need more information.

Thank you.
ar_sy

Answer 1

Hi @太郎 アルゴ ,

Did you only run the two select query without Update query?

Intent exclusive (IX) – when an intent exclusive lock (IX) is acquired it indicates to SQL Server that the transaction has the intention to modify some of lower hierarchy resources by acquiring exclusive (X) locks individually on those lower hierarchy resources.

Refer to the bog All about locking in SQL Server to get more information about locks.

You can follow the steps from MS document Understand and resolve SQL Server blocking problems to resolve your issue.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Answer 2

Hi @太郎 アルゴ ,

Yes, the IX is been required for insert query, the transaction has the intention to modify some of lower hierarchy resources.

You can execute the four queries one by one, do the next after the previous one has complete.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Answer 3

I note that there are page locks involved. If you can add indexes or improve the query, it is possible that you can avoid that the SELECT query wants page locks. A shared lock page level will clash with an IX lock on the same level.

Answer 4

Hi , @CathyJi-MSFT
Hi , @Erland Sommarskog

I'm sorry for the late reply.

I added an index to tableU.
Then the deadlock was resolved!!!

However, another deadlock occurred in another place......

Thank you for answering!!!!!!!