July 22, 2009 at 8:28 am
how to resolve LCK_M_IX.
or view the cause by query
July 22, 2009 at 8:38 am
Care to explain the problem? LCK_M_IX is an Intent-Exclusive lock.
July 22, 2009 at 8:46 am
Ok.Thanks....
In hurry I make a mistake.
July 22, 2009 at 9:36 am
If you want to view the cause of a lock, query sys.dm__tran_locks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2009 at 10:25 am
?? Okay, did you have a problem you wanted help with, or did you just want to know what type of lock the code represented?
July 23, 2009 at 5:10 am
Actually I get this problem previous day.
I have seen the LCK_M_IX from profiler and get that it is intent lock but cant get how to resolve that
can I know which user or which statement has cause this problem.
or how to avoid.
I tried by sys.dm_tran_locks but i cant get info about which table or user has caused this.
I u have time can give link or solution may be in feature i required?(for knowledge)
July 23, 2009 at 6:29 am
Locking is a natural and important part of updating data in SQL Server. Intent locks, and others, should be reduced as much as possible, but they can't be prevented or eliminated. To reduce locks you need to optimize your queries that they run as fast as possible, put the right indexes, especially clustered indexes, in place, possibly decrease your isolation level, partition data...
To see the lock information, you need to combine sys.dm_tran_locks with other dynamic management views such as sys.dm_os_waiting_tasks to see what sessions are being blocked, the resources and how long they've been blocked, sys.dm_exec_requests so that you can get to sys.dem_exec_sql_text.
Put it all together & you can monitor & investigate locks pretty quickly.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 23, 2009 at 6:53 am
mjarsaniya (7/23/2009)
Actually I get this problem previous day.I have seen the LCK_M_IX from profiler and get that it is intent lock but cant get how to resolve that
can I know which user or which statement has cause this problem.
or how to avoid.
I tried by sys.dm_tran_locks but i cant get info about which table or user has caused this.
I u have time can give link or solution may be in feature i required?(for knowledge)
That's great, but you really haven't answered the question. Is it causing a problem, and if so what is the problem.
July 23, 2009 at 9:22 am
I think intent exclusive lock means some has locked table for DML and the process of DML is high.
So another user are not capable to DML on same table.
Now please reply (may i have misunderstand or my concept is right) then.....
I want to pause long running process and give chance to another one because it takes a second or two second not more.
Then i want to continue first process.
I think u can understand what i want to do.
July 23, 2009 at 9:34 am
You can't really do that, no. You could break up the longer running process into smaller transactions so that there are available windows between commits for other processes to obtain locks, but no, you can't pause a process in the middle to allow others to work instead. As a matter of fact, attempting to pause it while it's still holding locks would probably be very bad for the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 24, 2009 at 12:07 am
Ok.thanks ....
after all it is good to write accurate and small queries for better performance.
I have make one Sp which takes load of 2 million records that is not daily but once a month procedure.
June 15, 2012 at 11:19 am
If you are making a costly update in a big table (with several joins), you should consider to partition your table. In my case it was a great solution .
So, like somebody say above, first of all, study and design the best indixes for the table, after that have in mind the possibility to partitio your table.
It's really hard that to decrease your isolation level coul help you inside update process. (maybe making (no LOCK) in the joined tables, but...mmm
September 2, 2015 at 11:28 am
If you are doing large batch operations, you should optimize the operation by figuring out ways to get rid of complex criteria and joins so that you are not taking wide sweeping locks on the table.
You should also be aware that IX locks can be shared with other IX locks. It is an indicator that there is an exclusive (X) lock at a lower level (page, row, range of rows, key). This is in case of lock escalation, it doesn't need to check every page, row, and key to see if there is a conflicting X lock somewhere on the table. It only needs to check if there is another IX lock on the table.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply