Lock a row

I wanted to locked to hide a record to other user if the record is viewing
by one user.
Are there any way to do this?
Does lock the record can hide this record to other user?
Any information is great appreciated.
SourisYou can use query hints with a transaction but you're looking at scalability
issues. Why do you want to hide a row while someone is looking at it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"souris" <soukkris@.viddotron.com> wrote in message
news:eJ3lg3kBFHA.2916@.TK2MSFTNGP10.phx.gbl...
I wanted to locked to hide a record to other user if the record is viewing
by one user.
Are there any way to do this?
Does lock the record can hide this record to other user?
Any information is great appreciated.
Souris|||On Sat, 29 Jan 2005 17:00:05 -0500, souris wrote:

>I wanted to locked to hide a record to other user if the record is viewing
>by one user.
>Are there any way to do this?
>Does lock the record can hide this record to other user?
>Any information is great appreciated.
Hi Souris,
You could use the READPAST locking hint. Keep in mind that this is only
usefull in some very specific circumstances. If you use this locking hint
in a statistical or reporting query, you'll miss some data without having
any indication if and how much data you miss.
A typical situation where the READPAST hint is useful is a queueing
system: each application / connection / agent / whatever reads the first
row from a wrok-queue table and acquire an exclusive lock; other agents
will read past this row and get the next non-locked one. The exclusive
lock ensures that the connection reading the row will be able to change or
even delete is after the data is processed.
BEGIN TRANSACTION
SELECT TOP 1 ....
FROM WorkQueue WITH (READPAST, XLOCK)
ORDER BY Priority
-- Do some stuff
IF (...) -- data changed
UPDATE WorkQueue
SET ... = ...
WHERE ....
IF (...) -- processing completely done
DELETE WorkQueue
WHERE ....
IF (...) -- check for unhandled errors
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the message.
This app is for a call center.
Because I do not want the customer get more than one call for the same issue
Souris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZJxXhlBFHA.1296@.TK2MSFTNGP10.phx.gbl...
> You can use query hints with a transaction but you're looking at
> scalability
> issues. Why do you want to hide a row while someone is looking at it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "souris" <soukkris@.viddotron.com> wrote in message
> news:eJ3lg3kBFHA.2916@.TK2MSFTNGP10.phx.gbl...
> I wanted to locked to hide a record to other user if the record is viewing
> by one user.
> Are there any way to do this?
> Does lock the record can hide this record to other user?
> Any information is great appreciated.
> Souris
>|||Thanks for the information,
It is what I am looking for.
Souris
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:4b6ov0dsbu5e3gmhsne0omtr2ul2ac95ae@.
4ax.com...
> On Sat, 29 Jan 2005 17:00:05 -0500, souris wrote:
>
> Hi Souris,
> You could use the READPAST locking hint. Keep in mind that this is only
> usefull in some very specific circumstances. If you use this locking hint
> in a statistical or reporting query, you'll miss some data without having
> any indication if and how much data you miss.
> A typical situation where the READPAST hint is useful is a queueing
> system: each application / connection / agent / whatever reads the first
> row from a wrok-queue table and acquire an exclusive lock; other agents
> will read past this row and get the next non-locked one. The exclusive
> lock ensures that the connection reading the row will be able to change or
> even delete is after the data is processed.
> BEGIN TRANSACTION
> SELECT TOP 1 ....
> FROM WorkQueue WITH (READPAST, XLOCK)
> ORDER BY Priority
> -- Do some stuff
> IF (...) -- data changed
> UPDATE WorkQueue
> SET ... = ...
> WHERE ....
> IF (...) -- processing completely done
> DELETE WorkQueue
> WHERE ....
> IF (...) -- check for unhandled errors
> ROLLBACK TRANSACTION
> ELSE
> COMMIT TRANSACTION
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||I would probably suggest that this is not a great idea. Holding locks from
the client is troublesome, and if the connection dies you are not even going
to see that there was a call in progress. In this case, why not use a
status column (or table, if you want a complete history of status)
Then when the user gets the row, you set the status to in-progress, and set
the user that has the row in use. If they don't complete the call properly,
it will stay in an open state. When the user logs back in, you can check to
see if they have any open sessions and make them deal with them.
All of your other clients will look for rows that are not in-progress or
completed. Use the readpast hint as mentioned by Hugo to keep any clients
from having to wait. This way your management applications can see which
calls are in progress, what their status is, and it will still be "locked"
from the users that it needs to be locked from.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"souris" <soukkris@.viddotron.com> wrote in message
news:%23Fbdm4oBFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks for the information,
> It is what I am looking for.
> Souris
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:4b6ov0dsbu5e3gmhsne0omtr2ul2ac95ae@.
4ax.com...
>