[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-04-08
6
Medium Priority
?
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 41

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 1000 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 70

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

656 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