SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to tell if a row is locked?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sam4
Starting Member

USA
7 Posts

Posted - 04/14/2008 :  19:29:56  Show Profile  Reply with Quote
I am writing in regard to this post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52798
Which I found while searching for a best-practice way to implement record locking in my application.
In the above referenced post the user asks “How can I tell if a row is locked?”, and the reason for asking is that the user wants to implement logic similar to this:

Select a row for editing.
If the row has been locked by a different user, display the information as read only.
Otherwise, lock the row and allow the user to edit (allow others to read).

My questions are exactly the same as the original poster of the above thread. I was surprised to see that the general response to the post was the user was taking a wrong approach and that sql server is unable to work as the user requested. I think the approach is both correct and desirable (I used it extensively as a foxpro programmer) and I would like further confirmation that sql server does not support it.

One of the things I don’t understand is the interaction between transactions and locks. Is it not possible to lock a row without initiating a transaction? Bottom line, my goal is this:

Select a row for editing.
If the row has been locked by a different user, display the information as read only.
Otherwise, lock the row and allow the user to edit (allow others to read).


However I get there is fine with me.



LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 04/14/2008 :  20:22:01  Show Profile  Reply with Quote
The locks in SQL Server are purely there to implement consistency and ensure that multiple transactions do not modify the same data at the same time.

What you are describing are application locks, which need to be written by your application to cater for your business rules.
Most applications do not do what you describe, rather make the assumption that any change will be ok and deal with the consequences of someone else having changed the data at update time(optimistic locking). Note that you still need to code the check for this when you update with something like

UPDATE x set a=@a, b=@ WHERE pk=@x AND lastUpdated=@timestampWhenIreadTheRowIntoMemory

The other way is pessimistic where you have to record somewhere that you want a particular row locked and then work it out from there when you query. You then have to think of a way of clearing all the locks that get left lying around when users fail to disconnect properly for some reason.

Go to Top of Page

sam4
Starting Member

USA
7 Posts

Posted - 04/15/2008 :  12:44:21  Show Profile  Reply with Quote
Thanks. I can see the advantage for web applications. Seems like a major step backwards for intranet/business desktop apps.

>>Most applications do not do what you describe, rather make the assumption that any change will be ok and deal with the consequences of someone else having changed the data at update time(optimistic locking).

Where did get that statistic? It doesn't sound right to me.

>>You then have to think of a way of clearing all the locks that get left lying around when users fail to disconnect properly for some reason.
TIMEOUT = n


Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/15/2008 :  13:12:56  Show Profile  Visit spirit1's Homepage  Reply with Quote
your scenarion is usualy implemented in this way:
in your table add a column IsEditing or something similar.
default value for this column is 0
when you start editing the row set the value to 1.
when you finish editing the row set the value back to 0.
and users can edit only IsEditing = 0 rows. you limit this in your select's where part.



_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 04/15/2008 :  13:18:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by sam4

>>Most applications do not do what you describe, rather make the assumption that any change will be ok and deal with the consequences of someone else having changed the data at update time(optimistic locking).

Where did get that statistic? It doesn't sound right to me.




Experience.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 04/16/2008 :  04:11:46  Show Profile  Reply with Quote
quote:
Originally posted by sam4


TIMEOUT = n


You've missed the point. You do not hold the transaction open while the user is editing.

begin trans
  select data into UI
  make note of timestamp/sequence
  You flag the fact someone is changing it (commit the flag)
commit


Get the changes from the user using your gui. This can take ages
User saves, so

begin trans
   update blah blah where ID=x and timestamp=value noted above
commit

-- Handle success or fail


remove flag.


The reason this is common is because if you do it your way you quickly realise a few things:

a) Transactions & database connections are expensive resources.
b) SQL Server sucks at concurrency and many of your other transactions will block when they need the row you have locked even if only for reading.
c) You can get round it by sprinkling 'nolock' everywhere, but then you read inconsistent data and introduce bugs
d) Your customers decide that the way your application needs to handle concurrent access needs to change. Sorry - that's the way it works and it is out of my hands. I suppose we could ask Microsoft to quickly change the way the locking mechanism works....or we can do it the other way.
e) You decide to port your desktop app to something webby or enterprisey, like J2EE to handle a large number of users. Shit! Stateless sessions! How do I do this? WTF is this connection pooling stuff?

I'll repeat - Locks are there to implement consistency. How you handle concurrent access to your records is your problem. A domain problem. Not a technical one. Not the database's.

Basically, as Tara says, you learn by experience. Ideally you'll learn from other peoples'.
Go to Top of Page

sam4
Starting Member

USA
7 Posts

Posted - 04/16/2008 :  11:27:17  Show Profile  Reply with Quote
Whoops.. you didn't read my post. I don't want to initiate a transaction. I want to lock a row.
>>Is it not possible to lock a row without initiating a transaction?

One thing we do agree on: That I learn from experience. I learned from my experience as a foxpro programmer that the application design I described in my post is optimal for desktop business applications. Weather or not it works with sql server is another issue. I wrote the major portion of an enterprise wide ERP system that supported an electronics manufacturing company for many years. Contrary to the naysayers I read on this forum I didn't need to hire an extra person to issue the unlock that was needed manually once every six months.

>>I'll repeat - Locks are there to implement consistency. How you handle concurrent access to your records is your problem. A >>domain problem. Not a technical one. Not the database's.
LOL so much drama. I hope you have as much fun writing this stuff as I have reading it :)
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 04/16/2008 :  12:32:56  Show Profile  Reply with Quote
"LOL so much drama. I hope you have as much fun writing this stuff as I have reading it :)"

Given that we're not the one with the problem, I'm not sure who's going to be enjoying themselves the most...US or you...
Free Advice comes with a health warning...you are free to discard it, but it usually is given (around here) with the best of intentions.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/16/2008 :  12:57:13  Show Profile  Reply with Quote
Obviously requirements vary. I've worked on projects where we just had a simple flag and userid (or something to identify who is editing) and if some one wanted to write while that flag was set and they were not the editor they were prompted and they had the option if they wanted to write data when it was probably going to be overwritten by the current editor. In another scenario, we had to save a datetime and the editor only had some time period (say 5 minutes) in which to make an edit or else their lock was released and a new person could edit the record. So, it all depends on your requirements on how to implement the "locking."
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 04/16/2008 :  19:17:34  Show Profile  Reply with Quote
Hey Sam. If you're asking for advice why not try listening to the answer. I did read your post but you seem incapable of understanding my response. You need transactions to implement your (domain) locking solution because you are updating your database.....
You know what? Actually I don't care whether you succeed or fail. My next and final bit of advice to you is to go and read up on the tools you are actually required to use (i.e. SQL Server) and not resort to knowledge gained on a completely different platform (i.e. Foxpro).
Then go & <edited out by tkizer> yourself.

Edited by - tkizer on 04/16/2008 19:21:39
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 04/16/2008 :  19:22:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
Let's try to keep this civil.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sam4
Starting Member

USA
7 Posts

Posted - 04/18/2008 :  00:54:00  Show Profile  Reply with Quote
Hey buddy relax a little. Believe it or not I both understand and appreciate your posts. I may choose to go another way. You need to be OK with that. I didn't ask for a mandate.

Regards & better luck next time.
Go to Top of Page

WyldOne
Starting Member

USA
4 Posts

Posted - 04/24/2008 :  01:29:09  Show Profile  Reply with Quote
Well it can be done. I'll leave the political arguments on 'if' it should be done aside.

read MSDN article on sp_getapplock and sp_releaseapplock:
http://msdn2.microsoft.com/en-us/library/ms189823.aspx

I would suggest that when you try to edit run sp_getapplock with the row's unique_id (GUID, identity, etc) then app can edit and when you finally do the update, then call sp_releaseapplock with the same unique_id to release it.

I would suggest a 'Exclusive' lock with a timeout value on it. that way no other process etc can get the same lock ad if the app hangs it will eventually timeout and release it anyway. the app has to make sure that when it does get the results of the sp_getapplock that it actually _has_ the lock before preceding.

This has several advantages.
1) you do not create a lock on the table until you actually do the update.
2) other users will not try to update the record you are using
3) if the app dies in the process (like that ever happens) the lock is _automaticly_ released
4) you don't have to deal with 'non-blocking (dirty) reads' and query optimizer hints
5) the data can still be read up until the point you do the update. then it should be a quick hit and run lock.

This is better than a special value in the database that might not be reset if something dies.

I have used this method to ensure that a special stored procedure will never run two copies of itself at the same time. eg. via a scheduled task that might be a bit slow between invocations.

now, you may have to test a bit in order to see what performance hits you get using many sp_getapplock's
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000