Solved

How to make sure the row in is not locked in WHERE clause?

Posted on 2014-04-08
6
189 Views
Last Modified: 2014-04-09
Here is my query. How do I make sure that row is NOT locked by anyone else? If someone has locked it then it should not return it. It should return me next top ullocked record.

SELECT TOP 1 Id FROM XYZ.
0
Comment
Question by:GouthamAnand
6 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39986112
Are you talking locked as in DB Lock?

If so then the query will wait until whoever else is done with the table, possibly timing out before the lock is released.

If you're talking about your own Lock column:
select top 1 id from XYZ where lock = 0

What exactly are you trying to do?
0
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39986142
So I have to ask...  What is the use case for this?  This is a rather in depth request (undocumented traceflag and possibly dmv) and there may be better options.
0
 

Author Comment

by:GouthamAnand
ID: 39986245
I need a TOP 1 record which has not been locked by ANYONE ELSE.

Several users working on the applicaton. And when a user opens a record on UI , it locks the record.

I want to select the top 1 record whish has not been locked by any other user.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 39986252
I would create your own lockedby column in the application then.

Set the lockedby column when you enter the record to the user
set the lockedby column to null when you leave the record

then you could do
select top 1 id from XYS where lockedby is null
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39986586
>> when a user opens a record on UI , it locks the record. <<

What UI?  And how specifically does it "lock the record"?

Sorry, but how you're doing this really matters to how to properly answer this q.
0
 

Author Closing Comment

by:GouthamAnand
ID: 39989065
Thanks for all the responses.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Writing SQL Select Query result to a text file 12 56
SQL query with cast 38 50
tempdb log keep growing 7 32
how to restore or keep sql2000  backups useful... 2 12
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question