locked
sp_getapplock appears to fail silently RRS feed

  • Question

  • Hello all,

    I seem to be experiencing a case where sp_getapplock fails without returning an error code.

    I am executing sp_getapplock against SQL Server 2005 with the following parameters:

    EXEC @LockResult = sp_getapplock @Resource = 'MyResource', @LockMode = ''exclusive'', @LockOwner = ''session'', @LockTimeout = 3000, @DbPrincipal = ''dbo''

    Later, I release the app lock:

    EXEC @LockResult = sp_releaseapplock @LockOwner = ''Session'', @Resource = ''MyResource'', @DbPrincipal = ''dbo''


    This works fine in dozens of SQL Server 2005 installations. For SQL Server 2000 installations, I use a slightly modified query that omits the DbPrinciple parameter.  That works fine as well.

    But on a small number of SQL Server 2005 installations used by our customers, sp_getapplock returns 0, indicating success ("The lock was successfully granted synchronously").  But when I call sp_releaseapplock, it fails with the message "Cannot release the application lock (Database Principal: 'dbo', Resource: 'MyResource') because it is not currently held"

    This suggests to me that sp_getapplock didn't actually succeed.  We have a theory that this is because of the permissions configured for the dbo user.  But I haven't found a way to configure my SQL Server dbo account so that I can reproduce this error.  I just installed a new SQL Server 2005 instance and this problem does not occur.

    Any ideas? 

    Thank you in advance,
    Dave
    Thursday, January 28, 2010 9:37 PM

Answers

  • Everyone:

    This was escalated to Microsoft SQL Server support.  Here is a summary of the results of the investigation.  Hopefully it will help someone else one day.

     

    An issue in the SQL OLE DB provider that is supplied with Windows 2003 SP2 (specifically, MDAC version 2.82) can cause SQL queries to fail in unusual and unpredictable ways.

    Microsoft corrected this issue before we reported it, but the correction is not yet in a Service Pack.  A hotfix is available at http://support.microsoft.com/kb/942211.  This hotfix must be applied to the machine running the application that uses ADO to communicate with SQL Server.


    The Problem
    =========

    SQL Server client applications using ADO to communicate with SQL Server were generating the following error:


    Cannot release the application lock (Database Principal: 'dbo', Resource: 'XXXXXXXXX') because it is not currently held (EOleException, 0051777E)


    An examination of a SQL Profiler trace showed the following queries being executed at the time of the error.  Note the questions marks at the end of the first statement are unprintable (garbage) characters:

    SET FMTONLY ON DECLARE @LockResult int  EXEC @LockResult = sp_getapplock @Resource = 'XXXXXXXXX', @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = 30000, @DbPrincipal = 'dbo'  SELECT @LockResult as LockResult    SET FMTONLY OFF ????????

    <SQL Output>
    Error: 102, Severity: 15, State: 1
    Incorrect syntax near '0xffff'.


    (Later, when releasing the app lock)

    <SQL Output>
    Error: 1203, Severity: 20, State: 1
    Error: 1223, Severity: 16, State: 1
    Cannot release the application lock (Database Principal: 'dbo', Resource: '1001001659974') because it is not currently held.

     
    Further examination of the SQL Profiler trace showed that many SQL statements were failing with similar errors:


    SET FMTONLY ON select * from MyTable order by MyTableID   WHERE 1=2  SET FMTONLY OFF
    Incorrect syntax near the keyword 'WHERE'.

     
    SET FMTONLY ON UPDATE MyTable SET Status =   SET FMTONLY OFF rce = SQL
    Incorrect syntax near the keyword 'SET'.
    Incorrect syntax near 'rce'.


    The errors above were never reported back to the our applications: these SQL statements are automatically generated by ADO and failures are by design hidden from us.  But for some reason the sp_getapplock statement was failing to execute.  The sp_releaseapplock therefore could not release the application lock.

    As you can see, there is no reason to assume that any of the other SQL statements above are not prone to failure for similar reasons.
     

    Correcting the issue
    ==============

    To correct this issue, install the hotfix available from the link above.  Be sure to download the correct hotfix for the operating system version in use (32-bit is x86, 64-bit is x64).   The client machine must be rebooted after applying the hotfix.

    It is not yet known whether the hotfix corrects ALL of the failure cases above.  If you look at the SQL queries, you will see that there are several different kinds of failures:

    1. Garbage characters or parts of other queries can be appended to a query.

    2. An additional WHERE clause can be appended to a query that already has a WHERE clause - so far we are not aware of this causing any actual failures in our applications.

    3. A WHERE clause can be appended to a query after the ORDER BY clause - so far we are not aware of this causing any actual failures in our applications.

    4. Nested queries are truncated (shortened) improperly - so far we are not aware of this causing any actual failures in our applications.

     

    Thanks for your responses and sorry for forgetting to update the group,

    Dave

    • Marked as answer by David F. Robb Wednesday, June 2, 2010 7:31 PM
    Wednesday, June 2, 2010 6:22 PM

All replies

  • Dave, 

    Could you check the build levels on your SQL Servers?  It would be interesting if the problem servers were at a different build from those that work well.  

    I found one person reporting that SQL Server 2005 SP2 was giving them problems with sp_getapplock, but their symptoms were different from yours.  (And no one provided a solution, so I don't know what happened.) http://www.windows-tech.info/15/42d9aea3fa862b3d.php

    Also, the Atom Server people where having a problem back in 2008.  http://atomserver.codehaus.org/releases.html

    Their fix log says the following:  "Corrected a problem when using SQLServer's sp_getapplock stored procedure. First, sp_getapplock can fail silently, which could cause an applock to not be released, so we now properly check the return code. Second, it appears that sp_getapplock interacts oddly with some JDBC drivers (JTDS), such that you must call SQL against a Table to get the transaction started, otherwise sp_getapplock returns a -999 error and doesn't take out a lock at all."

    FWIW,
    RLF
    • Marked as answer by Alex Feng (SQL) Wednesday, February 3, 2010 12:09 PM
    • Unmarked as answer by David F. Robb Wednesday, June 2, 2010 7:31 PM
    Monday, February 1, 2010 2:12 PM
  • Russell,

    Thank you for the prompt answer!  My reply is not so prompt because first I had the flu and then I had to move onto other things.

    We did verify that the SQL Server build was the latest released build, and was the same build that I was running locally.

    I did see the web sites that you referenced during my initial searches for this issue.  I found the atomserver page most intriguing, but I think they meant to say "First, *sp_releaseapplock* can fail silently, which could cause an applock to *not be released*."  I can't see how sp_getapplock failing silently could cause a lock to not be released.  Presumably if sp_getapplock fails a lock was never taken in the first place.

    I am considering raising this issue with Microsoft technical support, but am anxiously awaiting a Profiler trace conclusively demonstrating the problem before I attempt that.

    Thank you for your input!
    Dave
    Tuesday, February 16, 2010 7:29 PM
  • Everyone:

    This was escalated to Microsoft SQL Server support.  Here is a summary of the results of the investigation.  Hopefully it will help someone else one day.

     

    An issue in the SQL OLE DB provider that is supplied with Windows 2003 SP2 (specifically, MDAC version 2.82) can cause SQL queries to fail in unusual and unpredictable ways.

    Microsoft corrected this issue before we reported it, but the correction is not yet in a Service Pack.  A hotfix is available at http://support.microsoft.com/kb/942211.  This hotfix must be applied to the machine running the application that uses ADO to communicate with SQL Server.


    The Problem
    =========

    SQL Server client applications using ADO to communicate with SQL Server were generating the following error:


    Cannot release the application lock (Database Principal: 'dbo', Resource: 'XXXXXXXXX') because it is not currently held (EOleException, 0051777E)


    An examination of a SQL Profiler trace showed the following queries being executed at the time of the error.  Note the questions marks at the end of the first statement are unprintable (garbage) characters:

    SET FMTONLY ON DECLARE @LockResult int  EXEC @LockResult = sp_getapplock @Resource = 'XXXXXXXXX', @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = 30000, @DbPrincipal = 'dbo'  SELECT @LockResult as LockResult    SET FMTONLY OFF ????????

    <SQL Output>
    Error: 102, Severity: 15, State: 1
    Incorrect syntax near '0xffff'.


    (Later, when releasing the app lock)

    <SQL Output>
    Error: 1203, Severity: 20, State: 1
    Error: 1223, Severity: 16, State: 1
    Cannot release the application lock (Database Principal: 'dbo', Resource: '1001001659974') because it is not currently held.

     
    Further examination of the SQL Profiler trace showed that many SQL statements were failing with similar errors:


    SET FMTONLY ON select * from MyTable order by MyTableID   WHERE 1=2  SET FMTONLY OFF
    Incorrect syntax near the keyword 'WHERE'.

     
    SET FMTONLY ON UPDATE MyTable SET Status =   SET FMTONLY OFF rce = SQL
    Incorrect syntax near the keyword 'SET'.
    Incorrect syntax near 'rce'.


    The errors above were never reported back to the our applications: these SQL statements are automatically generated by ADO and failures are by design hidden from us.  But for some reason the sp_getapplock statement was failing to execute.  The sp_releaseapplock therefore could not release the application lock.

    As you can see, there is no reason to assume that any of the other SQL statements above are not prone to failure for similar reasons.
     

    Correcting the issue
    ==============

    To correct this issue, install the hotfix available from the link above.  Be sure to download the correct hotfix for the operating system version in use (32-bit is x86, 64-bit is x64).   The client machine must be rebooted after applying the hotfix.

    It is not yet known whether the hotfix corrects ALL of the failure cases above.  If you look at the SQL queries, you will see that there are several different kinds of failures:

    1. Garbage characters or parts of other queries can be appended to a query.

    2. An additional WHERE clause can be appended to a query that already has a WHERE clause - so far we are not aware of this causing any actual failures in our applications.

    3. A WHERE clause can be appended to a query after the ORDER BY clause - so far we are not aware of this causing any actual failures in our applications.

    4. Nested queries are truncated (shortened) improperly - so far we are not aware of this causing any actual failures in our applications.

     

    Thanks for your responses and sorry for forgetting to update the group,

    Dave

    • Marked as answer by David F. Robb Wednesday, June 2, 2010 7:31 PM
    Wednesday, June 2, 2010 6:22 PM
  • Thanks very much for coming back with the answer.   Frightening kind of mysterious problem. - RLF
    Wednesday, June 2, 2010 7:41 PM