Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to tell if a row is locked?

Author  Topic 

sam4
Starting Member

7 Posts

Posted - 2008-04-14 : 19:29:56
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 - 2008-04-14 : 20:22:01
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

7 Posts

Posted - 2008-04-15 : 12:44:21
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

11752 Posts

Posted - 2008-04-15 : 13:12:56
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

38200 Posts

Posted - 2008-04-15 : 13:18:09
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 - 2008-04-16 : 04:11:46
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

7 Posts

Posted - 2008-04-16 : 11:27:17
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
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-04-16 : 12:32:56
"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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-16 : 12:57:13
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 - 2008-04-16 : 19:17:34
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-16 : 19:22:31
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

7 Posts

Posted - 2008-04-18 : 00:54:00
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

4 Posts

Posted - 2008-04-24 : 01:29:09
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
   

- Advertisement -