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,
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
hello sir,
the examples given above is this:
SELECT OrderID
FROM Orders (WITH ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000
UPDATE Products (WITH NOLOCK)
SET ProductCat = ‘Machine’
WHERE ProductSubCat = ‘Mac’
NOLOCK doesnt apply to update or delete or insert. can you please explain why NOLOCK was given along with update statement for the blog “SQL SERVER – 2005 Locking Hints and Examples”. Shouldnt we use ROWLOCK or UPDLOCK?
thanks
suba
Hello sir
I am new to sqlserver
when retriving data like
name gender sal deptno
df F 100.00 10
mf M 1100.00 10
if M 1010.00 10
uf M 1000.00 20
yf F 1200.00 20
gf F 1030.00 20
hf M 1005.00 30
ff F 1040.00 30
from this i want retrive
deptno maletotal sal female total sa
10 2110 100
20 1000 2230
30 1040 1005 plz send me query sir
Hello Sir,
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
is it possible to lock single table in sql server 2010
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 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
here the statement
UPDATE Products (WITH rowlock)
SET ProductCat = ‘Machine’
WHERE ProductSubCat = ‘Mac’
will be updated early bcoz due to alphabetical order