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, Pinal
i want to know that in web application having mutiple server.
which locking is better (between these to Optimistic and Pessimistic ) & why ?
Please give me answer with some details also.
Thanks,
Virendra
Any way you can provide a code or direction on how to remove a table lock that was placed on a particular table by some transaction?
Thank you.
Pinal,
Thank you for all your wonderful articles.
Here is our problem.
I am developing an application in ASP.NET / SQL Server 2005 and want to use some triggers to fill “snap shot table”.
Most recent information about a company is spread over multiple tables with 100s of fields. Ie need a search function to search on very basic company information (such as address). My idea is to create a small table with these 15-20 basic fileds and add triggers on detail tables to update this small table as necessary. Do you think there is any draw back in using triggers in this case? How will the locking work…lets say if some one is reading the search table and at the same time a trigger files to update the search table…?
Can u tell us something about how to find which sql table is having the lock and of what type. also please tell us how to remove a lock from a locked table
thanks
Priyank
@Priyank
In SQL Server 2000 (Enterprise Manager)
1. Expand server – management-currentActivity-expand
locks/processid and you will be able to see all the locks related information.
2.Expand server – management-currentActivity-expand Locks/object you can see locks by object information.
In SQL Server 2005 (SSMS, object Explorer)
Expand-server-management-double click Activity Monitor.
on left side you have three options to choose from, select those options and you can see all the locks related information.
run this stored procedure in the database.
1. sp_lock
to know the running process in the sql server, run this query,
2. select * from sysprocesses ( in sql server 2000)
3. select * from sys.sysprocesses ( in sql server 2005)
4. sp_who
5. sp_who2 will also give you some good information.
To work around the locks, you can run profiler to check which query is is creating a lock and if that is necessary.
Types of locks on object level, ( general idea)
Database : Database.
Extent : Contiguous group of eight data pages or index pages.
Key: Row lock within an index.
Page: 8-kilobyte (KB) data page or index page.
RID :Row ID. Used to lock a single row within a table.
Table: Entire table, including all data and indexes.
Types of locks;
Shared (S) – more than one Query can access the object.
Exclusive lock (X) – only one Query can access the object.
Update lock (U)
Intent share (IS)
Intent Exclusive (IX)
Just to give you a brief idea about locks, We have something called as transaction levels in sql server databases.
TRANSACTION ISOLATION LEVEL
level 0. READ COMMITTED
level 1. READ UNCOMMITTED
level 2. REPEATABLE READ
level 3. SERIALIZABLE
level 0 is the lowest level isloation level, if your database is set in this isolation level, no query will lock any resources,Under this level, there will be no locks on the database, not even shared locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
This data will also read uncommitted data. Data which you have not comitted, you can still read that data.
level1 is the default isolation level of the database.
Under this category you will not be able to read uncomitted data, this is also called as dirty data. Under this we will have shared locks.
As the level increases the locks also increases. The highest is the serializable.
To make you understand in detail, lets see an example of what is committed data and what is uncomitted data.
use pubs
create table example1 ( eid int, ename varchar(10))
begin tran T1
insert into example1 values ( 1, ‘example’)
go
select * from example1 — this is uncomitted data.
The above is uncomitted transaction, because you started the transaction with a begin, you have to commit the transaction, untill then the transaction will not be uncommitted.
to commit the same transaction
commit tran T1
select * from example1 — this is committed data.
To check what is the current isolation level of your database, run this command,
Dbcc useroptions — check for isolation level.
If you dont want your query to put locks on objects you might want to use something like this,
select * from example1_1 with (nolock)
This will not keep any lock, not even a shared lock on the table.
This is indepth concept try looking BOL.
Hope this helps,
Imran.
Hi Pinal,
I am using manifold (a software for GIS data management) linked with SQL Server 2005. Basically all the data resides in SQL Server Project and Manifold gets linked with that data, renders it, and allows users to edit and use it in various ways. The problem is that when two or more people try to edit same record, it gets duplicated and the duplicate record remains there until manually deleted. I want to know if there is something that I can do to SQL Server so that I can lock records.
Regards.
Hello Pinal,
I would like to know about the Query Optimization technique. If you could give some technique, it will be very useful. More over, i need to fine tune few of our query in our project. Is it a good approach to include WITH (NOLOCK) command for Select statment for Query Performance.
Waiting for your reply.
Regards,
PRabhu
hi pinal,
i want brief explanation of locking.how to implement in our vb project.(row level and column level locking)
I have tried to use the syntax recommed for row locking/nolock shown above but my SQL 2005 developer edition 64bit tells me that it is only supported by the compact edition – can anybody help?
Are you saying that SELECT statements default to NOLOCK. Other material I have read indicates that adding NOLOCK will prevent SELECT statements from creating/responding to locks. Can you please clarify.
hi..
i want know about locking… and i want to know. how to implement in store procedures. Can you help me?
Hi Pinal,
I could not access one table some time. Please let us know why it was not accessible and also how to find out when it got locked and because what..
Thanks
Balaji
Hi,
Just need a help in getting the resources. My question is as follows:
How can I know what locks are running on which resource?
I am using sql server 2005.
Thanks
Krishna
Hi PD,
Would (NOLOCK) work with Link Server in SQL 2005?
Thanks,
PK
Hello Pinal,
I wanted to know if an updt lock hint would prevent conversion deadlocks,I am having an Update statement and a select statement , the update and select satements are causing a deadlock, Could I recomend an UIpdate Lock hint on the update statement?I could send you the locking event log and we could discuss further, I am pretty sure that what I ahve recomended is corrrect but my superieors tend to disagree, I wanted an Honest third party opinion.Can I send you the details via email?
Thanks
Ajay
when i am using this query in SqlServer 2005 Master database,error display
select * from projects
(WITH HOLDLOCK)where pj_id=1
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘WITH’.
Msg 319, Level 15, State 1, Line 2
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.
plz suggest what’s problem
thanks and regards
kapil gupta
here u mention as
select * from projects where pj_id= ‘1’ (with holdlock)
It will work;
select * from projects
WITH (HOLDLOCK)where pj_id=1
regards,
biju
It should be WITH(HoldLock)
select * from users (WITH ROWLOCK)
where userid=141
this command also give the following error in Sql Server 2005.error is
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘WITH’.
thanks and regards
kapil gupta
here u mention as
select * from users where userid=’141′(with rowlock)
select * from users (with rowlock) where userid=’141′
hii Sir,
i want to know about locks (their syntax,examples,..) in sql from the beginning.its not clear to me.how are they applied,some good examples,how shared and exclusive locks are used..Plzreply as soon as possible
regards
Neha
Hi Dave, reading your post above you said that NOLOCK does not apply to INSERT, UPDATE, and DELETE statements.
But you give this example:
UPDATE Products (WITH NOLOCK)
SET ProductCat = ‘Machine’
WHERE ProductSubCat = ‘Mac’
Hello Dave,
In SQL Server 2005 update If Exists Else Insert, there are two patterns : IF EXISTS … ELSE INSERT and UPDATE if @@rowcount = 0 INSERT. Which is better ?
1. IF EXISTS does two index search
2. UPDATE holds IX and X locks
Thank you,
Sharon