Prevent a SELECT Query from returning results using LOCKS

Question

Post reply

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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 · Answer 2

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: 1004504 More actions · Answer 3

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 · Answer 4

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: 1004504 More actions · Answer 5

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 · Answer 6

thanks gail 🙂

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

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

lilywhites SSCrazy Points: 2732 More actions · Answer 9

thanks guys

they have since added a load of auditing functionality to this project so its getting more complicated than just a checked-out function. I have a feeling this will end up as a small web-based application by the time they have finished adding stuff 🙁

its gone from checking out a work task until its completed to something that can track the user logins, task assignments, task start, task end, task timeout, task reassignment, task groupings, user permissions etc.....

im gonna speak to the web guys and hoping that C# can help with the auditing and assignment logic

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

lilywhites (12/4/2014)
its gone from checking out a work task until its completed to something that can track the user logins, task assignments, task start, task end, task timeout, task reassignment, task groupings, user permissions etc.....

Sharepoint with MS Project integration?

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 · Answer 11

it needs to plug into an in-house pre-existing CRM and we dont have a sharepoint resource at the moment

i will pose the idea and see if they are interested

thanks again 🙂