locked
How to lock rows by select statement RRS feed

  • Question

  • I want to execute two SQL simultaneously which should not be able to fetch same rows. How to achieve this with lock?

    Say my SQL is like select * from table where status='new'

    Suppose my above SQL will be executed from two different PC at same time.

    Second sql will not be able to fetch those rows which first SQL select statement get hold on.

    If it is possible then please discuss  with code sample.

    If not possible then how to achieve it with alternate way.

    Looking for guidance. Thanks


    • Edited by Sudip_inn Thursday, January 10, 2019 6:41 PM
    Thursday, January 10, 2019 6:35 PM

All replies

  • The OUTPUT clause to the UPDATE statement can come in handy here:


    UPDATE tbl
    SET   status = 'notsonewanylonger'
    OUTPUT inserted.col1, inserted.col2, ...
    WHERE  status = 'new'


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 10, 2019 10:55 PM
  • Hi Sudip_inn,

    SET TRANSACTION ISOLATION LEVEL controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server.

     

    REPEATABLE READ specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

     

    For more information, please refer to it: SET TRANSACTION ISOLATION LEVEL

     

    Please try to use 'set transaction isolation level repeatable read'. And in following script, I provide a simple example to lock the row.

    create table zc
    (id int not null,
     col1 char(4000),
     col2 char(4000)
     constraint pk_zc primary key clustered(id)
    )
     
    insert into zc
     select 1,'aaa','bbb' union all
     select 2,'ccc','ddd' union all
     select 3,'eee','fff' union all
     select 4,'ggg','hhh' union all
     select 5,'iii','jjj'
    
     -----connection A (execute the steps one by one A.1--->B.1(wait)--->A.2)
     -----step A.1 
    set transaction isolation level repeatable read
    begin tran
    select * from zc (xlock) where id=2
    
    ------connection B  
     -----step B.1 (it will wait and not finish)
    select * from zc (xlock) where id=2
    
     -----connection A
     -----step A.2
    commit tran
    
    ------connection B  (it will finish and show the result)


    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 11, 2019 6:24 AM
  • Hi,

    One suggestion:

    HOLDLOCK Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.

    Friday, January 11, 2019 3:14 PM
  • One suggestion:

    HOLDLOCK Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.

    No, this will not work, but rather it will cause more problems. Sudip asks how to prevent that that two processes do not get the same rows back. If all he does is to add the SERILIAZABLE hint, both processes will get rows with status = 'new'. I assume that in the next step, the process go an update the status of these rows. If there is no active transaction, nothing particular will happen in the relation between those two transaction. If there is a transaction, there will be a deadlock, since neither processes can update the rows locked by the other process.

    But it does not stop there. Presumably, there are one or more processes writing rows to this table. These processes will be blocked until a fetching procedure has committed its transactions, since the serializable isolation level guarantees that the result set returned in the SELECT WHERE status = 'new' will not change.

    The correct solution is to use an UPDLOCK with the REPEATABLE READ isolation level. An update lock is a read lock, but only one process can hold an update lock on a resource, so if two processes try to read rows with status = 'new' in parallel, one will be blocked until the other commits. Repatable read is a weaker isolation level than serializable, which permits for new inserts, which is exactly what we want here.

    Above I suggested an UPDATE with an OUTPUT clause. This achieves exactly this, but leaves it to SQL Server to implement the locks.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, January 11, 2019 4:20 PM
  • Note that when using a UPDLOCK or XLOCK, you don't need to change the transaction isolation level.  REPEATABLE READ just causes Shared locks to be held for longer.  U and X locks are held until the end of the transaction in every isolation level.

    David


    Microsoft Technology Center - Dallas
    My blog

    Friday, January 11, 2019 4:25 PM
  • You really do not want to do that. That is an outdated concept.

    You should really use this:

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/optimistic-concurrency

    Friday, January 11, 2019 6:33 PM
  • i am talking about select statement not insert or update statement.

    my same application will run in two pc which issue a select sql at same time but i want two application will not be able to work on same data. so how to issue a lock as a result when one application select few data by select sql then same data will not be able to other application.

    if you know best way then please share idea how to handle this situation. thanks

    Saturday, January 12, 2019 5:07 PM
  • Sir what is difference between UPDATE & XLOCK in sql server ?

    basically i want to place lock during select statement....does UPDATE or XLOCK work in that case?

    i am not after insert/update or delete..just want to lock during select select statement. please guide me further.

    Saturday, January 12, 2019 5:14 PM
  • i am not after insert/update or delete..just want to lock during select select statement.

    you said - I suggested an UPDATE with an OUTPUT clause

    what you trying to say....please provide some code sample which show me how to use UPDATE with an OUTPUT clause.

    basically i want my same application running on two or more pc will not be able to select same row if all application issue same select sql.

    please guide me further.

    Saturday, January 12, 2019 5:17 PM
  • i have few questions

    1) what xlock does ? does it lock rows during select statement ?

    2) every database has default isolation. if i mention isolation in my store proc so when store proc will be completed then isolation will be rest back to default ?

    3) how can i execute same select sql from two different sql server session from one pc at same time ? guide me how to simulate in my pc?

    this is required to test your code to simulate that one select can fetch data and other select from other session waiting?

    please guide me.

    Saturday, January 12, 2019 5:22 PM
  • i am talking about select statement not insert or update statement.

    my same application will run in two pc which issue a select sql at same time but i want two application will not be able to work on same data. so how to issue a lock as a result when one application select few data by select sql then same data will not be able to other application.

    if you know best way then please share idea how to handle this situation. thanks

    And best way is do it is to mark the rows as "taken". That is why I suggested an UPDATE with an OUTPUT. If you only select, how would the next guy know that they are not available?

    what you trying to say....please provide some code sample which show me how to use UPDATE with an OUTPUT clause.

    That was in my first post, which you replied to. But which you seemed to have forgotten when you came this far.

    Sir what is difference between UPDATE & XLOCK in sql server ?

    An XLOCK is an exclusive lock - the resource is not available for no other process.

    An UPDATE lock is a special type of read lock, so other processes can access the resource with a regular shared lock. However, only one process can hold an UPDATE lock on a resource. But if you use UPDATE with OUTPUT as I suggested, this is noting you have to bother about.

    Saturday, January 12, 2019 6:33 PM