May 15, 2008 at 3:06 am
Hello all,
I'm not very experienced in analyzing a Profiler Trace. Therefor I want to ask you to clear something for me.
I've created a trace of a short process to see if we can improve this. The process contains several queries in which we use the hint "with (nolock)". But in the trace-results I see quite some "lock:acquired" and "lock:released".
How can I find out more details about this "lock:acquired"? I want to know on which table and what type of lock (row/page/table) is acquired.
Kind regards,
Hans
May 15, 2008 at 5:45 am
Is the statement in question a SELECT or an INSERT/UPDATE/DELETE. If the latter, the engine ignores the NOLOCK hint.
The Mode column will show you what kind of lock you're dealing with and the ObjectId will tell you which object the lock was placed on. See Books Online for Details. This is the definitions for the Mode:
Resulting mode after the lock was acquired.
0=NULL - Compatible with all other lock modes (LCK_M_NL)
1=Schema Stability lock (LCK_M_SCH_S)
2=Schema Modification Lock (LCK_M_SCH_M)
3=Shared Lock (LCK_M_S)
4=Update Lock (LCK_M_U)
5=Exclusive Lock (LCK_M_X)
6=Intent Shared Lock (LCK_M_IS)
7=Intent Update Lock (LCK_M_IU)
8=Intent Exclusive Lock (LCK_M_IX)
9=Shared with intent to Update (LCK_M_SIU)
10=Shared with Intent Exclusive (LCK_M_SIX)
11=Update with Intent Exclusive (LCK_M_UIX)
12=Bulk Update Lock (LCK_M_BU)
13=Key range Shared/Shared (LCK_M_RS_S)
14=Key range Shared/Update (LCK_M_RS_U)
15=Key Range Insert NULL (LCK_M_RI_NL)
16=Key Range Insert Shared (LCK_M_RI_S)
17=Key Range Insert Update (LCK_M_RI_U)
18=Key Range Insert Exclusive (LCK_M_RI_X)
19=Key Range Exclusive Shared (LCK_M_RX_S)
20=Key Range Exclusive Update (LCK_M_RX_U)
21=Key Range Exclusive Exclusive (LCK_M_RX_X)
"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
May 15, 2008 at 6:05 am
Thanks Grant,
Your post made it all clear now. The query is a SELECT so the NOLOCK should apply, but I can now continue analyzing the trace why and on what the LOCKS are required.
Regards,
Hans
May 16, 2008 at 7:32 am
A LOT of stuff happens when you issue a simple SELECT statement! While the select is running, you certainly wouldn't want someone to alter the table underneath you and possibly affect the select columns. Nor would you want the table dropped. Some locks are taken on metadata to keep these things from happening. All the NOLOCK does is not bother with or honor DATA-related locks.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 19, 2008 at 11:07 am
But you should probably consider the consistency problem that you can get by using NOLOCK. If you are using it for reading tables that are not usually changing. I never thought this could cause so much problem if not taken care of 😛
I found an article by Tony Rogerson which was quoted in some other post in the forum. You might find this interesting.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/16/1345.aspx#1350
Have fun with it :D.
May 19, 2008 at 11:17 am
Itzik Ben-Gan gave a presentation last year at the PASS summit that showed the same query getting different results with very small data sets, all because of NOLOCK. It really is a dangerous hint.
"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
May 20, 2008 at 8:25 am
Grant Fritchey (5/19/2008)
Itzik Ben-Gan gave a presentation last year at the PASS summit that showed the same query getting different results with very small data sets, all because of NOLOCK. It really is a dangerous hint.
Itzik was pretty surprised himself when this issue was first reported about 2 years ago. No one, even Microsofties, believed it was possible at the time. And I think everyone was shocked when the developer said it was by design! 🙂 I have joined the ranks of others who have rescinded the recommendation to NOLOCK most queries for performance/concurrency gains. Uncommitted data is one thing - WRONG data is another matter entirely!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 20, 2008 at 10:39 am
Problem is, in our shop, the developers jumped on it with both feet. It's everywhere. We're slowly backing it out. We get a lot resistance because using it allows for a certain amount of laziness in the code that no one wants to revisit and clean up.
"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
May 21, 2008 at 12:38 am
The discussion becomes very interesting. I didn't know that NOLOCK can cause so much issues.
But when I read the BOL I read the following "NOLOCK: Does not issue any locks. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements."
So if SQL takes it as default, couldn't that create all the issues where this discussion is about?
May 21, 2008 at 4:50 am
I'm working from home today so I don't have all my reference material at hand. If I miss a detail here & there, someone will correct me.
Select statements put a shared lock on the data they are reading. This is so that reads can only retreive committed data otherwise the data would change underneath your query as it was reading. This implies a certain amount of waiting in line taking place as the reads wait for the changes to complete and the changes wait until the reads have got out of the way. Placing NOLOCK as a hint changes this behavior. The read no longer places shared locks on the data, thereby, in theory, speeding things up a bit. But, the data can literally change underneath the read. Most people refer to this as "dirty" reads, meaning, the data is changing and so the read gets uncommitted data. But Itzik showed how, because of page reallocations that occur as part of data changes, you can actually get duplicate records and other sorts of bad data because of the NOLOCK hint. Incorrect and bad data, not merely changed data.
"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
May 21, 2008 at 7:30 am
HanShi (5/21/2008)
The discussion becomes very interesting. I didn't know that NOLOCK can cause so much issues.But when I read the BOL I read the following "NOLOCK: Does not issue any locks. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements."
So if SQL takes it as default, couldn't that create all the issues where this discussion is about?
I beleive this is incorrect Han. The default isolation level for a connection is READ COMMITTED. You need to explicitly change isolation level to READ UNCOMMITTED or explicitly include the (NOLOCK) table hint to get that behavior.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply