SQL SERVER – Locking Hints and Examples

Locking Hints and Examples are as follows. The usage of them is the same but the effect is different.


Use row-level locks when reading or modifying data.

Use page-level locks when reading or modifying data.

Use a table lock when reading or modifying data.

Use a database lock when reading or modifying data.

UPDLOCK reads data without blocking other readers, and updates it later with the assurance that the data has not changed since the last read.


Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.

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.

This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.


AND 2000
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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL Lock, SQL Scripts, SQL Server
Previous Post
SQL SERVER – SELECT vs. SET Performance Comparison
Next Post
SQL SERVER – Query to Retrieve the Nth Maximum Value

Related Posts

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.

  • Vishal Motwani
    January 19, 2013 11:01 am

    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

  • Shrikant Patil
    January 31, 2013 12:51 pm

    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:
    SELECT * from tableA WITH (ROWLOCK, XLOCK ,HOLDLOCK) WHERE Recono=getRecono and post =0
    UPDATETableA WITH (ROWLOCK, XLOCK ,HOLDLOCK) SET post = 1 WHERE Recono=?getRecono and post =0

  • mohammad farhan
    March 26, 2013 7:21 pm

    hi sir
    you provide logical quuestion fro me in sql..i like your stamenets

  • 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)

    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)

    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,


  • 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.


  • 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,
    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



  • 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’,,,


Leave a ReplyCancel reply

Exit mobile version