Stored procedure with Mutex lock

2 posts, 0 answers
  1. Ashley
    Ashley avatar
    30 posts
    Member since:
    Nov 2012

    Posted 20 Aug 2013 Link to this post

    Hi,

    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,
                    docNoParam,
                    docTableParam,
                    clientHandleParam,
                    userIdParam,
                    computerIdParam,
                    moduleIdParam,
                    serverIdParam,
                    whereParam,
                    orderParam,
                    maximumResultsParam,
                    reconnectCodeParam);

    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)
        BEGIN
            RAISERROR ('Unable to aquire lock', 16,1)
        END

    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?

    Regards,
    Ash
  2. Kaloyan Nikolov
    Admin
    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]
         
    AS
    BEGIN
        declare @Result int
        EXEC @Result = sys.sp_getapplock
                        @Resource = 'AnotherResoruceKey',
                        @LockMode = 'Exclusive',
                        @LockOwner = 'Session',
                        @LockTimeout = 2000
        IF @Result NOT IN (0,1)
        BEGIN
            print @Result
            RAISERROR ('Unable to aquire lock', 16,1)
        END
        ELSE
        BEGIN
            EXEC @Result = sys.sp_releaseapplock
                        @Resource = 'AnotherResoruceKey',
                        @LockOwner = 'Session'
        END
        return 33
    END


    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.

    Regards,
    Kaloyan Nikolov
    Telerik
    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.
  3. DevCraft banner
Back to Top