The sp_getapplock secret

sp_getapplock is not very well advertised in SQL Server 2005, however it is a good way to synchronize code in a stored procedure.

Before finding out about sp_getapplock, I would SELECT from a table with an exclusive lock, like so:

BEGIN TRAN;
SELECT TOP 1 * FROM dbo.ATable with (tablockx, holdlock);
-- Do something while the lock is held, for instance:
UPDATE dbo.ATable SET FieldA = FieldA + 1 WHERE FieldB = 'something';
COMMIT TRAN;

This blocks other users from entering the section of code until the lock is released (when the transaction is committed). Normally you couldn’t do FieldA = FieldA + 1, because other users might be updating the table, however with the lock on the table you could.

This approach has some downsides

  1. Other users can’t SELECT from the table.
  2. If you rebuild indexes on the table with the “online=on” option, it will want to put a schema lock on the table, to prevent other schema changes. The exclusive lock prevents the rebuild from starting.

sp_getapplock is the built-in way to allow only one user in a section of code at a time, for example:

BEGIN TRANSACTION
DECLARE @res INT
EXEC @res = sp_getapplock @Resource = 'Lock ID', @LockMode = 'Exclusive';
IF @res >= 0
BEGIN
PRINT 'lock is held.';
END
COMMIT TRAN;

@Resource can be used for different locks that don’t interfere with each other.

You have to be careful which database you are “use”ing. SQL Server assumes that different databases have nothing to do with each other and won’t block each other.

About Clay Lenhart

I am a DBA/Technical Architect for Latitude Group and love technology.
This entry was posted in SQL Server Development. Bookmark the permalink.

18 Responses to The sp_getapplock secret

  1. Yair says:

    Hi,

    Why don’t you release the transaction after the commit?

    Yair

  2. Clay Lenhart says:

    Yair, I’m not sure how to answer your question. You could say that “releasing” the transaction is the same thing as “committing” the transaction.

    The way transactions work, “BEGIN TRAN” is committed with “COMMIT TRAN”.

    Let me know if this doesn’t answer your question.

  3. rob towne says:

    Clay, huge props, man.
    I was needing something just like this and have been going crazy trying some different workarounds. Thanks so much for sharing this..

    Robert

  4. Greg Martin says:

    Clay,

    I think Yair meant you didn’t call sp_releaseapplock() after the COMMIT which might appear necessary at first glance at sp_getapplock().

    The reason it’s not is that an applock has by default transaction scope and is released automatically when the transaction is committed or rolled back.

    Had your code included the parameter @LockOwner=’Session’ then you would have had to explicitly call sp_releaseapplock().

    Greg

  5. Pingback: DB2 application locks - dBforums

  6. I believe what you said was very logical. But, what about this?what if you added a little information? I ain’t saying your content isn’t solid, but suppose you added a title thst grabbed folk’s attention? I mean Chabt College is kinda boring. You might glance aat Yahoo’s home page and note how they reate post titles to gget people to open the links. You might add a vidreo or a pic or two to get peokple interestesd about what you’ve got to say. Just my opinion, it could make your blog a little livelier.

  7. we came across a cool website that you simply could possibly delight in. Take a search if you want

  8. A vacuum cleaner is the most reliable of cleaning resources; it may also be the more expensive. You can find various kinds of vacuum with various features. Therefore prior to buying a premier vacuum-cleaner make sure you know what class of vacuum is best for your demands.

  9. hi!,I like your writing so a lot! proportion we communicate more approximately your article on AOL? I require an expert in this space to resolve my problem. May be that’s you! Having a look forward to look you.

  10. This same algorithm is employed to discover porn video clips.

  11. Harry Mathis says:

    whoah this weblog is great i love studying your articles. Stay up the good work! You realize, a lot of individuals are hunting around for this info, you can aid them greatly.

  12. security says:

    Hi, thanks for pointing the latest Ubuntu release. This is one of the best releases and i have it installed on my computer at home. the only issue is buggy ati driver.

  13. [url=http://www.gginza.com/%E6%99%82%E8%A8%88/%E3%83%AD%E3%83%AC%E3%83%83%E3%82%AF%E3%82%B9/index.html]ケースの後部は特に面白いです、だけではないので、どのように運動を終了するが、また、方法のため、それはサファイアクリスタルケースを介して表示されます。ここでは、再び、あなたが湾曲した顔をしている、そして、あなたは運動してシャボン玉」という印象を得ます。それは大きな視覚効果と機械運動腕時計のcasebackに私は見たの最も印象的な見解の1つである。[/url]

  14. [url=http://www.msnbrand.com/goods-copy-4781.html]機能性とデザイン性の二つを満たしているバッグに興味がある人への贈り物には、仏・アニエスベーのバッグが最適です。オシャレが大好きでプリティな女性ならきっと喜んでくれますよ。マリリン・モンロー、カトリーヌ・ドヌーブなど大物女優に愛されたCHANEL製のバッグ。女性であれば、CHANELのバッグが欲しいと願っているに違いない、と思っているのは、私だけではないでしょう。[/url]

  15. [url=http://www.gowatchs.com/brand-193.html]また、Rolex(ロレックス)が現地に積極的に賛助スポーツの試合を含め、上海Rolex(ロレックス)マスターズ、オーストラリアオープンや多くの著名なゴルフ大会(例えば上海で開催されたHSBC選手権など)。また、戦略的に中国とゴルフ協会結成パートナー関係。Rolex(ロレックス)最近の重要な措置はタイガー・ウッズと手を組む。後者は婚变事件後、当時のスポンサー(例えば豪雅など)にいちいち「捨てる」。しかし、ウッズにもかかわらず、アメリカのイメージががた落ちだが、アジアには「ボール聖と見なされ」。だから、前テニス選手、現職Rolex(ロレックス)会社の宣伝とイメージ総監のArnaud Boetsch決定を彼に「賭け一注」。ブルガリコピー時計この決定はかなり読み応え。これだけではブランド「保証」はタイガー・ウッズは「新たな挑戦に直面している」に伴そのごろ、主にこの協力は一定のリスク性。冒険が積極的な企業の座右の銘も業の中で勝利者もそう。[/url]

  16. [url=http://www.fujisanbrand.com/wallet/vuitton/index_5.html]最近、ティソ訪ねたジュネーヴの時計世界は、私たちと共有専門を女性の設計の新品。ティソほとんど定価1000ドル以下、五六桁の価格-製品価格から150ドルから、ちょうど適オフィスレディの気持ち、彼女たちに付き添って過ごす歳月に代えることができる自分が学生の旧表し、異なる場合デザインのものを購入する。[/url]

  17. [url=http://www.brandiwc.com/brand-7-copy-0.html]人気の春夏シャネルベルトコピー流行ファッションや定番アイテムなどファッションアイテムが勢揃いシャネル CHANEL レディースファッション ファッション雑貨・小物 ベルト を海外通販!美しいシャネルのサングラス、格安のシャネルのサングラスコピーこれらの魅力的な割引シャネルのサングラスだけでなく、暑い夏に目を保護することも するのに十分な美しさと優雅さを追加します。シャネル サングラス カメリア・最安価格をぶった斬り![/url]

  18. Madelaine says:

    I see interesting posts here. Your site can go viral easily, you need
    some initial traffic only, you should search for: Bushano’s traffic sources

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>