SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Serializing Procedure Calls Without Uneccesssary Blocking


Serializing Procedure Calls Without Uneccesssary Blocking

Author
Message
R Michael
R Michael
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 275
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/rcary/2649.asp

SQL guy and Houston Magician
tymberwyld
tymberwyld
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 275
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.



noeld
noeld
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9564 Visits: 2048
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
R Michael
R Michael
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 275
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
R Michael
R Michael
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 275
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
tymberwyld
tymberwyld
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 275
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.



R Michael
R Michael
SSC-Addicted
SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)SSC-Addicted (429 reputation)

Group: General Forum Members
Points: 429 Visits: 275
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
tymberwyld
tymberwyld
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 275
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!



jswong05
jswong05
SSC Veteran
SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)SSC Veteran (229 reputation)

Group: General Forum Members
Points: 229 Visits: 476
[b]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
:-P
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search