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
The correct syntax for the Rowlock and Nolock examples are as follows:
SELECT OrderID
FROM Orders WITH (ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000
UPDATE Products WITH (NOLOCK)
SET ProductCat = ‘Machine’
WHERE ProductSubCat = ‘Mac’
If your program has issues with lock contention, fix your code. Using the NOLOCK hint is not an answer. In fact, adding NOLOCK to a place that has known contention almost guarantees problems at some point. NOLOCK is supposed to be used were you are guaranteed not to have contention (so it does not waste the overhead to check). It’s not even allowed in Oracle and it is completely overrated as a performance booster in SQL Server.
In summary, the places where it is OK to use, there is not a big difference in performance. The places where it does have an impact are usually exactly the places it will cause problems if you use it.
Think of it this way. There are two bars and you search for weapons at both. In one where there are a lot of thugs coming in, the search process is slow because you find things. Eliminating the backup by just not searching is not a solution
At the other bar the patrons are less troublesome and weapaons are never found. You can speed up any potential delay by not checking for weapons without a problem as it is not necessary. By the same token, if everyone comes in the door clean, you are likely to have a backup at the door as the weapons search is quick.
There are some comments on the use of NOLOCK in this thread that are dead wrong. NOLOCK should never, ever be used with update. The examples given doing so are terrible.
It is probably best just to not use NOLOCK at all
Hi Pinal,
My Question is we received the data regularly in one table and on same table we using bulky procedures for generate reports.
The issue is when the client send the data at the time when my procedures are also running so the client received the error message due to time out.
So kindly give me your vital suggestion.
I am using SQL server 2005.
Thanks in advance.
Hi Umar,
If the import process is slow then load the data into a temporary table first and then insert or update the main table from local temporary table.
In update and insert statemetns use the ROWLOCK hint. This will avoide table level lock and other processes could complete simultaneously.
If dirty read is acceptable then use the NOLOCK hint in SELECT statements of reports.
Regards,
Pinal Dave
Hi,
Is there any way that an insert statement can insert data into a db table without holding exclusive lock on the table?
We have a VB application 1 that inserts data in to set of 7 tables. VB application 2 selects data from these 7 tables. Though the app2 is trying to select different set of data, it has to wait till the app1 commits the transaction. Is there any workaround for this? We use SQL server2000.
Thanks,
Sree.
If you don’t mind dirty reads you can try give NOLOCK hint to your SELECT query.
But it could be that your tables are not indexed properly and DB is locking whole tables instead of just some rows. You could try to use sp_lock procedure to find out what locks the update operation is placing. Read more about sp_lock from here: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql?view=sql-server-2017
Then there is SNAPSHOT transaction isolation level where read operations are done against snapshot stored on tempdb. This enables that read and update operations are not conflicting each other. You can read more from here: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189122(v=sql.105)
how to unlock a view in sql server 2005
Hi Pinal,
Can i make Lock on SP or something like that so,
When User A executing SP – SP1, then USER B request For Executing SP1 is queued and will execute SP1 after User A finished Execution of SP1.
Hello Vishal,
There is no built-in functionality to setup such behavior. It’s not because Microsoft missed something but because there are other alternative (Lock hints and Isolation levels) to implement such requirements.
Regards,
Pinal Dave
Hi Pinal,
In my organization we are using WITH (NOLOCK) with every select query. I was thinking is there any way, we can set this WITH (NOLOCK) option for every select/Read operations at database level, So that we can avoid using WITH (NOLOCK) at every query!
Hello Tanmay,
Change the isolation level of database to READ UNCOMMITTED. Then you would not have to write WITH(NOLOCK) with each table in select queries.
Regards,
Pinal Dave
haii..
i need to konw one thing more about this locks..
whether my following sp is thread safe?
ALTER proc [dbo].[MoveMoney]
@toId int,@fromId int, @money float
As
Begin
if @money >0
BEGIN
BEGIN transaction
if(select accountBalance from tbl_Accounts with(XLOCK, SERIALIZABLE) where accountNumber=@fromId) >=@money
BEGIN
update tbl_Accounts set accountBalance -=@money where accountNumber=@fromId
update tbl_Accounts set accountBalance += @money where accountNumber=@toId
COMMIT transaction
END
END
END
thks & regards
nithin.
Hi Pinal,
This is regarding the dead Locks and locking i have changed my isolation to READ_COMMITTED_SNAPSHOT so that i can get deadlock and blocking issue resolved i know the impact of using this isolation i dnt have any issue as currently we are facing deadlocks and blocking issues in our database as there are 500 concurrent users accing the database and the database size in 500+ gb.
Please provide ur suggestions so that i can futher resolve the issues of deadlocks and blocking
If a table is locked for any reason, transaction begin but not comitted or rollback, how can it be unlocked?
I had one table which was in transaction and i did not rollback or comitted the transaction, that table locked but after an hour or so it unlocked.
I could not get the reason why it was remain locked for some time and how it unlocked?
I am writing a C# Program for an organization.I use Visual Studio 2008
and SQL server 2005 as its data base.
In that organization there are three or more employee which work with
the same program.
they want our program have this capability that when an employee
register a record to the database other employee be capable of viewing
that record. in the other work we want to have a shared database.
what shall we do?
Do we need to install and config a Domain(e.g. win 2003 server)? or
having a work group network is enough?
what would we do to meet this requirement ?
Best Regards
hi,
i want lock one row in a table using condition,
and i was tried the below query
SELECT OrderID
FROM Orders (WITH ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000
but its show
Incorrect syntax near the keyword ‘WITH’.
so give me an example for lock one row in a table .
Hi all,
Is there a way to clear the lock to table?
I read this article,
But the question is not answered.
I had this situation where, a table was locked, luckely I was able to ask the users to log off, and then detached and reattach the database.
I am sure there are many administrators out there who do not have my luxury, they cannot ask their users to log out and log in later. So there must be an option to control the locks, atleast when logged in as an admin.
Regards
Hello Pinal,
am using sql server 2005,
in my table the primary key is alpha numeric.
ex : ABC000001
ie if the first record is with ABC000001,then the next record has to be inserted with ABC000002.
For this, at the time of the insertion am getting the last record from table and incrementing it with 1 and then inserting. am using only queries (my boss restricted me to use the stored procedures)
my problem is when 2 or more users are trying to insert the record into the table they are getting the same value from the table (last record).so at the time of the insertion am getting the error bcoz primary key should be unique.
how to solve this problem.how to use the locks/transactions with sql queries.
please give me a example.
Thanks in Advance,
Nikhil
Hello Sir,
I am getting Error of
“Transaction (Process ID 57) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”
during execution of my sp in which i am doing updation on a table by using xml.
Below is my SP in which i am passing xml string:
EXEC sp_xml_preparedocument @idoc1 OUTPUT, @str1
Update Booking_Update set InformDate = GetDate(),
IndStatus = t1.Field13,IndBookNo = t1.Field3,
IndBookDate = convert(char(25),t1.Field4,102),
LastDeliveryDate =convert(char(25),t1.Field5 ,102),
UpdateFlag = 2 ,DownloadStatus = 1
From (select Field13,Field3,Field4,Field5,Field9,Field16 from OPENXML (@idoc1, ‘/NewDataSet/Table1’,2) WITH (Field13 varchar(10), Field3 bigint, Field4 Varchar(25), Field5 varchar(25), Field9 bigint,Field16 bigint)) t1, Booking_Info with(nolock) INNER JOIN Booking_Update with(nolock)
On Booking_Info.BookId=Booking_Update.BookId
where Booking_Update.BookId = t1.Field16
I have done indexing on my table and also include with(nolock) in all select queries.
If you have any idea than please tell me .
Thanks.
[edited personal emails and other details]
Hi pinal,
I have small doubt. In my project i want to lock the data if any user updates so that second user can not update same record at same time.
Can u please suggest which lock i can use?
i have stored Procedute to update the record and select stat for pulling the data.
hi pinal ,
i have huge doubt in sql server
1.is possible to encrypt the tables
2.you say “yes” tell me how it’s possible with examples
3.how to protect database from unauthority person
4.what is table lock explain with detailed queries
hi,
i m tarun. i want to clear myself about sql pls mail me