Stored procedure with Mutex lock

Thread is closed for posting
2 posts, 0 answers
  1. Ashley
    Ashley avatar
    30 posts
    Member since:
    Nov 2012

    Posted 20 Aug 2013 Link to this post


    I have a stored procedure I'm trying to run using:
    IEnumerable<int> actualResults = this.DataSource.Context.ExecuteQuery<int>("spCore_AllocateWorkItem", System.Data.CommandType.StoredProcedure,

    Inside the stored proc I try to acquire a Mutex lock with:
    EXEC @Result = sys.sp_getapplock
                                        @Resource = 'WorkAllocator',
                                        @LockMode = 'Exclusive',
                                        @LockOwner = 'Session',
                                        @LockTimeout = 20000
        IF @Result NOT IN (0,1)
            RAISERROR ('Unable to aquire lock', 16,1)

    When I attempt to run this proc in SQL everything works fine. If I try to run this procedure using OpenAccess I get an error raised with the "Unable to acquire lock" message. Could you please let me know why I cannot acquire the lock and how to achieve this with OpenAccess?

  2. Kaloyan Nikolov
    Kaloyan Nikolov avatar
    118 posts

    Posted 21 Aug 2013 Link to this post

    Hello Ash,

    I can confirm this strange behavior. it is not related to OpenAccess and you can simulate it even in the SQL Server Management Studio if you try to execute your stored procedure. It should give you the same error.  

    I tested with the following stored procedure and it works:

    ALTER PROCEDURE [dbo].[TestAppLock]
        declare @Result int
        EXEC @Result = sys.sp_getapplock
                        @Resource = 'AnotherResoruceKey',
                        @LockMode = 'Exclusive',
                        @LockOwner = 'Session',
                        @LockTimeout = 2000
        IF @Result NOT IN (0,1)
            print @Result
            RAISERROR ('Unable to aquire lock', 16,1)
            EXEC @Result = sys.sp_releaseapplock
                        @Resource = 'AnotherResoruceKey',
                        @LockOwner = 'Session'
        return 33

    It seems that there is something to do with the resource name = WorkAllocator. When try with this value you will see that the sys.sp_getapplock stored procedure will report result value -1 which means 'The lock request timed out.'  This could happen if there is such lock already.

    If you use different key it works. This could be related to the lock retrieval/storage mechanism. Sql Server hashes the resource value and store it.

    To troubleshoot the issue you could find the system table holding the lock objects and try to found out who has created it.

    I hope this helps.

    Kaloyan Nikolov
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
Back to Top