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
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?
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
Hi, I have a Java code for Oracle with which I keep a file in the database, the file is stored in a Blob field, for this purpose I use a “select for update” and put the connection in “autocommit (false ) “to keep the channel open to that record, after that I keep that result in a Blob and then write content to an OutputStream, then do” commit “and put the connection back to” autocommit (true) “.
The question is, how I can do a “select for update” for SQL Server 2008? I’ve been reading that possibly with a “WITH (ROWLOCK)” in my select, but if I do so, after you commit to writing the OutputStream object is unlocked automatically? or what is the procedure?
Thanks for listening.
udaya’s solution to the above mentioned problem
select deptno
,SUM(case when gender = ‘M’ then sal else 0 end) ‘Male total sal’
,SUM(case when gender = ‘F’ then sal else 0 end) ‘female total sal’
from ##dept
group by deptno
Hi, Can we SELECT the records from row that holds ROWLOCK?
Scenario :
First transaction update the record
e.q.: Update employee WITH(ROWLOCK) SET empname=’xyz’ WHERE empid=10
And before completing above transaction i need to SELECT data for same record.
e.q. : SELECT * FROM employee(NOLOCK) WHERE empid=10