SQL SERVER – 2005 Locking Hints and Examples

Locking Hints and Examples are as follows. The usage of them is same same but 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 update it later with the assurance that the data has not changed since 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'

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
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.

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

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

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

    Reply
  • mohammad farhan
    March 26, 2013 7:21 pm

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

    Reply
  • my hold lock not work properly

    Reply
  • how to handle the xlock from multiple access
    thanks in advance

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

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

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

    Reply
  • 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 ?

    Reply
  • Hello sir ,
    Can you please tell me how to unlock the table with example ?

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

    Reply
  • 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;

    Reply
  • **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**

    Reply
  • 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 :).

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

    Reply
  • 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!

    Reply
  • i want use “NOLOCK” with update and delete. is it possible

    Reply
  • Hello sir ,
    Which Hint should i use in SQL SERVER , As an Alternative for Oracle’s select query with ‘for update nowait’,,,

    Reply

Leave a Reply

Menu