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, I have a Java code for Oracle with which I keep a file in the database, the file is stored in a Blob field, for this purpose I use a “select for update” and put the connection in “autocommit (false ) “to keep the channel open to that record, after that I keep that result in a Blob and then write content to an OutputStream, then do” commit “and put the connection back to” autocommit (true) “.
The question is, how I can do a “select for update” for SQL Server 2008? I’ve been reading that possibly with a “WITH (ROWLOCK)” in my select, but if I do so, after you commit to writing the OutputStream object is unlocked automatically? or what is the procedure?
Thanks for listening.
udaya’s solution to the above mentioned problem
select deptno
,SUM(case when gender = ‘M’ then sal else 0 end) ‘Male total sal’
,SUM(case when gender = ‘F’ then sal else 0 end) ‘female total sal’
from ##dept
group by deptno
Hi, Can we SELECT the records from row that holds ROWLOCK?
Scenario :
First transaction update the record
e.q.: Update employee WITH(ROWLOCK) SET empname=’xyz’ WHERE empid=10
And before completing above transaction i need to SELECT data for same record.
e.q. : SELECT * FROM employee(NOLOCK) WHERE empid=10
HI, can we use ROWLOCK, XLOCK ,HOLDLOCK in Select and update commands in same time
Actually I want lock&hold row from other users until current user update it
need your assistance & advice
For exmp:
BEGIN TRAN
SELECT * from tableA WITH (ROWLOCK, XLOCK ,HOLDLOCK) WHERE Recono=getRecono and post =0
SET NOCOUNT ON
UPDATETableA WITH (ROWLOCK, XLOCK ,HOLDLOCK) SET post = 1 WHERE Recono=?getRecono and post =0
COMMIT TRAN
hi sir
you provide logical quuestion fro me in sql..i like your stamenets
thanks
farhan
my hold lock not work properly
how to handle the xlock from multiple access
thanks in advance
try
{
con1.ConnectionString = SQLConnectionString;
if (con1.State == ConnectionState.Closed)
{
con1.Open();
}
trans = con1.BeginTransaction();
SqlCommand cmd1 = new SqlCommand(“select name from testinfo WITH(XLOCK) where ID='” + textBox1.Text + “‘”, con1,trans);
SqlDataReader dr1 = cmd1.ExecuteReader();
if (dr1.HasRows)
{
while (dr1.Read())
{
label1.Text = dr1[“name”].ToString();
}
}
catch (Exception ee)
{
trans.Commit();
con1.Close();
MessageBox.Show(“Access By Other User “);
}
}
this is working properly ,it generate an exception which is correct , gives an message “access by another user ” ,but this system define exception required lot of time min 10 sec it take , so i want to find any other condition which will show that this row is access by another user.is there is any other parameter so i can identify that this row is exclusive lock by other user.
Hi,
I have a query that runs hourly but on certain times it return erroneous results and was wondering if it could be caused by some locking issue.
My query runs on a table and filters the data by date and time but on certain times it return resultset that are not in the filtered date and time and no errors. Do you think this could be caused by locking issues?
Thank you,
Geo
Hi,
I want to lock the database with read only then i will update the database like adding data tables , adding columns to existing tables and set default values to columns .
Will you please let me know how can i write query for locking and unlock also.
1)I lock the database.
2)update database with required changes(mean time no one able to update the database)
3)I will release lock.
Regards,
Chakradhar.
Hello sir ,
Can you please help me ?
My question is how to lock and unlock the table and maintain which user is lock first so when user unlock the table then other user can insert the row ?
Hello sir ,
Can you please tell me how to unlock the table with example ?
hello sir,
can you please help me ?
My Question :- i want to fetch lastid from a table ,and then against condition i will update or insert record. when two user get same id it will not work so i want to lock the table when user fetch lastid and when user update/insert the record then it should unlock the table
Hi,
we are using following query in our code ; except DESC1 all other values are getting updated
STATEMENT : “Update Table1 with (ROWLOCK) set DESC1 = ?, DESC2 = ?, INSTR =?, GRP =? where ID = ? ”
Can you Please, let me know what could be the reason ? why DESC1 is not getting update at all times;
**when i applied the row lock in one of my store procedure on delete row in a table..while processing the procedure with one more thread i am calling the same table different row delete..but i am getting lock request time out error.**
23:20:21,600 ERROR [com.newscale.bfw.udkernel.util.UdPersistenceManagerImpl] (org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-3) inputParams = ProcessName=ReqDataPoller,
TenantID=1
23:20:21,601 ERROR [com.newscale.bfw.udkernel.util.UdPersistenceManagerImpl] (org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-3) udconfig.defdbsession.delete; uncategorized SQLException for SQL [DELETE FROM DefDatabaseSession WHERE ProcessName = ?]; SQL state [HY000]; error code [1222]; [newscale][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.; nested exception is java.sql.SQLException: [newscale][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.: com.newscale.bfw.udkernel.kernel.UdKernelException: udconfig.defdbsession.delete; uncategorized SQLException for SQL [DELETE FROM DefDatabaseSession WHERE ProcessName = ?]; SQL state [HY000]; error code [1222]; [newscale][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.; nested exception is java.sql.SQLException: [newscale][SQLServer JDBC Driver][SQLServer]Lock request time out period exceeded.
at com.newscale.bfw.udkernel.kernel.UdKernelBean.service(UdKernelBean.java:86) [newscale_udkernel.jar:Development Build]
at com.newscale.bfw.udkernel.kernel.UdKernelBean.getUdSqlResponse(UdKernelBean.java:653) [newscale_udkernel.jar:Development Build]
at com.newscale.bfw.udkernel.kernel.UdKernelBean.getUdSqlResponse(UdKernelBean.java:611) [newscale_udkernel.jar:Development Build]
at com.newscale.bfw.udkernel.kernel.UdKernelBean.doUpdate(UdKernelBean.java:499) [newscale_udkernel.jar:Development Build]
at com.newscale.bfw.udkernel.util.UdPersistenceManagerImpl.doUpdate(UdPersistenceManagerImpl.java:1007) [newscale_udkernel.jar:Development Build]
at com.newscale.bfw.udkernel.util.UdPersistenceManagerImpl.doDelete(UdPersistenceManagerImpl.java:1047) [newscale_udkernel.jar:Development Build]
at com.newscale.bfw.dam.impl.DefDatabaseSessionImpl.delete(DefDatabaseSessionImpl.java:150) [newscale_dam.jar:Development Build]
at com.newscale.api.poller.BasePollerSessionUtil.deleteSessionInfo(BasePollerSessionUtil.java:218) [newscale_rapidelegate.jar:]
at com.newscale.api.poller.BasePollerSessionUtil.deletePollerSessionInfo(BasePollerSessionUtil.java:209) [newscale_rapidelegate.jar:]
at com.newscale.api.reqdata.requisition.RequisitionsDataTriggerListener.triggerComplete(RequisitionsDataTriggerListener.java:75) [newscale_rapidelegate.jar:]
at org.quartz.core.QuartzScheduler.notifyTriggerListenersComplete(QuartzScheduler.java:1908) [quartz-1.8.5.jar:]
at org.quartz.core.JobRunShell.notifyTriggerListenersComplete(JobRunShell.java:391) [quartz-1.8.5.jar:]
at org.quartz.core.JobRunShell.run(JobRunShell.java:260) [quartz-1.8.5.jar:]
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549) [quartz-1.8.5.jar:]
**row lock should lock that row in my store procedure and i want to continue parallely delete some other rows in the same table with other threads.
can any one help me ASAP**
Hello Sir ,
I am new to programming and I have some queries regarding SQL lock :
1. How other users will know that some lock is applied and they can not read/modify data (if I need to inform them)
2. How to maintain which user is presently accessing the data.
Any help will be appreciated :).
Hi Pinal,
I have a couple of questions regarding “ROWLOCK” table hint .
As per Microsoft documentation specifying “ROWLOCK” doesn’t give guarantee of having “Key” level lock only. And Query optimizer starts with acquiring “rowlock” and then if require escalates it to “TAB”.
In what situation we need following queries
1.UPDATE [TABLE] WITH(ROWLOCK) SET……..
2.SELECT * FROM [TABLE] WITH(ROWLOCK)
Awesome website you have here but I was curious if you knew of
any user discussion forums that cover the same topics discussed in this article?
I’d really like to be a part of online community where I can get feed-back from
other experienced individuals that share the same interest.
If you have any suggestions, please let me know.
Appreciate it!
i want use “NOLOCK” with update and delete. is it possible
Hello sir ,
Which Hint should i use in SQL SERVER , As an Alternative for Oracle’s select query with ‘for update nowait’,,,
I don’t know what the does in Oracle so can’t tell you equivalent.