Application locking.

Some collegues of mine want to use an application lock to ensure that a
function can only be entered once.
Any suggestions how to 'build' the application lock that the future risk of
locks/deadlocks/performance problems is minimised ?
Suggestions why this should not be undertaken ?
My viewpoint is that I would like to avoid application controlled locks, but
if they can not be avoided then use a sound and proven mechanism (RDBMS) and
a good implementation to set and test these locks.
In the past I have seen some applications using this technique, and there
deadlocks occured very rarely and loads of specialist told me that deadlocks
in such a situation could/should not occure, but they still did. So I am a
bit cautious.
suggestions please ?
Ben brugman> Some collegues of mine want to use an application lock to ensure that a
> function can only be entered once.
> Any suggestions how to 'build' the application lock that the future risk
> of locks/deadlocks/performance problems is minimised ?
You can use sp_getapplock and sp_releaseapplock to leverage SQL Server
locking primitives for application-defined locking. See the Books Online
for usage details.

> Suggestions why this should not be undertaken ?
The most obvious is that serializing access to application functionality
will reduce concurrency and overall throughput.

> My viewpoint is that I would like to avoid application controlled locks,
> but if they can not be avoided then use a sound and proven mechanism
> (RDBMS) and a good implementation to set and test these locks.
I agree that application locks should generally be avoided. There are
alternatives, such as specifying a TABLOCK locking hint to override default
row-level locking granularity or simply retrying following a deadlock.

> In the past I have seen some applications using this technique, and there
> deadlocks occured very rarely and loads of specialist told me that
> deadlocks in such a situation could/should not occure, but they still did.
> So I am a bit cautious.
>
A deadlock cannot occur if access to all of the referenced objects is truly
serialized. It is likely that there some other data access was done outside
the scope of the app lock, such as an ad-hoc query. You need to perform
deadlock analysis to identify the underlying cause.
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:urpqv$bdHHA.284@.TK2MSFTNGP05.phx.gbl...
> Some collegues of mine want to use an application lock to ensure that a
> function can only be entered once.
> Any suggestions how to 'build' the application lock that the future risk
> of locks/deadlocks/performance problems is minimised ?
> Suggestions why this should not be undertaken ?
> My viewpoint is that I would like to avoid application controlled locks,
> but if they can not be avoided then use a sound and proven mechanism
> (RDBMS) and a good implementation to set and test these locks.
> In the past I have seen some applications using this technique, and there
> deadlocks occured very rarely and loads of specialist told me that
> deadlocks in such a situation could/should not occure, but they still did.
> So I am a bit cautious.
>
> suggestions please ?
> Ben brugman
>|||Hello Dan,
Thanks for you tips, I'll see into them and probably use them. (Great help).

> A deadlock cannot occur if access to all of the referenced objects is
> truly serialized. It is likely that there some other data access was done
> outside the scope of the app lock, such as an ad-hoc query. You need to
> perform deadlock analysis to identify the underlying cause.
>
Two processes where going, the first using an application lock did a number
of
updates of one single row all within on transaction.
The second process did :
SELECT * FROM <tablename> WHERE OID = '5673828' ;
(Tablename and value are altered from the original). The deadlock was on the
first process and on this statement. Of course the select would result in
the
same row which was updated by the first process.
I was very surprised that such a simple single select statement could be
'partner' in a deadlock.
(The second process was not part of a transaction and not serialised, I
think (not sure) the
second process did this single statement to check if this row was 'in
use'.).
Thanks for your time and attention,
Ben Brugman

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:urpqv$bdHHA.284@.TK2MSFTNGP05.phx.gbl...
>|||> I was very surprised that such a simple single select statement could be
> 'partner' in a deadlock.
Without the actual deadlock info, I can only speculate. One scenario is
that the SELECT was got blocked by the first session. Then the first
session tried to acquire an incompatible lock with the first session. Keep
in mind that the chance of a deadlock increase with lock escalation and with
scans. To reduce deadlock likelihood, make sure that indexes are used
efficiently and scans are avoided. Index and query tuning can improve both
performance and concurrency.
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:eQ4sBQgdHHA.5056@.TK2MSFTNGP02.phx.gbl...
> Hello Dan,
> Thanks for you tips, I'll see into them and probably use them. (Great
> help).
>
> Two processes where going, the first using an application lock did a
> number of
> updates of one single row all within on transaction.
> The second process did :
> SELECT * FROM <tablename> WHERE OID = '5673828' ;
> (Tablename and value are altered from the original). The deadlock was on
> the
> first process and on this statement. Of course the select would result in
> the
> same row which was updated by the first process.
> I was very surprised that such a simple single select statement could be
> 'partner' in a deadlock.
> (The second process was not part of a transaction and not serialised, I
> think (not sure) the
> second process did this single statement to check if this row was 'in
> use'.).
> Thanks for your time and attention,
> Ben Brugman
>
>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote :
> Without the actual deadlock info, I can only speculate. One scenario is
> that the SELECT was got blocked by the first session. Then the first
> session tried to acquire an incompatible lock with the first session.
> Keep in mind that the chance of a deadlock increase with lock escalation
> and with scans. To reduce deadlock likelihood, make sure that indexes are
> used efficiently and scans are avoided. Index and query tuning can
> improve both performance and concurrency.
>
The point I was trying to make :
A developer should realise that locks and deadlocks can occure
when altering the database. (But try to write 'clean' code to avoid them).
I did not realise that a single simple SELECT can use locks and
can cause deadlocks.
The select statement :
caused the locks below :
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
60 10 357576312 0 PAG 1:82184 IS GRANT
60 10 357576312 0 RID 1:82184:6 S ...
And this resulted in a deadlock with another process altering the same row.
But as a developer I had not anticipated that the select could be
victem in a deadlock and had not coded for this eventuality.
As a developer I can not oversee all the coding of other developers, but
thought it was safe to assume that I could do a single select. (Wrong).
(The Oid-field was indexed, no scans, no escalations).
Now in principle when coding and making SELECTs one should always
anticipate a deadlock and code for this eventuality. In practise I think
it's a bit overdone to code for deadlocks in all select situations, this
would
make coding cumbersome.
In the documentation I have seen so far (BOL, inside and Locking and
Blocking (Kalen)),
I have never seen a warning or a suggestion that one should anticipate a
deadlock with
a statement as simple as a select. Question; should a developer be aware of
the fact
that a simple select can be deadlocked ?
Thanks for you time and attention,
I will advise the use of "sp_getapplock and sp_releaseapplock" and advise to
use
these as clean as possible. (On it's own only as the first single and the
last single
parts of coding and not within a transaction to avoid complexity and
potential
deadlocks.).
ben brugman
(The lock information was gathered at the moment the deadlock occured,
because I could not manage to gather the lock information when running
the process on it's own, because then the existence of the locks was to
short
to catch them. The altering process offcourse did hold some locks on the
same object as wel.
The situation was reproduced using the Query Analyser, where on connection
was used for the altering transaction and another connection was used to
do the select only.).
[vbcol=seagreen]
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eQ4sBQgdHHA.5056@.TK2MSFTNGP02.phx.gbl...
>|||> In practise I think
> it's a bit overdone to code for deadlocks in all select situations, this
> would
> make coding cumbersome.
I agree.

> Question; should a developer be aware of the fact
> that a simple select can be deadlocked ?
Yes, but IMHO, potential deadlocks don't need to be addressed during initial
development as long as you follow Best Practices (tune SELECT and UPDATE
queries, perform updates in consistent order). If deadlocks are
subsequently encountered with a SELECT query, take a look at the deadlock
chain to determine the underlying cause. The problem may be in the update
transaction, perhaps due to lock escalation, so the ideal place to address
the issue is in the update rather than in the select application. It's best
to resort to deadlock retry or app locks only after all other techniques are
exhausted.
BTW, in SQL 2005, you also have the option of snapshot isolation.
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:eLotlPDeHHA.1868@.TK2MSFTNGP04.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote :
> The point I was trying to make :
> A developer should realise that locks and deadlocks can occure
> when altering the database. (But try to write 'clean' code to avoid them).
> I did not realise that a single simple SELECT can use locks and
> can cause deadlocks.
> The select statement :
> caused the locks below :
> spid dbid ObjId IndId Type Resource Mode Status
> -- -- -- -- -- -- -- --
> 60 10 357576312 0 PAG 1:82184 IS GRANT
> 60 10 357576312 0 RID 1:82184:6 S ...
> And this resulted in a deadlock with another process altering the same
> row.
> But as a developer I had not anticipated that the select could be
> victem in a deadlock and had not coded for this eventuality.
> As a developer I can not oversee all the coding of other developers, but
> thought it was safe to assume that I could do a single select. (Wrong).
> (The Oid-field was indexed, no scans, no escalations).
> Now in principle when coding and making SELECTs one should always
> anticipate a deadlock and code for this eventuality. In practise I think
> it's a bit overdone to code for deadlocks in all select situations, this
> would
> make coding cumbersome.
> In the documentation I have seen so far (BOL, inside and Locking and
> Blocking (Kalen)),
> I have never seen a warning or a suggestion that one should anticipate a
> deadlock with
> a statement as simple as a select. Question; should a developer be aware
> of the fact
> that a simple select can be deadlocked ?
> Thanks for you time and attention,
> I will advise the use of "sp_getapplock and sp_releaseapplock" and advise
> to use
> these as clean as possible. (On it's own only as the first single and the
> last single
> parts of coding and not within a transaction to avoid complexity and
> potential
> deadlocks.).
> ben brugman
> (The lock information was gathered at the moment the deadlock occured,
> because I could not manage to gather the lock information when running
> the process on it's own, because then the existence of the locks was to
> short
> to catch them. The altering process offcourse did hold some locks on the
> same object as wel.
> The situation was reproduced using the Query Analyser, where on connection
> was used for the altering transaction and another connection was used to
> do the select only.).
>
>|||
> Thanks for you time and attention,
> I will advise the use of "sp_getapplock and sp_releaseapplock" and advise
> to use
> these as clean as possible. (On it's own only as the first single and the
> last single
> parts of coding and not within a transaction to avoid complexity and
> potential
> deadlocks.).
Just tried this combination. Well the "sp_getapplock and sp_releaseapplock"
must be used within a transaction. So I think they should be used as first
and
last statement within the transaction.
(My problem now is because they fall within a transaction, the RDBMS does
not need to 'activate' these constructs until commit time. The RDBMS
probably
does use these locks the moment the stored procedure is run, but this is an
assumption of me. And with assumptions I have been wrong in the past).
ben brugman|||> Just tried this combination. Well the "sp_getapplock and
> sp_releaseapplock"
> must be used within a transaction. So I think they should be used as first
> and
> last statement within the transaction.
By default, app locks are in the scope of the current transaction and
released after sp_releaseapplock or COMMIT/ROLLBACK. However, you can
specify @.LockOwner='Session' so that the app lock is released after
sp_releaseapplock or the session closed.
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:eCUPKOFeHHA.1216@.TK2MSFTNGP03.phx.gbl...
>
> Just tried this combination. Well the "sp_getapplock and
> sp_releaseapplock"
> must be used within a transaction. So I think they should be used as first
> and
> last statement within the transaction.
> (My problem now is because they fall within a transaction, the RDBMS does
> not need to 'activate' these constructs until commit time. The RDBMS
> probably
> does use these locks the moment the stored procedure is run, but this is
> an
> assumption of me. And with assumptions I have been wrong in the past).
>
> ben brugman
>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F298208B-2DFD-4D8D-85A6-3DFC2979BD1E@.microsoft.com...
> By default, app locks are in the scope of the current transaction and
> released after sp_releaseapplock or COMMIT/ROLLBACK. However, you can
> specify @.LockOwner='Session' so that the app lock is released after
> sp_releaseapplock or the session closed.
Thanks for this suggestion.
Just tried this suggestion and it's exactly what I want. (I guess). Thanks.
I like this better than the lock within a transaction. (Where I do not now
when the lock will actually be set by the RDBMS, allthough is probably
my personal paranoia).
For your other remark, I come from the old school and would say that
you have to eliminate 'all' danger from potential deadlocks before the
coding goes into production.
So rather than to minimize deadlocks until the point they become so
rare that you can never catch them. I would like to follow a pattern
where all deadlocks have a reasonable change of being caught during
a long testrun and than add code that the deadlocks do not cause
damage during production).
But with deadlocks affecting 'SELECT' statements this is less realistic.

I do like snapshot isolation for queries, selects and reports. Probably
would
prevent the deadlock occuring with the 'SELECT', although I do not know if
this is garanteed.
But with snapshot isolation one always should keep in mind that this is not
'serializable' . (Serializeble as described in the 1992 SQL Draft). So for
transactions where decisions are made on content of the database, the
snapshot
isolation does not give adequate protection against 'serializable' errors.
Thanks for sharing your knowledge and time,
ben brugman
Remark:
As you can see I am not easy satisfied in this area of locking. This stems
from
a few reasons. Working with Oracle I learned the hard way that their
'serializable'
isolation level is not as described by the 1992 SQL Draft. Working with
Oracle
I discovered that deadlocks can occur even if you work with distinct
datasets, although
most specialist would deny this possible. (If rows are situated in the same
block they
use the same resourses for the administration of locks).
In SQL-server I have seen developers creating deadlocks within one
application
where the writes within the application would block the reads within the
same
application. (Because they used different connections, and the developers
did this
because in Oracle this was no problem). With SQL server I had the experience
with the deadlock with the SELECT, also a situation where loads of
specialist
would say that this can not happen. In General books and documentation do
describe deadlocks which are completely plausible, but do not mention the
more unlikely deadlocks. Some object oriented books even give a completely
wrong description of how locks and deadlocks work.
[vbcol=seagreen]
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCUPKOFeHHA.1216@.TK2MSFTNGP03.phx.gbl...
>