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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
185 Comments. Leave new
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.
hello sir,
i have two linked server.
i am trying to insert from one to another using sp but in all this process
one table is locked.
not even access the data from local server also can’t drop table
this table have one trigger afterinsert and using this trigger i maintain a
log that table also locked so trigger can’t drop or altered
so to release this what i have to do
help me…
Hi pinal, u r doing good job, This blog helps me lot.
how to unlock d locked database
I have a database where I need to process 100+ queries on the same table per second. The query takes approx 20 milliseconds to run normally, but as I start to bring up more threads and do more simultaneous queries it starts to slow down to 200-2000 milliseconds. The more simultaneous queries I use the slower they all become. Is there any way to avoid this? I am using NOLOCK in the query, but was hoping you may have some tips to help me with this one.
Hi Pinal,
I am facing one problem in one our application. Here we have table where we insert 400+ records every time. At the same time if any user performs a select operation on this, we are getting time out error and occurrence of deadlock.
When we select the records with nolock condition we are getting the records which are not locked. But this is not the actual solution to it.
This table consists of 500000 records altogether. Kindly suggest us a way to overcome of it.
Thanks.
Hi Pinal,
I am facing performance issue why processing the data. Here is what my data processing scenario. we process the data on daily basis (charges/Transactions)
1. We load the data into Temporary Tables (New) and Create the Transaction keys for the new records.
2. Then we merger the new data with Production Data (below logic we follow)
A. Insert the complete Production Data into TableB
B. Create Indexs on TableB
C. Delete old data if exists based on MDXkey with New Table
D. Insert New Data into TableB
E. Switch the Productiontable – TableA ,TableB to Production Table
F. Drop Table new, TableA
Questions:
1. Because of the above process we are facing lot issues in server level (Database server is getting crashed frequently) the reason for this is Lot of IO reads and taking lot of resources Is there any alternate way you can suggest me to process data
Thank you in Advance
Thanks
hello sir,
Please help me. in my programme i m using sql 2008. when it run it multi user environment.if one person save a big invoice then my programme in all other systems are hanged untill he save the invoice. I know it s by db table lock. how to overcome it .pls reply. thanks in advance.
Hi Pinal,
Since I knew about this site.. have become a frequent reader.. Could you please tell me by default when sql runs an update is there any lock on table also during insert. Thanks, Laxmi
Hi Pinal
I Have some problem in my database SP. That is created by another person .Now he is not with us.He Lock the Stored procedure and other person can not modify this.Now I want to modify and check This Procedure.Can you help me how this is Possible?
Thanks
Susendran
Hi,
The below code part of an stored procedure has been mentioned below:
——————————————————————————-
UPDATE [CandidateTests].[TestDetails_test1]
SET [TestUID] = (SELECT MIN(TestUID) – 1 FROM CandidateTests.TestDetails_test1 WITH(NOLOCK))
WHERE [BusinessStreamID] = @BusinessStreamID AND
[TestID] = @TestID AND
[BankID]= @BankID AND
[TestYear] = @TestYear
UPDATE [CandidateTests].TestDetails_test1
SET [TestUID] = (SELECT MIN(TestUID) – 1 FROM CandidateTests.TestDetails_test1 WITH(NOLOCK))
WHERE [BusinessStreamID] = @BusinessStreamID AND
[TestUID] = @TestUID
——————————————————————————-
Here BusinessStreamID,TestID,BankID,TestYear are primary key and BusinessStreamID and TestUID are having nonclustered unique index.
This code part is taking log time to excute to process approx 60,000 records.
It seems the code part is blocking by itself.
So could you please help me out to resolve the problem.
Thanks,
Kaushik
Rowlock hints not working…. select * from std with(rowlock) where rno = 5;
same query works from another pc, database is accessed from server….
client server envirnoment….
I have made the query like –
SELECT TOP 3 * FROM MMSContent WITH (HOLDLOCK XLOCK ROWLOCK) waitfor delay ‘0:00:15’
SELECT TOP 3 * FROM MMSContent WITH (HOLDLOCK XLOCK ROWLOCK) waitfor delay ‘0:00:10’
SELECT TOP 3 * FROM MMSContent WITH (HOLDLOCK XLOCK ROWLOCK) waitfor delay ‘0:00:10’
When I run the first query it comes with the ID No – 1,2,3. It holds for 15 seconds. But in the meantime when I run the 2nd query in another query analyzer, it also come ID NO – 1,2,3. But it should not come becasue the first 3 rows is already locked. It should come ID No -4,5,6.
How can I do this?
Hey Faisal,
I have same issue with my sql, Have you solved it ? If yes please let me know how?
Hi Pinal,
Is NO LOCK default to a SELECT statement in SQL Server 2000?
No it is not.
Hi Pinal,
Is NO LOCK the default behaviour for a SELECT statement in SQL Server 2000?
Regards,
Ray
No be default it is a shared lock. You need to specify NOLOCK if you want
sir when we create a new table we want table lock that qurry
sir when we create a new table we want table lock that qurry with example
Hi Sir,
i like to read your article about lock in sql server,
but could you give the good solution for resolve this problem,
if the lock begin,
Thankyou for your answer
Rahmat