[Solved] Stored procedure with Mutex lock

[Ashley]

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

[Kaloyan Nikolov]

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