Need help on LCK_M_IX blocking in SQL Server
-
Can you help in which server settings is causing this block LCK_M_IX.
The table omro_po_trndet_tmp has a clustered index on GUID and multiple columns - nonclustered index
The table ommx_mst_mmi_srch_tmp has a clustered index on guid column. There are about 50k rows in this table.
session_id
status
Blk_by
wait_type
wait_resource
Wait_M
cpu_time
reads
writes
Elaps_M
statement_text
command_text
command
118
suspended
132
LCK_M_IX
OBJECT: 10:1332915820:9
8
2
0
0
8
delete from omro_po_trndet_tmp where guid = @guid_tmp
[scmdb].[omroser_fet_aut_trandet]
DELETE
132
running
0
0
645656
313831
80702
13
UPDATE ommx_mst_mmi_srch_tmp
SET mst_prod_bal_qty = ISNULL(tqt_qty, 0) - ISNULL(mqt_moved_qty, 0),
mst_prod_qty = ISNULL(tqt_qty, 0) - ISNULL(mqt_moved_qty, 0),
mst_alt_qty = ISNULL(tqt_alt_qty, 0) - ISNULL(mqt_moved_alt_qty, 0),
mst_alt_bal_qty = ISNULL(tqt_alt_qty, 0) - ISNULL(mqt_moved_alt_qty, 0)
FROM ommx_mst_mmi_srch_tmp /*(nolock)*/ --Nolock commented for CUMI_02_11_2021
LEFT OUTER JOIN ommx_tqt_tomove_qty_tmp(NOLOCK)
ON (
tqt_ou_code = @ctxt_ouinstance_tmp
AND tqt_guid = @guid_tmp
AND mst_item = tqt_itemcode
AND mst_variant = tqt_variant
AND mst_prod_ord = tqt_prod_ord
AND mst_line = tqt_line
AND mst_lotno = tqt_lot_no
AND mst_sublotno = tqt_sub_lotno
AND mst_trans_no = tqt_tran_no
AND mst_status = tqt_item_status
)
LEFT OUTER JOIN ommx_mqt_moved_qty_tmp(NOLOCK)
ON (
mqt_ou_code = @ctxt_ouinstance_tmp
AND mqt_guid = @guid_tmp
AND mst_item = mqt_itemcode
AND mst_variant = mqt_variant
AND mst_prod_ord = mqt_prod_ord
AND mst_line = mqt_line
AND mst_lotno = mqt_lot_no
AND mst_sublotno = mqt_sub_lotno
AND mst_trans_no = mqt_tran_no
AND mst_status = mqt_item_status
)
WHERE mst_guid = @guid_tmp
AND mst_ou_instance = @ctxt_ouinstance_tmp
[scmdb].[ommxmmi_sp_sr_det_out]
UPDATE
- This topic was modified 2 years, 8 months ago by vikramchander90.
-
My opinion - that is hard to say on a forum post as we have no access to your data or your data structures. My question to you would be do you know what a lock is in SQL Server?
Not trying to be rude or insult you, but you are asking what server setting would cause a lock and the answer is none. That's not a server setting, that is the SQL engine by design. You are getting an Intent Exclusive (IX) lock on the table because you are doing something that is modifying the underlying data in the table. In order to change the data, you MUST first lock the table to ensure that 2 queries don't update the same page a the same time causing inconsistencies in the data. It is by design.
What you need to do in order to reduce the locking time is to improve your queries. Your second query (the update) is running for a long time it appears - the CPU time is 645656 which is a long time for a query to run for! You should look at tuning your second query to improve performance.
Now, you say that you have "a clustered index on GUID and multiple columns - nonclustered index". This makes sense for the GUID (mind you, the clustered index MAY be able to be improved) as you are filtering on GUID, but when you say multiple columns - nonclustered index, do you mean that you have multiple other nonclustered indexes on the other columns? Are all of the join columns and where columns indexed? how many indexes do you have?
To fix the blocking (which is the problem, not the locking), you need to speed up your query. To speed up your query, you need to determine what is making it slow and address that problem. If, for example, your query is updating 100 TB of data, you will want to break that down into smaller chunks so the blocking is shorter.
On top of that, the tables that the DELETE is touching and the tables in the UPDATE appear to be unrelated. This leads me to believe that one, or more, of those are views OR you have triggers on there that are causing some hiccups.
What I would start by doing is to restore your system to a test/dev environment and then tune that second query so it can complete in a much shorter time. If you are unable to tune it, I would tweak it to update only a few rows at a time. How many will depend on the performance, but I would aim to have it complete in under 5 seconds on my systems. I am not sure how much blocking you allow on yours, but whatever number that is. It may even be you are only able to update 1 row at a time.
I'd also remove those nolocks as they are probably not helping anything, and may actually be causing problems. NOLOCK does basically nothing for a shared lock (which is usually what a select statement will request) and if the data is changing (which I would expect is likely for a query that runs so long), can provide inaccurate results. I would also look if it is possible to reduce your JOIN columns as the fewer of those there are, the faster the join will occur. Otherwise SQL MUST compare all of the columns (unless you have a nice index that can handle all of that).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - Mr. Brian Gale wrote:
NOLOCK does basically nothing for a shared lock (which is usually what a select statement will request)
That's not factually correct. NOLOCK prevents having to take, and release, that shared lock on every row as it is read.
That is not to say that NOLOCK is properly used here -- and it certainly is not on the table that is being UPDATEd, on which NOLOCK is literally impossible -- but that NOLOCK does server a purpose even for just shared locks.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply