I am writing in regard to this post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52798Which 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).

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.

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

your scenarion is usualy implemented in this way:in your table add a column IsEditing or something similar.default value for this column is 0when 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.

>>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).

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 bugsd) 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'.

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 :)

"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.

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."

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.

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 using3) if the app dies in the process (like that ever happens) the lock is _automaticly_ released4) you don't have to deal with 'non-blocking (dirty) reads' and query optimizer hints5) 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