NOLOCK hint is being ignored
-
We have a stored procedure that specifies NOLOCK hints on all its tables. When we run this procedure on SQL Profiler though, we find that it still causes a table lock, even with the NOLOCK hints in place. Why is this happening, and what can I do to stop the procedure from causing a table lock?
-
NOLOCK only applies to the SELECT statement. Is it possible you may have an INSERT, UPDATE or DELETE statement in your SP which is causing the tablelock.
Rick.
-
Can you post the entire proc?
Andy
-
There is no UPDATE, DELETE, or INSERT statements. It's a rather simple stored proc, which is why this is so mystifying....
Here is the code:
CREATE PROCEDURE dbo.sp_SaGetFundSourceGLAccount
@SaTransID int,
@Type char(1)
AS
Set NoCount On
Declare@SyCampusID int,
@FaStudentAidID int,
@FaFundSourceID int
-- Get the CampusID and Student Aid ID from the SaTrans table
Select@SyCampusID = SaTrans.SyCampusID,
@FaStudentAidID = SaTrans.FaStudentAidID
From SaTrans (NOLOCK)
Where SaTrans.SaTransID = @SaTransID
-- Get the Fund Source ID from the Student Aid table
Select@FaFundSourceID = FaStudentAid.FaFundSourceID
From FaStudentAid (NOLOCK)
Where FaStudentAid.FaStudentAidID = @FaStudentAidID
-- Return the resultset listing the Debit & Credit Account Information
Select FaFundSourceGLAcct.DebitAccount
,FaFundSourceGLAcct.CreditAccount
,FaFundSourceGLAcct.CAcctIndex
,FaFundSourceGLAcct.DAcctIndex
From FaFundSourceGLAcct (nolock)
Where FaFundSourceGLAcct.FaFundSourceID = @FaFundSourceID
AND FaFundSourceGLAcct.SyCampusID = @SyCampusID
AND FaFundSourceGLAcct.TransactionType = @Type
Return (0)
GO
-
It is incorrect that the NOLOCK hint will not issue a lock from BOL
quote:
NOLOCK = Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.See "Locking Hints" in BOL for an example where they show a query with NOLOCK hint and what happens in regards to locks.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
May be you need to have "With" verb before (NOLOCK). I did not try and i am not sure.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
-
quote:
May be you need to have "With" verb before (NOLOCK). I did not try and i am not sure.Yes, the syntaxes requires WITH(NOLOCK)like:
-- Get the CampusID and Student Aid ID from the SaTrans table
Select @SyCampusID = SaTrans.SyCampusID,
@FaStudentAidID = SaTrans.FaStudentAidID
From SaTrans WITH(NOLOCK)
Where SaTrans.SaTransID = @SaTransID
-
Again refer to BOL as there are still locks set when the NOLOCK hint is used. See my previous post.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
The SELECT query using the lock hint WITH (NOLOCK) will not issue a shared (S) lock. A shared lock is typically used if no lock hint is specified, unless the transaction isolation level is set to read uncommitted. In fact, WITH (NOLOCK) makes the query function the same. Also, exclusive (X) locks will not be honored. Essentially, if you have a running transaction modifying a table, using WITH (NOLOCK) will allow you to get to the rows locked by that transaction for the update.
The example given in Books Online involves a Schema Stability (Sch-S) lock. This lock is compatible with any other type of lock except for Schema Modification (Sch-M) locks. Schema Modification locks are used for DDL where objects are being modified. The Sch-S lock will be initiated to ensure that during the compiling of a query, the underlying objects won't be changed.
Imagine in a query was being compiled that referenced a particular column (even using NOLOCK) and another query was being executed which dropped said column. That's the need for the Sch-S lock, to prevent that scenario from occuring.
BTW, I attempted to run queries in SQL 2K using locking hints and using WITH and leaving it out. Apparently, it is optional. At least in SQL 2K, it does not make a difference with respect to locking behavior.
Can you post the types of locks being kept? Are the Shared (S) locks or Schema Stability (Sch-S) locks? Also, ensure the locks are on the tables being referenced WITH (NOLOCK). Because you are using sp_, unless your stored procedures are located in the master database, you are creating exclusive [COMPILE] locks on the stored procedures themselves, causing them to be serialized.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
OK.....I found the answer....the only locks being kept are Schema Stability locks, which don't interfere with other users. So the nolock hint is working.....thanks guys for the info...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply