October 16, 2006 at 9:38 am
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/rcary/2649.asp
SQL guy and Houston Magician
October 16, 2006 at 9:39 pm
Wow, how have I never heard of this? I'm having trouble wrapping my head around this, could you perhaps give an easy "real-world" example of how it might be used?
Currently, we're locking at the record level in one of our apps but we use a Column called "SessionLockID" or something. It'd be nice to get rid of this methodology if possible.
October 17, 2006 at 8:41 am
Once again I have to opose to the
IF not exists()
Insert ...
Technique. That statement does *not* guarranties that after two connections return from the "If" part with the same key, one of them is going to fail and if that is the case, what you really do is to perform an *atomic* transaction on the table, in other words combine the insert with the exists in a single query not in separated statements.
Second the pessimistic locking strategy, which is what you are doing *does* *block* others so I am not so sure of how appropriate the title is.
Other than that, I believe that your discussing of the theme is a very good introduction to something that most people never get to talk about and you did a very good job.
Cheers,
* Noel
October 17, 2006 at 9:33 am
Thank you both very much for your feedback!
Noel:
I agree, the
IF not exists()
Insert...
Technique is definitely flawed; I have seen this approach fail many times. Very good point about combining the existence check with the insert!
The locking hints in the second example will also, most definitely, block other processes. As you know, the blocking in that example is deliberate because we wish to ensure that multiple instances of a proc cannot run simultaneously. You are also correct that this can still produce unexpected results (blocking attempts to lock other rows).
The benefit is that, because you are simply acquiring a lock on a table that serves no other purpose than to be locked, you can block processes you wish you block, without blocking access to 'real' tables (and thus blocking processes you may not wish to be blocked).
Perhaps I should have been clearer in the article. The first two--I believe to be flawed--examples were approaches that I have seen used in the past. For this reason I concluded that if you needed to serialize calls in this manner, it would be best to use sp_getapplock (rather than the first two examples).
tymberwyld:
An example that springs to mind is a variation of what I first used this technique for. Imagine you have a 50 machines that are all retrieving sets of data from SQL server to process and then act on (e.g. sends an email to a customer). Also imagine that these sets of data are provided by a single stored procedure (this procedure will retrieve the N oldest rows, and then update the datestamp). As each machine completes processing of its respective set of data, it hits the PROC for a new set. Occasionally two machines will hit the database at the same time and end up with sets of data that overlap each other causing the applications to act twice on the same row(s) of data. By ensuring that only one application can call the proc at a time, you ensure that every application has a distinct set of data to work with.
I will try to think of some more examples where this might be useful.
I hope this helps!
-Robert Cary
SQL guy and Houston Magician
October 17, 2006 at 9:53 am
I would be interested to know a little more about your SessionLockID column. I would guess that you are using this column to tell a client application weather a record should be temporarily read-only or not.
Tell me more about it and maybe we could look at some alternatives if you would like.
Thanks.
Robert
SQL guy and Houston Magician
October 17, 2006 at 11:03 am
Yes, you are correct. The SessionLockID is either the MAC Address of the PC or (in Citrix or Terminal Services) a Citrix assigned SessionID or Terminal Services Session ID. The one issue is that these session locks can remain in the tables if the application crashes unexpectedly. It seems like using your locking mechanism it would be easier to manage which locks need to be released.
October 17, 2006 at 11:41 am
SP_getapplock needs to be run inside a transaction.
I'm not convinced that this would be the best idea but...
In order for you to make this work for your situation, you would need to maintain a connection to the database and leave a transaction uncommitted while the user is editing that record. When the users is finished editing/viewing, the transaction is committed. This 'hanging' transaction should ONLY have the call to sp_getapplock (exclusive mode)!
When the transaction is committed, the lock is released and other clients can then acquire the lock. If the connection is broken, the lock is released (this is because SQL server will rollback open transactions when a connection is lost)
Many people (myself included) will tell you that it is generally a bad idea to leave a transaction open like that, the main reason is it can cause blocking. If all you are doing is EXECing sp_getapplock, blocking won't really be an issue. It also eats up resources, holding connections open indefinitely. These are things you would need to consider. I'm sure others will add to this list of things to consider/reasons not to do this.
On the upside, you could use APPLOCK_TEST to check for a lock in your form constructor and if the user crashed out, the lock would be released (although it depends where the crash was, in the application or the citrix/terminal services client).
An alternative would be to write a SessionLockID and a datestamp. That way you could time out locks after a set period of time.
SQL guy and Houston Magician
October 17, 2006 at 12:26 pm
Thanks for clearing that up. It sounds like we'd need to stick with the current solution because all of the apps are disconnected. We do use a SessionLockDateTime as well so cleanup isn't a huge issue.
Thanks!
May 11, 2012 at 7:58 am
Occasionally two machines will hit the database at the same time and end up with sets of data that overlap each other causing the applications to act twice on the same row(s) of data.
Is that SQL-92 defined concurrency and isolation level is trying to do !!! Nothing is "at the same time" at nano level.
Jason
http://dbace.us
😛
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply