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 (http://blog.SQLAuthority.com)

181 thoughts on “SQL SERVER – 2005 Locking Hints and Examples

  1. Hello Sir,

    If you write article about dirty read along with this article it will be good to read it.

    Your articles are good read. We really enjoy reading them. We are small group of people from Chili. We need help getting publicity or international platform. You seem to know people. How about partnership?

    Alisorab

    Like

    • hello sir,

      the examples given above is this:

      SELECT OrderID
      FROM Orders (WITH ROWLOCK)
      WHERE OrderID BETWEEN 100
      AND 2000

      UPDATE Products (WITH NOLOCK)
      SET ProductCat = ‘Machine’
      WHERE ProductSubCat = ‘Mac’

      NOLOCK doesnt apply to update or delete or insert. can you please explain why NOLOCK was given along with update statement for the blog “SQL SERVER – 2005 Locking Hints and Examples”. Shouldnt we use ROWLOCK or UPDLOCK?

      thanks
      suba

      Like

    • Hello sir

      I am new to sqlserver
      when retriving data like
      name gender sal deptno
      df F 100.00 10
      mf M 1100.00 10
      if M 1010.00 10
      uf M 1000.00 20
      yf F 1200.00 20
      gf F 1030.00 20
      hf M 1005.00 30
      ff F 1040.00 30
      from this i want retrive
      deptno maletotal sal female total sa
      10 2110 100
      20 1000 2230
      30 1040 1005 plz send me query sir

      Like

    • Hello Sir,
      I Have some problem in my database SP. That is created by another person .Now he is not with us.He Lock the Stored procedure and other person can not modify this.Now I want to modify and check This Procedure.Can you help me how this is Possible?

      Thanks

      Like

  2. Alisorab,

    Thank you very much for your response. I will sure write about dirty read. I will include my thoughts about locks along with that article.

    1) Your IP address indicates your comment is from Texas.
    2) Your email address does not work.

    Regarding your question. I am sure interested in helping any good product. Send me details about your product. If I believe my readers will have interest in your product I will publish my review on this blog.

    My view and reviews are my own and I desire no pay or accept any pay for them. If I do not agree with product or review. I do not publish them as my faith is about positive attitude towards SQL.

    Regards,
    Pinal Dave
    (http://www.SQLAuthority.com)

    Like

    • hello pinal,

      the examples given above is this:

      SELECT OrderID
      FROM Orders (WITH ROWLOCK)
      WHERE OrderID BETWEEN 100
      AND 2000

      UPDATE Products (WITH NOLOCK)
      SET ProductCat = ‘Machine’
      WHERE ProductSubCat = ‘Mac’

      NOLOCK doesnt apply to update or delete or insert. can you please explain why NOLOCK was given along with update statement for the blog “SQL SERVER – 2005 Locking Hints and Examples”. Shouldnt we use ROWLOCK or UPDLOCK?

      thanks
      suba

      Like

  3. Hi Pinal,

    Well, I have one query regarding locking hints.

    Q: if one user accessing the 10 records of table.
    if at the same time, 2nd user trying to access those 10
    records, he should not get those 10 records.

    i.e even 2nd user can’t read those 10 records.
    is it possible using Lock hints?

    I have used Row level lock, but in tht one can read those records. I want to restricts those records for read also.
    Any user cant read those records.

    Like

    • Hi Pinal,

      We are having a problems with insert statements which locks the table while inserting the records and we cannot perform any other operations on that table at a time.. I have some questions regarding SQL Server 2000.

      1> if one person inserting some records into the table that table will be locked and another person cannot access records from that table.. or another person trying to perform update or selecting records from that particular table. its not happening and he has to wait. Wat to do ? please help me with this..

      2> is there any possibility that we can create duplicate table so that we can perform different operations like insert on one table and select or update on duplicated table…

      Like

  4. Hi Pinal,
    I find you page very well organized. Your effort is truly appreciated.
    I am looking for some advice on the usage of LOCKING HINTS.
    My interest lies more on the drawbacks of each type of locking hint specially when used by a network based enterprise application.
    Can two people over the network acess the same procedures parallel to each other…and if so what impact will it have on my data concurrency

    Like

  5. Hi,
    how wud I aply a read lock to a settings or config table.

    what I have to do is, to get a value of a field from the table “Settings” (suppose the field name is NumItems)
    I read it, insert that value into another table inventoryand then i decrement this value by 1 in the Settings table.

    Pseudo code of what i reckon should be

    readlock (on Settings)
    update inventory set numitems = (select NumItems from Settings);
    update Settings set NumItems = NumItems -1 ;
    release lock

    Like

  6. I need Help, with a code, I made a store procedure that build a code with 7 letters, the 4 digit of year and a number, but always repeat codes

    BEGIN TRANSACTION

    INSERT INTO dbo.aincidente (
    primer, segundo, tercer, cuarto, quinto,
    fecha, dia, hora, fechareporte, horareporte,
    caso,
    correlativo,
    forma_conocimiento, no_oficio, situacion_delito,
    delito_contra, tipo_delictivo, area, departamento, municipio,
    canton_hecho, caserio_hecho, colonia_hecho, comunidad, calle,
    avenida, barrio, no_casa, complemento, sector,
    x, y, gps, condiciontiempo, condicionlugar,
    tipo_escena, tipo_lugar, tlugar_especifico, movil, circunstancia,
    circunstanciaespecifica, tipo_violencia, nombreplan, novedad, creado_por,
    reportado
    )
    select
    @primer, @segundo, @tercer, @cuarto, @quinto,
    @fecha, @dia, @hora, @fechareporte, @horareporte,
    @hasc+@abrev+@ano+’-‘+ replicate(‘0′,7-len(convert(varchar,isnull(MAX(correlativo)+1,1))))+ convert(varchar,isnull(MAX(correlativo)+1,1)),
    isnull(MAX(correlativo)+1,1),
    @forma_conocimiento, @no_oficio, @situacion_delito,
    @delito_contra, @tipo_delictivo, @area, @departamento, @municipio,
    @canton_hecho, {fn UCASE(@caserio_hecho)}, {fn UCASE(@colonia_hecho)}, {fn UCASE(@comunidad)}, {fn UCASE(@calle)},
    {fn UCASE(@avenida)}, {fn UCASE(@barrio)}, {fn UCASE(@no_casa)}, {fn UCASE(@complemento)}, @sector,
    @x, @y, @gps, @condiciontiempo, @condicionlugar,
    @tipo_escena, @tipo_lugar, @tlugar_especifico, @movil, @circunstancia,
    @circunstanciaespecifica, @tipo_violencia, @nombreplan, {fn UCASE(@novedad)}, @creado_por,
    @reportado
    from aincidente WITH (XLOCK, HOLDLOCK)
    where delito_contra=@delito_contra
    and municipio=@municipio
    and DATEPART(year, fecha)= datepart(year,@fecha)
    COMMIT TRANSACTION

    Like

  7. Hi Pinal,

    I m using HOLDLOCK in my application, The code is given below

    BEGIN TRAN

    SELECT * FROM test1
    WITH (HOLDLOCK, ROWLOCK)
    WHERE Name = ‘Rahul4′

    update test1 set Name = ‘Rahul6′
    where Name=’Rahul5′

    COMMIT TRAN

    Now, if a second user tries to hit the same record before COMMIT TRAN has executed, then the control waits until the COMMIT TRAN has executed on the first user.

    My question is that, Is there a way I can stop the second user from hitting the same record by checking if that record has been already locked by the first user?

    Like

  8. Hi,
    thanks a lot for providing the knowledge about the basic types of locks available in SQL 2005.
    it’ld be great if I can have complete tutorial for Locking in SQL server 2005 including performance issues.

    thnx in advance.

    Like

  9. Sir,

    In my Visual basic 6.0 program I am executing a stored procedure, which writes result to a table…If at a time more than one person executes that SP, the table content will be mixed up. So I want to lock a table till first user releases the lock…like

    Lock Table (T_Details)
    —I will do neccessary actions here.
    — ..
    —..
    Unlock (T_Details)

    Please tell me the solution for this with a small example. Please URGENT…

    Ramesh Sambari

    Like

  10. Hi Pinal
    I want apply Lock on Table Row beacuse of User given Password not matched with existed Data.In this application User has three chance to send request to database but after third time i want provide Lock for that row.

    Please reply

    Like

  11. I v got a website using sql server 2005 when my ws reach 1000 users at the same time I start getting deadlocks… I have a vb forum installed on the same server when I try using it It’s working very well.. do I have to set everything to nolock
    How Can I resolve this issue ? is it from the application ?

    Please help.

    thanks…

    Like

  12. I have a question on HoldLock. Currently in HOLDLOCK syntax it is expecting a parenthesis for SQL 2005 and for SQL 2000 we do not need Parenthesis… I have third party code which do not expect Parenthesis for HOLDLOCK. How do I accommodate this feature for both SQL 2000 and SQL 2005??? Your response is much appreciated.

    Like

  13. Hi Pinal,

    I have a which reconciles data and insert more then 100 thousand records to a table. I don’t want this job to acquire X table lock on the table. I tried running this insert with RowLock and PAGlock, but it got escalated to TAB lock.

    Is their a way to avoid TAB lock here. I don’t want to stop users from reading and inserting to this table, while this job is inserting huge data, which takes quite a good time to complete.

    Looking forward to a quick help

    Thanks,
    Prashant

    Like

  14. Hi Pinal,
    UPDATE Products (WITH rowlock)
    SET ProductCat = ‘Man’
    WHERE ProductSubCat = ‘Mac’

    UPDATE Products (WITH rowlock)
    SET ProductCat = ‘Machine’
    WHERE ProductSubCat = ‘Mac’

    just had a interesting question posted to me .

    what will happen if both these queries are run by the users at the same time (no deviation even for a microsecond).
    Which Value will be updated?
    could you please help with this Query…..

    Thanks
    Nagaraj

    Like

    • here the statement

      UPDATE Products (WITH rowlock)
      SET ProductCat = ‘Machine’
      WHERE ProductSubCat = ‘Mac’

      will be updated early bcoz due to alphabetical order

      Like

  15. hi, Pinal

    i want to know that in web application having mutiple server.
    which locking is better (between these to Optimistic and Pessimistic ) & why ?

    Please give me answer with some details also.

    Thanks,

    Virendra

    Like

  16. Pinal,

    Thank you for all your wonderful articles.

    Here is our problem.

    I am developing an application in ASP.NET / SQL Server 2005 and want to use some triggers to fill “snap shot table”.

    Most recent information about a company is spread over multiple tables with 100s of fields. Ie need a search function to search on very basic company information (such as address). My idea is to create a small table with these 15-20 basic fileds and add triggers on detail tables to update this small table as necessary. Do you think there is any draw back in using triggers in this case? How will the locking work…lets say if some one is reading the search table and at the same time a trigger files to update the search table…?

    Like

  17. Can u tell us something about how to find which sql table is having the lock and of what type. also please tell us how to remove a lock from a locked table
    thanks
    Priyank

    Like

  18. @Priyank

    In SQL Server 2000 (Enterprise Manager)

    1. Expand server – management-currentActivity-expand
    locks/processid and you will be able to see all the locks related information.

    2.Expand server – management-currentActivity-expand Locks/object you can see locks by object information.

    In SQL Server 2005 (SSMS, object Explorer)
    Expand-server-management-double click Activity Monitor.
    on left side you have three options to choose from, select those options and you can see all the locks related information.

    run this stored procedure in the database.

    1. sp_lock

    to know the running process in the sql server, run this query,

    2. select * from sysprocesses ( in sql server 2000)
    3. select * from sys.sysprocesses ( in sql server 2005)

    4. sp_who
    5. sp_who2 will also give you some good information.

    To work around the locks, you can run profiler to check which query is is creating a lock and if that is necessary.

    Types of locks on object level, ( general idea)

    Database : Database.
    Extent : Contiguous group of eight data pages or index pages.
    Key: Row lock within an index.
    Page: 8-kilobyte (KB) data page or index page.
    RID :Row ID. Used to lock a single row within a table.
    Table: Entire table, including all data and indexes.

    Types of locks;
    Shared (S) – more than one Query can access the object.
    Exclusive lock (X) – only one Query can access the object.
    Update lock (U)
    Intent share (IS)
    Intent Exclusive (IX)

    Just to give you a brief idea about locks, We have something called as transaction levels in sql server databases.

    TRANSACTION ISOLATION LEVEL
    level 0. READ COMMITTED
    level 1. READ UNCOMMITTED
    level 2. REPEATABLE READ
    level 3. SERIALIZABLE

    level 0 is the lowest level isloation level, if your database is set in this isolation level, no query will lock any resources,Under this level, there will be no locks on the database, not even shared locks.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    This data will also read uncommitted data. Data which you have not comitted, you can still read that data.

    level1 is the default isolation level of the database.
    Under this category you will not be able to read uncomitted data, this is also called as dirty data. Under this we will have shared locks.

    As the level increases the locks also increases. The highest is the serializable.

    To make you understand in detail, lets see an example of what is committed data and what is uncomitted data.

    use pubs
    create table example1 ( eid int, ename varchar(10))

    begin tran T1
    insert into example1 values ( 1, ‘example’)
    go

    select * from example1 — this is uncomitted data.

    The above is uncomitted transaction, because you started the transaction with a begin, you have to commit the transaction, untill then the transaction will not be uncommitted.

    to commit the same transaction

    commit tran T1

    select * from example1 — this is committed data.

    To check what is the current isolation level of your database, run this command,

    Dbcc useroptions — check for isolation level.

    If you dont want your query to put locks on objects you might want to use something like this,

    select * from example1_1 with (nolock)

    This will not keep any lock, not even a shared lock on the table.

    This is indepth concept try looking BOL.

    Hope this helps,
    Imran.

    Like

  19. Pingback: SQL SERVER - Detailed Explanation of Transaction Lock, Lock Type, Avoid Locks Journey to SQL Authority with Pinal Dave

  20. Hi Pinal,

    I am using manifold (a software for GIS data management) linked with SQL Server 2005. Basically all the data resides in SQL Server Project and Manifold gets linked with that data, renders it, and allows users to edit and use it in various ways. The problem is that when two or more people try to edit same record, it gets duplicated and the duplicate record remains there until manually deleted. I want to know if there is something that I can do to SQL Server so that I can lock records.

    Regards.

    Like

  21. Hello Pinal,
    I would like to know about the Query Optimization technique. If you could give some technique, it will be very useful. More over, i need to fine tune few of our query in our project. Is it a good approach to include WITH (NOLOCK) command for Select statment for Query Performance.

    Waiting for your reply.

    Regards,
    PRabhu

    Like

  22. I have tried to use the syntax recommed for row locking/nolock shown above but my SQL 2005 developer edition 64bit tells me that it is only supported by the compact edition – can anybody help?

    Like

  23. Are you saying that SELECT statements default to NOLOCK. Other material I have read indicates that adding NOLOCK will prevent SELECT statements from creating/responding to locks. Can you please clarify.

    Like

  24. Hi Pinal,

    I could not access one table some time. Please let us know why it was not accessible and also how to find out when it got locked and because what..

    Thanks
    Balaji

    Like

  25. Hi,

    Just need a help in getting the resources. My question is as follows:

    How can I know what locks are running on which resource?

    I am using sql server 2005.

    Thanks
    Krishna

    Like

  26. Hello Pinal,
    I wanted to know if an updt lock hint would prevent conversion deadlocks,I am having an Update statement and a select statement , the update and select satements are causing a deadlock, Could I recomend an UIpdate Lock hint on the update statement?I could send you the locking event log and we could discuss further, I am pretty sure that what I ahve recomended is corrrect but my superieors tend to disagree, I wanted an Honest third party opinion.Can I send you the details via email?

    Thanks

    Ajay

    Like

  27. when i am using this query in SqlServer 2005 Master database,error display

    select * from projects
    (WITH HOLDLOCK)where pj_id=1

    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘WITH’.
    Msg 319, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    plz suggest what’s problem

    thanks and regards
    kapil gupta

    Like

  28. select * from users (WITH ROWLOCK)
    where userid=141

    this command also give the following error in Sql Server 2005.error is

    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘WITH’.

    thanks and regards
    kapil gupta

    Like

  29. hii Sir,

    i want to know about locks (their syntax,examples,..) in sql from the beginning.its not clear to me.how are they applied,some good examples,how shared and exclusive locks are used..Plzreply as soon as possible

    regards
    Neha

    Like

  30. Hi Dave, reading your post above you said that NOLOCK does not apply to INSERT, UPDATE, and DELETE statements.
    But you give this example:

    UPDATE Products (WITH NOLOCK)
    SET ProductCat = ‘Machine’
    WHERE ProductSubCat = ‘Mac’

    Like

  31. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  32. Hello Dave,

    In SQL Server 2005 update If Exists Else Insert, there are two patterns : IF EXISTS … ELSE INSERT and UPDATE if @@rowcount = 0 INSERT. Which is better ?
    1. IF EXISTS does two index search
    2. UPDATE holds IX and X locks

    Thank you,
    Sharon

    Like

  33. Hi Dave,

    I am not a DBA and we do not have one on staff but have a huge DB that we are monitoring with an app and it gets about 750 dblocks several times an hour. Your article mentions these so I am asking if you know of any further sites that can explain these better and what the ramifications are for these many dblocks on users trying to access the data.

    Like

  34. Hi Pinal sir,

    I am updating 693000 files. While updating I am also selecting 2640 record from the same table in a another query analyser.

    I noted that when i fire only select statemnt it took 0 second to fetch the record and if i select along with Update statement, it will increase the time. What should i do to prevent it. Can you please help me??

    Like

  35. With MSSQL 2008 the syntax of LOCK is like this

    Examples:
    SELECT OrderID
    FROM Orders WITH (ROWLOCK)
    WHERE OrderID BETWEEN 100
    AND 2000

    UPDATE Products WITH (NOLOCK)
    SET ProductCat = ‘Machine’
    WHERE ProductSubCat = ‘Mac’

    Note the parenthesis “WITH (LOCKTYPE) “

    Like

  36. Hello Sir,

    Need solution to an important problem.

    The Scenario is Hotel Management.

    The Rooms selected by one user, shoud not be displayed to other. How to achieve this without locks.

    Very very urgent.

    Please reply

    Like

  37. Hi Pinal,

    Your article is very interesting and I am a regular visitor of your wonderful site.

    What Imran Mohammed has written creates a confusion about the default isolation level.

    TRANSACTION ISOLATION LEVEL
    level 0. READ COMMITTED
    level 1. READ UNCOMMITTED
    level 2. REPEATABLE READ
    level 3. SERIALIZABLE

    “level1 is the default isolation level of the database.”

    BOL states that “READ COMMITTED” is the default isolation level.

    Thanks,
    \\K

    Like

  38. Hi Pinal,

    We have a financial accounting application where in we want to implement a lock as under:

    Suppose user 1 selects a particual voucher for edit, we want to lock in such a way that if any other user tries accessing the same voucher, they get an error message stating ‘voucher is locked, pls wait’. Once user1 commits the voucher, the lock gets released and becomes available for other users.

    Our application development platform is .net 2003, Framework 1.1 with SQL Server 2005 as the RDBMS. We use ADO.Net for DB interactions. We user VB.Net or C#.Net coding.

    Pls let me know what is the best way to implement this type of locks.

    Thx in advance
    Chandru

    Like

  39. hi all,

    i am executing the below query in sql server 2005.

    In the table structure is:

    sportsid int 4
    createdon datetime 8

    Query:

    SELECT * FROM tblblogs(NOLOCK) WHERE sportsid=2

    Result:
    16 rows affected.

    SELECT * FROM tblblogs(NOLOCK) WHERE sportsid=2 and createdon=’2009-04-10 00:00:00′

    Results:
    No records displayed.

    Please provide me the solution.

    Like

    • here instead of created on you should write created sdate=’20102204′ and created stime=’10:08:41′

      pls make sure the format of time and date u gace in your coding and follow the procedure.

      Like

  40. My name is Brian and i have a project to do. I need your support, this is the problem:

    I have a multithread client with 30 threads that execute same Stored Procedure (ex. getproducts ) that procedure return 10 diferent products from the products table, i want every thread get diferent products. I try including Serializable Transaction Lock Level and tons of deadlocks were found, my question is , what lock level i need to set in the stored procedure in order to get per thread 10 diferents products..

    — procedure getproducts —

    set transaction isolation level serializable

    select top 10 productID from products
    where checked=0

    update top (10) products
    set checked=1
    where checked=0

    — end procedure

    any idea?

    Like

  41. Would just like to mention using SS2005 that the statement examples above with the general syntax:

    Select * from table_name (with lock_type)

    failed to execute correctly. However the following form worked:

    Select * from table_name with (lock_type)

    Perhaps this is a syntax difference between SS2000 and 2005, I don’t know.

    Yours faithfully,

    Like

  42. Hi sir!
    i don’t know the syntax lock table, please tell me detail about it and please give me an example.thanks a lot.

    Like

  43. hi
    this is krishna i am beginner to sqlserver .
    i wnat to write trigger for attandance table i.e when
    attendance_dategetdate() u can’t update
    plz send urgent required…….

    Like

  44. Hi,

    I have two procesess running parally..one is removing and inserting data in a table..and same time other process is try to select it from the same table….
    it ends up locking the table…. how to avoid this…

    Thanks..

    Like

  45. Hi Pinal,

    basically my application having two pages
    1)to view booked tickets
    2)to view detail of ticket and update the ticket details
    i want to use row level locing when one dispatcher
    pnrno for updation no others can view that pnr

    i tried this using your examples but total table is getting locked
    can u give some examples

    Like

  46. Great article, but my question is by default does SQL Server lock records? This will drive some issues we are having with our database and response time, and error problems.

    Thanks,

    Tim

    Like

  47. I’m confused. I found your site and read about NOLOCK which you say is the default for SELECT and does not apply to INSERT, UPDATE, and DELETE. Then the example shows UPDATE Products (WITH NOLOCK)

    -> If it’s the default for SELECT and not valid for anything else, then when would one use it?
    -> Why does the UPDATE example show (WITH NOLOCK) if it’s not valid for update.
    -> I thought the syntax was WITH (NOLOCK) not (WITH NOLOCK)

    Your site says…
    =======================
    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:

    UPDATE Products (WITH NOLOCK)
    SET ProductCat = ‘Machine’
    WHERE ProductSubCat = ‘Mac’
    ==========================

    Like

  48. hi Pinal,

    i am creating a report on reporting service using stored procedure and deploying it on the web via asp.net
    The reason i need it is becoz i want the client run the report and if he is satisfied with the report of that day, the report of that day needs to be locked i.e nobody can access the same report of that date again..

    can you plz help me. i even don’t know whether its possible or not

    Like

  49. Hi Pinal,
    The following code gives the range of the vehicles which have disposal date later of end of august, 2009 and delivery date less than september 2009

    AND ((AA.DISPOSAL_DATE > ‘20090831’)
    OR (AA.STATUS_FIELD=’IN SERVICE’
    AND AA.DELIVERY_DATE < '20091001'))

    Since this is the specification for september month. i want to do it for each month before and after. I am not taking any month and year parameters.
    I should do in inside the stored procedure where above code also lies.

    HOW CAN I DO IT PLZ? HELP ME

    Like

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

    Like

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

    Like

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

    Like

  53. 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?

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  60. 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’

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

    • 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: http://msdn.microsoft.com/en-us/library/ms187749.aspx

      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: http://msdn.microsoft.com/en-us/library/ms189122.aspx

      Like

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

    Like

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

    Like

  66. 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!

    Like

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

    Like

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

    Like

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

    Like

  70. 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?

    Like

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

    Like

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

    Like

  73. Hi all,
    Is there a way to clear the lock to table?

    I read this article, http://social.msdn.microsoft.com/Forums/en/sqlgetstarted/thread/cae67b92-1f17-4f97-b68e-cfaba1207253
    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

    Like

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

    Like

  75. 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]

    Like

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

    Like

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

    Like

  78. hi,

    i have Category table with parent -child relationship.Now i want to copy this record and insert into same table with parent-child relationship with new identity.

    declare @id as int
    SEt @id=IDENT_CURRENT(‘[tblCategory]’) ;

    WITH t AS
    (SELECT [CategoryId],[CategoryParentId],[CategoryName],
    (@id
    + ROW_NUMBER() OVER (ORDER BY CategoryId))
    AS NewCategoryParentId
    FROM tblCategory)

    INSERT INTO [CategoryParentId],[CategoryName]
    SELECT isnull(t2.NewCategoryParentId,0),t1.CategoryName
    FROM t t1
    LEFT JOIN t t2
    ON t1.CategoryParentId = t2.CategoryId ;

    I want to lock table for insert.

    Like

  79. i have a query

    set transaction isolation level serializable
    BEGIN TRAN

    declare @MyVal nvarchar(50)
    set @MyVal =(
    select thisVal from TableA (TABLOCK)
    )

    declare @TempTable table ( Col1 nvarchar(10) )
    Insert into @TempTable (Col1)
    select MyCol From TableB (NoLock)

    ………………..
    ………………..

    COMMIT TRAN

    Please tell me….
    TableA had been locked during Tran?
    TableB had been nolock during Tran?

    Like

  80. Hello Pinal, I am facing a very severe problem. I have a table with 4 lac records almost and I am displaying them in gridview via paging. I am retrieving records from the table in chunks of 500 and then bind those 500 to gridview.
    The problem really is that when I go for the last chunk generated by my query, it times out… It does not times out at all for any other chunk except last chunk. Please I await your reply sir.

    Thanks

    Like

  81. Hi pinal

    We faced a blocking issue when we issued a qurey like

    select from , with (nolock) where order by

    I am sure there was DDL commands after this becuase it happend in production environment

    I would like know why this led to a blocking even though we applied nolock option

    Like

  82. can i get more descriptions bout all these locks ….if possible provide me ..or if you have written some articles please provide me appropriate link please

    Like

  83. hi Pinal,

    I have a situation where large number of user reads data from table, at the same time some job will insert data into the same table .we should make user to wait till insert complete and we should not make job to wait until shared lock release (data has to be refresh for every 2 min’s).

    thought of apply hold lock while insert the data into table .

    Please suggest me the best practice

    Like

  84. hi Pinal,
    I have a situation where large number of user reads data from table, at the same time some job will insert data into the same table .we should not make user to wait till insert complete and we should not make job to wait until shared lock release (data has to be refresh for every 2 min’s).
    thought of apply hold lock while insert the data into table .
    Please suggest me the best practice

    Like

  85. hi pinal,
    i got below exception while using (with rowlock) as i try to execute your above example for locking in select statement.am sql server 2005.

    Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword ‘with’.
    Msg 319, Level 15, State 1, Line 4
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    Like

  86. hi!!
    i want to ask that how create a lock on login.
    i as an administrator created a user and now i want to lock my login section in sql server 2000 can you tell me how can i do it.

    REPLY!! ASAP…

    Like

  87. hii pinal,
    thank u very much for such a great post…….. it’s fabulous.
    I need to ask u one question.

    suppose if i am using rowlock in following manner

    SELECT OrderID
    FROM Orders WITH( holdlock , ROWLOCK)
    WHERE OrderID =100

    then can u please tell me ….. untill how much it will hold this lock . because as soon as i execute this query ; simultaneously i can select the same query from another page. that means it is not holding a lock …

    SO PLEASE ME HELP OUT…….
    THANKS IN ADVANCE

    Like

  88. hey pinal,
    thank u very much for such a great post…….. it’s fabulous.
    I need to ask u one question.

    suppose if i am using rowlock in following manner

    SELECT OrderID
    FROM Orders WITH( holdlock , ROWLOCK)
    WHERE OrderID =100

    then can u please tell me ….. untill how much it will hold this lock . because as soon as i execute this query ; simultaneously i can select the same query from another page. that means it is not holding a lock …

    SO PLEASE ME HELP OUT…….
    PLEASE REPLY ME AS SOON AS POSSIBLE
    THANKS IN ADVANCE

    Like

  89. Hi Kapil,

    The syntax is incorrect. Use WITH(HoldLock) rather than (WITH HoldLock).

    Query will become:

    select * from projects WITH (HOLDLOCK)where pj_id=1

    Same is for (WITH ROWLOCK). Use WITH (ROWLOCK).

    Query will become:

    Select * from users WITH (ROWLOCK) where userid=141

    Like

  90. Hello,

    We are migrating from sql server 2000 to sql server 2005. When running the advisory stats, the below error message was given for an particular SP.

    In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause of a query only when the hints are specified using the WITH keyword.

    Below is the query in the SP, here an index has been specified to be used.

    Select cw.FundID from BRCallPaymentWork cw (INDEX = NonIdx_FourKey_BrCallPayment) where cw.FundID = cp.FundID and cw.InvestorID = cp.InvestorID and cw.ParentID = cp.ParentID and cw.ChildID = cp.ChildID and cw.amountcalled = cp.amount

    I believe in 2005 we need to use an hint , can u please help me how will this work?

    Your help will be appreciated.

    thanks,
    aman

    Like

  91. Hi Pinal,
    I want to know how one can view and remove locking on DBOs in SQLServer and also how to decide on which table to be removed off locking (if is deadlocked) without loosing data.

    “Thanks in advance P.Dave.”

    Like

  92. We have some deadlock issue while updating a set of tables in a single transaction.if multiple users are trying to update same table at same time, it gets locked. Could you please give a solution for this

    Thanx in advance

    Like

  93. here i read a row
    select name from emply where id=19
    and nxt if i try to read d same row it should be locked..i shouldnt be able to read it…can u suggest me how to achieve this hibernate

    Like

  94. I’d recommend editing your text up top to remove that nolock is the default for SELECT or specify up top its the default only for read uncommitted. I know you mention this in your reply below this is the case, but since READ UNCOMMITTED is not a usual default this is quite misleading when you first read this post as witnessed by several replies in this thread

    Like

  95. Hello sir,

    I am susendran,i new to sql i want to lock my stored procedure and when i want nedd to modify it please give me idea

    Like

  96. hello sir,

    i have two linked server.

    i am trying to insert from one to another using sp but in all this process
    one table is locked.

    not even access the data from local server also can’t drop table
    this table have one trigger afterinsert and using this trigger i maintain a
    log that table also locked so trigger can’t drop or altered

    so to release this what i have to do
    help me…

    Like

  97. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 16 of 31 Journey to SQLAuthority

  98. I have a database where I need to process 100+ queries on the same table per second. The query takes approx 20 milliseconds to run normally, but as I start to bring up more threads and do more simultaneous queries it starts to slow down to 200-2000 milliseconds. The more simultaneous queries I use the slower they all become. Is there any way to avoid this? I am using NOLOCK in the query, but was hoping you may have some tips to help me with this one.

    Like

  99. Hi Pinal,

    I am facing one problem in one our application. Here we have table where we insert 400+ records every time. At the same time if any user performs a select operation on this, we are getting time out error and occurrence of deadlock.

    When we select the records with nolock condition we are getting the records which are not locked. But this is not the actual solution to it.

    This table consists of 500000 records altogether. Kindly suggest us a way to overcome of it.

    Thanks.

    Like

  100. Hi Pinal,

    I am facing performance issue why processing the data. Here is what my data processing scenario. we process the data on daily basis (charges/Transactions)

    1. We load the data into Temporary Tables (New) and Create the Transaction keys for the new records.
    2. Then we merger the new data with Production Data (below logic we follow)
    A. Insert the complete Production Data into TableB
    B. Create Indexs on TableB
    C. Delete old data if exists based on MDXkey with New Table
    D. Insert New Data into TableB
    E. Switch the Productiontable – TableA ,TableB to Production Table
    F. Drop Table new, TableA

    Questions:

    1. Because of the above process we are facing lot issues in server level (Database server is getting crashed frequently) the reason for this is Lot of IO reads and taking lot of resources Is there any alternate way you can suggest me to process data

    Thank you in Advance

    Thanks

    Like

  101. hello sir,
    Please help me. in my programme i m using sql 2008. when it run it multi user environment.if one person save a big invoice then my programme in all other systems are hanged untill he save the invoice. I know it s by db table lock. how to overcome it .pls reply. thanks in advance.

    Like

  102. Hi Pinal,

    Since I knew about this site.. have become a frequent reader.. Could you please tell me by default when sql runs an update is there any lock on table also during insert. Thanks, Laxmi

    Like

  103. Hi Pinal

    I Have some problem in my database SP. That is created by another person .Now he is not with us.He Lock the Stored procedure and other person can not modify this.Now I want to modify and check This Procedure.Can you help me how this is Possible?

    Thanks
    Susendran

    Like

  104. Hi,

    The below code part of an stored procedure has been mentioned below:
    ——————————————————————————-
    UPDATE [CandidateTests].[TestDetails_test1]
    SET [TestUID] = (SELECT MIN(TestUID) – 1 FROM CandidateTests.TestDetails_test1 WITH(NOLOCK))
    WHERE [BusinessStreamID] = @BusinessStreamID AND
    [TestID] = @TestID AND
    [BankID]= @BankID AND
    [TestYear] = @TestYear

    UPDATE [CandidateTests].TestDetails_test1
    SET [TestUID] = (SELECT MIN(TestUID) – 1 FROM CandidateTests.TestDetails_test1 WITH(NOLOCK))
    WHERE [BusinessStreamID] = @BusinessStreamID AND
    [TestUID] = @TestUID
    ——————————————————————————-
    Here BusinessStreamID,TestID,BankID,TestYear are primary key and BusinessStreamID and TestUID are having nonclustered unique index.

    This code part is taking log time to excute to process approx 60,000 records.

    It seems the code part is blocking by itself.

    So could you please help me out to resolve the problem.

    Thanks,
    Kaushik

    Like

  105. Rowlock hints not working…. select * from std with(rowlock) where rno = 5;
    same query works from another pc, database is accessed from server….
    client server envirnoment….

    Like

  106. I have made the query like –

    SELECT TOP 3 * FROM MMSContent WITH (HOLDLOCK XLOCK ROWLOCK) waitfor delay ‘0:00:15′

    SELECT TOP 3 * FROM MMSContent WITH (HOLDLOCK XLOCK ROWLOCK) waitfor delay ‘0:00:10′

    SELECT TOP 3 * FROM MMSContent WITH (HOLDLOCK XLOCK ROWLOCK) waitfor delay ‘0:00:10′

    When I run the first query it comes with the ID No – 1,2,3. It holds for 15 seconds. But in the meantime when I run the 2nd query in another query analyzer, it also come ID NO – 1,2,3. But it should not come becasue the first 3 rows is already locked. It should come ID No -4,5,6.

    How can I do this?

    Like

  107. Hi Sir,

    i like to read your article about lock in sql server,

    but could you give the good solution for resolve this problem,

    if the lock begin,

    Thankyou for your answer

    Rahmat

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  115. 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 ?

    Like

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

    Like

  117. 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;

    Like

  118. **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**

    Like

  119. 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 :).

    Like

  120. 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)

    Like

  121. 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!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s