Prevent a SELECT Query from returning results using LOCKS

• lilywhites

  SSCrazy

  Points: 2732

  More actions

  December 2, 2014 at 6:29 am

  #313508

  Hello,

  I am trying to find a way to lock rows of data used in a SELECT query from being read by another SELECT query.

  I could do a "begin tran - select - update - rollback" sequence but was wondering if there is a cleaner way to do this??

  Tried UPDLOCK, ROWLOCK, TABLOCK, HOLDLOCK in multiple variations but none seem to block the select.

  Many Thanks

  Samuel

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  December 2, 2014 at 6:29 am

  #1762036

  XLOCK

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• lilywhites

  SSCrazy

  Points: 2732

  More actions

  December 2, 2014 at 6:50 am

  #1762048

  i have tried that but it doesnt work....still able to select from another session

  i have the following:

  --create table tbl_work_list

  --(task varchar(10))

  --insert into tbl_work_list

  --values('task1'), ('task2'), ('task3'), ('task4')

  select top 1 * from tbl_work_list with (xlock)

  then in another session:

  select * from tbl_work_list

  returns all 4 rows

  is there an isolation level i need to set??

  thanks

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  December 2, 2014 at 6:57 am

  #1762055

  Because the one select took the lock, ran the select and automatically released the lock once it finished. The second would have waited for the first to finish (because the XLOCK is not compatible with any other lock) and then run once the first finished.

  Locks aren't held permanently (for very good reason), just for the duration of the query or at most the transaction.

  An XLOCK will prevent anyone reading the locked row, until the query that requested the XLOCK completes. Once it completes, the lock is released and any other query waiting to read that row will then run.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• lilywhites

  SSCrazy

  Points: 2732

  More actions

  December 2, 2014 at 7:03 am

  #1762059

  ah ok....i was hoping a lock could be session based rather than transaction based....but i understand why that could be a bad idea!!

  ill create a checked-out column or something similar

  thanks 🙂

  ================

  please mark as closed

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  December 2, 2014 at 7:10 am

  #1762061

  If you're trying to implement a checked in/checked out feature, please do some reading up first. It's one of those things that's easy to get wrong and very hard to get right.

  iirc there's a chapter on the subject in "Expert SQL Server 2005 Development" (the 2005 version, not any other version) http://www.amazon.com/Expert-SQL-Server-2005-Development/dp/159059729X/

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• lilywhites

  SSCrazy

  Points: 2732

  More actions

  December 2, 2014 at 7:15 am

  #1762062

  thanks gail 🙂

  i was just looking for some articles....but i got a lot of time to test this fortunately!

• ScottPletcher

  SSC Guru

  Points: 100262

  More actions

  December 3, 2014 at 11:57 am

  #1762493

  To avoid rows already being modified, you can use the READPAST hint. That's often used for work-queue-type processing. If you can use that, there's no good reason to re-write what's already built into the SQL engine.

  SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  December 3, 2014 at 12:42 pm

  #1762500

  Another method I have used for a queue table is to delete the row and insert it into a user queue table, nothing is going to read it if it doesn't exist.

  set rowcount 1;

  delete main_queue

  output @userid, deleted.id, deleted.foo, etc.

  into user_queue

  where foo='bar';

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]