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
Dada,
You are great.
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.
Hi Pinal,
I am using C# at the front end and my requirement is if any customer care executive opened any Customer specific detail for edit and other executive also want to open that customer detail at the same time, we can restrict him.
For this I am using UDLOCK, But when second executive want to access same record he is getting timeout, while I want to get any specific error or error code so that I can show error msg accordingly.
Can you please help me regarding this.
Hi, There is worng on your sample
UPDATE saleslt.product WITH (NOLOCK) –WITH (ROWLOCK) — — –(WITH NOLOCK)
SET name = ‘Machine’
WHERE productid = ‘680’
Msg 1065, Level 15, State 1, Line 15
The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE or DELETE statements.
Hi Pinal,
We are working in the development enviornment.
Currently the situation is like this we have multiple users accessing the same database for development purpose. This has cause sometimes blocking issues.
I figured out the locking type i.e PAGE level at some case where as KEY at some cases.
As per my understanding SQL 2005 support ROW level locking by default. but i can’t see this level of locking in the Activity monitor as well as using DMV’s.
Any suggestions?
Hi there,
First of all thank you for a very useful series of articles.
Next, I’m sure someone else has mentioned this previously but in your examples for this article your syntax for the locking hints is not quite right – you write it as (WITH NOLOCK), but the correct syntax is WITH (NOLOCK).
Finally, I’m wondering about the default locking behavior for SELECT statements. You state that NOLOCK is the default, which would seem logicial for a SELECT although I would think it would really depend on the transaction.
I have just demonstrated to myself a situation where a table is locked against reads (due to a seperate, faulty piece of code) and a SELECT against that table cannot complete, yet when I add a NOLOCK hint it does complete.
Thanks again
Chris
Hello Chris,
Thank you for your corrections.
The NOLOCK hint is only applicable for SELECT statement and it is default in READ UNCOMMITTED isolation level. For READ COMMITTTED isolation level this is not default.
Regards,
Pinal Dave
Hi
i want to lock a particular row , so other user cannot read this row
how????
Please help me
Hello Shilpesh,
Lock is an sql object that can be used by sql engine only. You can create view to restrict the access of perticuler rows.
Regards,
Pinal Dave
hi ,
I need one help
how to singal database lock of sql server 2005 ya
how to lock db Table , sp , view ,
Plz Tell me
Hello Pankaj,
If you want that only dbo role member can access the database then alter database to set RESTRICTED_USER.
If you want that only one user at a time can connect to database then alter database to SINGLE_USER
If want to close the database then offline the database.
Regards,
Pinal Dave
Hello Pinal dave
mene ek college ka setup bnaya he , backend me sql server 2005 ki database use kar raha ,me yah chata hu meri database ko me lock kar do, sa user bhi database ko open nahi kar sake ,ya koi bhi user database ko open kare to user name password requirment ho ,
plz help me
hi
I need
how to two instance create in sql server 2005
Hello Pankaj,
You will have to run SQL Server installation setup every time to install a new SQL Server instance.
Regards,
Pinal Dave
hi
you help me so thanks
hello sir
I have complete install sql server 2005 , i need new instance install use by other software
Plz tell me what is software use in install new instance
hello
delete windows user for sql server 2005 command line how to ?
Hello Pankaj,
Use the DROP LOGIN command as below:
DROP LOGIN login_name
Regards,
Pinal Dave
Hello Friends,
I am using asp.net, .NET 3.5, C#, and SQL Server Express 2005.
I have created a stored procedure in SQL, and when I run SP from SQL server it takes less than 1 second to return results. I have also tried that query in query analyzer and it also gives me results in less than 1 second. But when I try to call this SP from .NET (C#), it takes a long time, and then gives a timeout error.
//*******************************1. ArtifactEntityDetails*************************************
SqlCommand cmd_delete = new SqlCommand(@”update ArtifactEntityDetails set OriginPlaceId=null where OriginPlaceId=@id” , con);
cmd_delete.Parameters.AddWithValue(“@id” , vCity.ENTITYID);
cmd_delete.Transaction = Tran;
cmd_delete.ExecuteNonQuery();
cmd_delete.CommandText = @”update ArtifactEntityDetails set LocationID=null where LocationID=@id”;
cmd_delete.ExecuteNonQuery();
//******************************* 2.AwardEntityDetail *************************************
cmd_delete.CommandText = @”update AwardEntityDetail set LocationPlaceId=null where LocationPlaceId=@id”;
cmd_delete.ExecuteNonQuery();//Timeout Error
Hi Pinal,
You know everything of SQL Server. Your
article is really useful. thanks