Re: Lock a row
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 01/31/05
- Next message: Manish Sawjiani: "Re: @@IDENTITY Programming Jitters Thanks"
- Previous message: Matt Bolton: "Re: Expressions in queries"
- In reply to: souris: "Re: Lock a row"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 30 Jan 2005 21:52:50 -0600
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... >> 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) > >
- Next message: Manish Sawjiani: "Re: @@IDENTITY Programming Jitters Thanks"
- Previous message: Matt Bolton: "Re: Expressions in queries"
- In reply to: souris: "Re: Lock a row"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
