Locking Hints and Examples are as follows. The usage of them is the same but the effect is different.
ROWLOCK
Use row-level locks when reading or modifying data.
PAGLOCK
Use page-level locks when reading or modifying data.
TABLOCK
Use a table lock when reading or modifying data.
DBLOCK
Use a database lock when reading or modifying data.
UPDLOCK
UPDLOCK reads data without blocking other readers, and updates it later with the assurance that the data has not changed since the last read.
XLOCK
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.
HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required.
NOLOCK
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.
Examples:
SELECT OrderID FROM Orders WITH (ROWLOCK) WHERE OrderID BETWEEN 100 AND 2000
UPDATE Products WITH (NOLOCK) SET ProductCat = 'Machine' WHERE ProductSubCat = 'Mac'
I hope you enjoyed this video on Locking Hints. Here are my few recent videos and I would like to know what is your feedback about them. You can also subscribe to my YouTube Channel – SQL in Sixty Seconds.
- Copy Database – SQL in Sixty Seconds #169
- 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
- Excel – Sum vs SubTotal – SQL in Sixty Seconds #167
- 3 Ways to Configure MAXDOP – SQL in Sixty Seconds #166
- Get Memory Details – SQL in Sixty Seconds #165
- Get CPU Details – SQL in Sixty Seconds #164
- Shutdown SQL Server Via T-SQL – SQL in Sixty Seconds #163
- SQL Server on Linux – SQL in Sixty Seconds 162
- Query Ignoring CPU Threads – SQL in Sixty Seconds 161
- Bitwise Puzzle – SQL in Sixty Seconds 160
- Find Expensive Queries – SQL in Sixty Seconds #159
- Case-Sensitive Search – SQL in Sixty Seconds #158
- Wait Stats for Performance – SQL in Sixty Seconds #157
- Multiple Backup Copies Stripped – SQL in Sixty Seconds #156
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
185 Comments. Leave new
hi,
i have Category table with parent -child relationship.Now i want to copy this record and insert into same table with parent-child relationship with new identity.
declare @id as int
SEt @id=IDENT_CURRENT(‘[tblCategory]’) ;
WITH t AS
(SELECT [CategoryId],[CategoryParentId],[CategoryName],
(@id
+ ROW_NUMBER() OVER (ORDER BY CategoryId))
AS NewCategoryParentId
FROM tblCategory)
INSERT INTO [CategoryParentId],[CategoryName]
SELECT isnull(t2.NewCategoryParentId,0),t1.CategoryName
FROM t t1
LEFT JOIN t t2
ON t1.CategoryParentId = t2.CategoryId ;
I want to lock table for insert.
i have a query
set transaction isolation level serializable
BEGIN TRAN
declare @MyVal nvarchar(50)
set @MyVal =(
select thisVal from TableA (TABLOCK)
)
declare @TempTable table ( Col1 nvarchar(10) )
Insert into @TempTable (Col1)
select MyCol From TableB (NoLock)
………………..
………………..
COMMIT TRAN
Please tell me….
TableA had been locked during Tran?
TableB had been nolock during Tran?
Hello Pinal, I am facing a very severe problem. I have a table with 4 lac records almost and I am displaying them in gridview via paging. I am retrieving records from the table in chunks of 500 and then bind those 500 to gridview.
The problem really is that when I go for the last chunk generated by my query, it times out… It does not times out at all for any other chunk except last chunk. Please I await your reply sir.
Thanks
Hi pinal
We faced a blocking issue when we issued a qurey like
select from , with (nolock) where order by
I am sure there was DDL commands after this becuase it happend in production environment
I would like know why this led to a blocking even though we applied nolock option
can i get more descriptions bout all these locks ….if possible provide me ..or if you have written some articles please provide me appropriate link please
hi Pinal,
I have a situation where large number of user reads data from table, at the same time some job will insert data into the same table .we should make user to wait till insert complete and we should not make job to wait until shared lock release (data has to be refresh for every 2 min’s).
thought of apply hold lock while insert the data into table .
Please suggest me the best practice
hi Pinal,
I have a situation where large number of user reads data from table, at the same time some job will insert data into the same table .we should not make user to wait till insert complete and we should not make job to wait until shared lock release (data has to be refresh for every 2 min’s).
thought of apply hold lock while insert the data into table .
Please suggest me the best practice
hi pinal,
i got below exception while using (with rowlock) as i try to execute your above example for locking in select statement.am sql server 2005.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘with’.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
It should be WITH (ROWLOCK)
hi!!
i want to ask that how create a lock on login.
i as an administrator created a user and now i want to lock my login section in sql server 2000 can you tell me how can i do it.
REPLY!! ASAP…
hii pinal,
thank u very much for such a great post…….. it’s fabulous.
I need to ask u one question.
suppose if i am using rowlock in following manner
SELECT OrderID
FROM Orders WITH( holdlock , ROWLOCK)
WHERE OrderID =100
then can u please tell me ….. untill how much it will hold this lock . because as soon as i execute this query ; simultaneously i can select the same query from another page. that means it is not holding a lock …
SO PLEASE ME HELP OUT…….
THANKS IN ADVANCE
hey pinal,
thank u very much for such a great post…….. it’s fabulous.
I need to ask u one question.
suppose if i am using rowlock in following manner
SELECT OrderID
FROM Orders WITH( holdlock , ROWLOCK)
WHERE OrderID =100
then can u please tell me ….. untill how much it will hold this lock . because as soon as i execute this query ; simultaneously i can select the same query from another page. that means it is not holding a lock …
SO PLEASE ME HELP OUT…….
PLEASE REPLY ME AS SOON AS POSSIBLE
THANKS IN ADVANCE
Hi Kapil,
The syntax is incorrect. Use WITH(HoldLock) rather than (WITH HoldLock).
Query will become:
select * from projects WITH (HOLDLOCK)where pj_id=1
Same is for (WITH ROWLOCK). Use WITH (ROWLOCK).
Query will become:
Select * from users WITH (ROWLOCK) where userid=141
Hello,
We are migrating from sql server 2000 to sql server 2005. When running the advisory stats, the below error message was given for an particular SP.
In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause of a query only when the hints are specified using the WITH keyword.
Below is the query in the SP, here an index has been specified to be used.
Select cw.FundID from BRCallPaymentWork cw (INDEX = NonIdx_FourKey_BrCallPayment) where cw.FundID = cp.FundID and cw.InvestorID = cp.InvestorID and cw.ParentID = cp.ParentID and cw.ChildID = cp.ChildID and cw.amountcalled = cp.amount
I believe in 2005 we need to use an hint , can u please help me how will this work?
Your help will be appreciated.
thanks,
aman
Hi Pinal,
I want to know how one can view and remove locking on DBOs in SQLServer and also how to decide on which table to be removed off locking (if is deadlocked) without loosing data.
“Thanks in advance P.Dave.”
We have some deadlock issue while updating a set of tables in a single transaction.if multiple users are trying to update same table at same time, it gets locked. Could you please give a solution for this
Thanx in advance
here i read a row
select name from emply where id=19
and nxt if i try to read d same row it should be locked..i shouldnt be able to read it…can u suggest me how to achieve this hibernate
I’d recommend editing your text up top to remove that nolock is the default for SELECT or specify up top its the default only for read uncommitted. I know you mention this in your reply below this is the case, but since READ UNCOMMITTED is not a usual default this is quite misleading when you first read this post as witnessed by several replies in this thread
Hello sir,
I am susendran,i new to sql i want to lock my stored procedure and when i want nedd to modify it please give me idea
Hello Sir,
Please can you help me on executing the ssis package from
trigger,as it’s going to an infinity loop .
Hello sir,
Please tell me why we need nolock and working of rowlock.