Locking Hints and Examples are as follows. The usage of them is same same but 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 update it later with the assurance that the data has not changed since 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'
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
Hello Sir,
If you write article about dirty read along with this article it will be good to read it.
Your articles are good read. We really enjoy reading them. We are small group of people from Chili. We need help getting publicity or international platform. You seem to know people. How about partnership?
Alisorab
Alisorab,
Thank you very much for your response. I will sure write about dirty read. I will include my thoughts about locks along with that article.
1) Your IP address indicates your comment is from Texas.
2) Your email address does not work.
Regarding your question. I am sure interested in helping any good product. Send me details about your product. If I believe my readers will have interest in your product I will publish my review on this blog.
My view and reviews are my own and I desire no pay or accept any pay for them. If I do not agree with product or review. I do not publish them as my faith is about positive attitude towards SQL.
Regards,
Pinal Dave
(http://www.SQLAuthority.com)
Hi Pinal,
Well, I have one query regarding locking hints.
Q: if one user accessing the 10 records of table.
if at the same time, 2nd user trying to access those 10
records, he should not get those 10 records.
i.e even 2nd user can’t read those 10 records.
is it possible using Lock hints?
I have used Row level lock, but in tht one can read those records. I want to restricts those records for read also.
Any user cant read those records.
Hi Kaushal,
You can use HOLDLOCK for this case.
This will not let second user read those records which are with holdlock with first user.
Regards,
Pinal Dave
(http://www.SQLAuthority.com)
Hi Pinal,
I find you page very well organized. Your effort is truly appreciated.
I am looking for some advice on the usage of LOCKING HINTS.
My interest lies more on the drawbacks of each type of locking hint specially when used by a network based enterprise application.
Can two people over the network acess the same procedures parallel to each other…and if so what impact will it have on my data concurrency
Hi,
how wud I aply a read lock to a settings or config table.
what I have to do is, to get a value of a field from the table “Settings” (suppose the field name is NumItems)
I read it, insert that value into another table inventoryand then i decrement this value by 1 in the Settings table.
Pseudo code of what i reckon should be
readlock (on Settings)
update inventory set numitems = (select NumItems from Settings);
update Settings set NumItems = NumItems -1 ;
release lock
I need Help, with a code, I made a store procedure that build a code with 7 letters, the 4 digit of year and a number, but always repeat codes
BEGIN TRANSACTION
INSERT INTO dbo.aincidente (
primer, segundo, tercer, cuarto, quinto,
fecha, dia, hora, fechareporte, horareporte,
caso,
correlativo,
forma_conocimiento, no_oficio, situacion_delito,
delito_contra, tipo_delictivo, area, departamento, municipio,
canton_hecho, caserio_hecho, colonia_hecho, comunidad, calle,
avenida, barrio, no_casa, complemento, sector,
x, y, gps, condiciontiempo, condicionlugar,
tipo_escena, tipo_lugar, tlugar_especifico, movil, circunstancia,
circunstanciaespecifica, tipo_violencia, nombreplan, novedad, creado_por,
reportado
)
select
@primer, @segundo, @tercer, @cuarto, @quinto,
@fecha, @dia, @hora, @fechareporte, @horareporte,
@hasc+@abrev+@ano+’-'+ replicate(‘0′,7-len(convert(varchar,isnull(MAX(correlativo)+1,1))))+ convert(varchar,isnull(MAX(correlativo)+1,1)),
isnull(MAX(correlativo)+1,1),
@forma_conocimiento, @no_oficio, @situacion_delito,
@delito_contra, @tipo_delictivo, @area, @departamento, @municipio,
@canton_hecho, {fn UCASE(@caserio_hecho)}, {fn UCASE(@colonia_hecho)}, {fn UCASE(@comunidad)}, {fn UCASE(@calle)},
{fn UCASE(@avenida)}, {fn UCASE(@barrio)}, {fn UCASE(@no_casa)}, {fn UCASE(@complemento)}, @sector,
@x, @y, @gps, @condiciontiempo, @condicionlugar,
@tipo_escena, @tipo_lugar, @tlugar_especifico, @movil, @circunstancia,
@circunstanciaespecifica, @tipo_violencia, @nombreplan, {fn UCASE(@novedad)}, @creado_por,
@reportado
from aincidente WITH (XLOCK, HOLDLOCK)
where delito_contra=@delito_contra
and municipio=@municipio
and DATEPART(year, fecha)= datepart(year,@fecha)
COMMIT TRANSACTION
Hi Pinal,
I m using HOLDLOCK in my application, The code is given below
BEGIN TRAN
SELECT * FROM test1
WITH (HOLDLOCK, ROWLOCK)
WHERE Name = ‘Rahul4′
update test1 set Name = ‘Rahul6′
where Name=’Rahul5′
COMMIT TRAN
Now, if a second user tries to hit the same record before COMMIT TRAN has executed, then the control waits until the COMMIT TRAN has executed on the first user.
My question is that, Is there a way I can stop the second user from hitting the same record by checking if that record has been already locked by the first user?
Hi,
I need one help..
I want to compare the data among various rows in single table..
any pointers how can we do that?
thanx in advance
Use the READPAST hint
Hi,
thanks a lot for providing the knowledge about the basic types of locks available in SQL 2005.
it’ld be great if I can have complete tutorial for Locking in SQL server 2005 including performance issues.
thnx in advance.
Sir,
In my Visual basic 6.0 program I am executing a stored procedure, which writes result to a table…If at a time more than one person executes that SP, the table content will be mixed up. So I want to lock a table till first user releases the lock…like
Lock Table (T_Details)
—I will do neccessary actions here.
— ..
—..
Unlock (T_Details)
Please tell me the solution for this with a small example. Please URGENT…
Ramesh Sambari
its very good article
my problem was resolved
thankyou for u article
Would it be possible to provide a complete tutorial on SQL Server 2005 Locking….
Prashanth
Solved my problem. Thanks
Hi Pinal
I want apply Lock on Table Row beacuse of User given Password not matched with existed Data.In this application User has three chance to send request to database but after third time i want provide Lock for that row.
Please reply
hi
i nead that indicate a deallock in sqlserver(query)
please help me
sincerely
I v got a website using sql server 2005 when my ws reach 1000 users at the same time I start getting deadlocks… I have a vb forum installed on the same server when I try using it It’s working very well.. do I have to set everything to nolock
How Can I resolve this issue ? is it from the application ?
Please help.
thanks…
Pls, give me example to create table using with all the locks
I have a question on HoldLock. Currently in HOLDLOCK syntax it is expecting a parenthesis for SQL 2005 and for SQL 2000 we do not need Parenthesis… I have third party code which do not expect Parenthesis for HOLDLOCK. How do I accommodate this feature for both SQL 2000 and SQL 2005??? Your response is much appreciated.
Hi Pinal,
I have a which reconciles data and insert more then 100 thousand records to a table. I don’t want this job to acquire X table lock on the table. I tried running this insert with RowLock and PAGlock, but it got escalated to TAB lock.
Is their a way to avoid TAB lock here. I don’t want to stop users from reading and inserting to this table, while this job is inserting huge data, which takes quite a good time to complete.
Looking forward to a quick help
Thanks,
Prashant
Hi Pinal,
UPDATE Products (WITH rowlock)
SET ProductCat = ‘Man’
WHERE ProductSubCat = ‘Mac’
UPDATE Products (WITH rowlock)
SET ProductCat = ‘Machine’
WHERE ProductSubCat = ‘Mac’
just had a interesting question posted to me .
what will happen if both these queries are run by the users at the same time (no deviation even for a microsecond).
Which Value will be updated?
could you please help with this Query…..
Thanks
Nagaraj
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.
[...] Imran Mohammed answered in great depth to this question. I personally enjoyed it very much. [...]
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
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
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’
[...] SQL SERVER – 2005 Locking Hints and Examples [...]
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
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.
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
Dada,
You are great.
@Suman
Pinal has an article on finding the first day of the current month here: http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/
Pinal/Imran,
I am little bit confused with this concept and explanation of Imran
begin tran T1;
insert into example1 with(xlock)
values ( 1, ‘example’);
select * from example1
I think my select query should not show any data as transaction is in xlock but it shows inserted record.