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 Dave,
I am not a DBA and we do not have one on staff but have a huge DB that we are monitoring with an app and it gets about 750 dblocks several times an hour. Your article mentions these so I am asking if you know of any further sites that can explain these better and what the ramifications are for these many dblocks on users trying to access the data.
Hi Pinal sir,
I am updating 693000 files. While updating I am also selecting 2640 record from the same table in a another query analyser.
I noted that when i fire only select statemnt it took 0 second to fetch the record and if i select along with Update statement, it will increase the time. What should i do to prevent it. Can you please help me??
With MSSQL 2008 the syntax of LOCK is like this
Examples:
SELECT OrderID
FROM Orders WITH (ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000
UPDATE Products WITH (NOLOCK)
SET ProductCat = ‘Machine’
WHERE ProductSubCat = ‘Mac’
Note the parenthesis “WITH (LOCKTYPE) “
Hello Sir,
Need solution to an important problem.
The Scenario is Hotel Management.
The Rooms selected by one user, shoud not be displayed to other. How to achieve this without locks.
Very very urgent.
Please reply
Hi Pinal,
Your article is very interesting and I am a regular visitor of your wonderful site.
What Imran Mohammed has written creates a confusion about the default isolation level.
TRANSACTION ISOLATION LEVEL
level 0. READ COMMITTED
level 1. READ UNCOMMITTED
level 2. REPEATABLE READ
level 3. SERIALIZABLE
“level1 is the default isolation level of the database.”
BOL states that “READ COMMITTED” is the default isolation level.
Thanks,
\\K
I am seeing a lot of Locks from SQL server 2005 in my application logs,
How can I delete/remove this locks?
Hi Pinal,
We have a financial accounting application where in we want to implement a lock as under:
Suppose user 1 selects a particual voucher for edit, we want to lock in such a way that if any other user tries accessing the same voucher, they get an error message stating ‘voucher is locked, pls wait’. Once user1 commits the voucher, the lock gets released and becomes available for other users.
Our application development platform is .net 2003, Framework 1.1 with SQL Server 2005 as the RDBMS. We use ADO.Net for DB interactions. We user VB.Net or C#.Net coding.
Pls let me know what is the best way to implement this type of locks.
Thx in advance
Chandru
hi all,
i am executing the below query in sql server 2005.
In the table structure is:
sportsid int 4
createdon datetime 8
Query:
SELECT * FROM tblblogs(NOLOCK) WHERE sportsid=2
Result:
16 rows affected.
SELECT * FROM tblblogs(NOLOCK) WHERE sportsid=2 and createdon=’2009-04-10 00:00:00′
Results:
No records displayed.
Please provide me the solution.
here instead of created on you should write created sdate=’20102204′ and created stime=’10:08:41′
pls make sure the format of time and date u gace in your coding and follow the procedure.
My name is Brian and i have a project to do. I need your support, this is the problem:
I have a multithread client with 30 threads that execute same Stored Procedure (ex. getproducts ) that procedure return 10 diferent products from the products table, i want every thread get diferent products. I try including Serializable Transaction Lock Level and tons of deadlocks were found, my question is , what lock level i need to set in the stored procedure in order to get per thread 10 diferents products..
— procedure getproducts —
set transaction isolation level serializable
select top 10 productID from products
where checked=0
update top (10) products
set checked=1
where checked=0
— end procedure
any idea?
Would just like to mention using SS2005 that the statement examples above with the general syntax:
Select * from table_name (with lock_type)
failed to execute correctly. However the following form worked:
Select * from table_name with (lock_type)
Perhaps this is a syntax difference between SS2000 and 2005, I don’t know.
Yours faithfully,
Hi sir!
i don’t know the syntax lock table, please tell me detail about it and please give me an example.thanks a lot.
Hi Pinal
Would (NOLOCK) work with Link Server in SQL 2005?
Best Regards
Vano
hi
this is krishna i am beginner to sqlserver .
i wnat to write trigger for attandance table i.e when
attendance_dategetdate() u can’t update
plz send urgent required…….
Hi,
I have two procesess running parally..one is removing and inserting data in a table..and same time other process is try to select it from the same table….
it ends up locking the table…. how to avoid this…
Thanks..
Hi Pinal,
I want to lock all table while executing a SP. Is it possible? If yes then how?
Hi Pinal,
basically my application having two pages
1)to view booked tickets
2)to view detail of ticket and update the ticket details
i want to use row level locing when one dispatcher
pnrno for updation no others can view that pnr
i tried this using your examples but total table is getting locked
can u give some examples
Great article, but my question is by default does SQL Server lock records? This will drive some issues we are having with our database and response time, and error problems.
Thanks,
Tim
I’m confused. I found your site and read about NOLOCK which you say is the default for SELECT and does not apply to INSERT, UPDATE, and DELETE. Then the example shows UPDATE Products (WITH NOLOCK)
-> If it’s the default for SELECT and not valid for anything else, then when would one use it?
-> Why does the UPDATE example show (WITH NOLOCK) if it’s not valid for update.
-> I thought the syntax was WITH (NOLOCK) not (WITH NOLOCK)
Your site says…
=======================
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:
…
UPDATE Products (WITH NOLOCK)
SET ProductCat = ‘Machine’
WHERE ProductSubCat = ‘Mac’
==========================
hi Pinal,
i am creating a report on reporting service using stored procedure and deploying it on the web via asp.net
The reason i need it is becoz i want the client run the report and if he is satisfied with the report of that day, the report of that day needs to be locked i.e nobody can access the same report of that date again..
can you plz help me. i even don’t know whether its possible or not
Hi Pinal,
The following code gives the range of the vehicles which have disposal date later of end of august, 2009 and delivery date less than september 2009
AND ((AA.DISPOSAL_DATE > ‘20090831’)
OR (AA.STATUS_FIELD=’IN SERVICE’
AND AA.DELIVERY_DATE < '20091001'))
Since this is the specification for september month. i want to do it for each month before and after. I am not taking any month and year parameters.
I should do in inside the stored procedure where above code also lies.
HOW CAN I DO IT PLZ? HELP ME