CODEWITHSUNDEEP
sql-servert-sqllocking
Sascha
Sascha

Reputation: 1218

Can I lock 2 tables at the same time?

I need to implement a locking scheme in my application. A user may only open a dialog/form, if it hasn't been locked. On the other hand a lock may only be set, if there is no dialog currently open, that corresponds to that lock.

My approach so far: I have a sql server table tbl_lock with column lockname where I maintain locks and a table tbl_dialog (user, dialogname) where I create a row whenever a user opens a dialog.

To create a lock I do the following: 1) Begin a transaction 2) select * from tbl_dialog with (SERIALIZABLE) where dialogname=@somename 3) insert a lock record for the dialog @somename in tbl_lock if the prio select returns no records 4) commit transaction

The problem ist, that I would also like to prevent that someone tries to open a dialog when some other user is trying to set a lock. I could repeat the above 4 steps and select tbl_lock with serializable and insert a record in tbl_dialog, but I fear the I run into a deadlock if 2 concurrent transactions execute step thus locking both tables tbl_dialog and tbl_lock at the same time.

Could I lock both tables in step 2 at once? Is there a smarter way to solve this problem?

Best Regards, Sascha

EDIT: Based on Bogdan's comment I would implement the following SP to set a lock:

Create procedure CreateLock 
    @dialogname nvarchar(10),
    @lockname nvarchar(10)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result int;
    DECLARE @locksuccess int = 0;

    begin tran;

    EXEC @result = sp_getapplock @Resource = 'myapplockmanagement', @LockMode = 'Exclusive', @LockTimeout=3000;
    if @result>=0
    BEGIN
        -- Ensure no user has opened the dialog @lockname
        if not EXISTS(select 1 from tbl_dialog where dialogname=@dialogname)
        BEGIN
            if not EXISTS(select 1 from tbl_lock where lockname=@lockname)
            BEGIN
                insert into tbl_lock (lockname) values (@lockname);
                set @locksuccess = 1;
            END
        END
        commit;
    END
    ELSE
    BEGIN
        rollback;
    END

    return @locksuccess;
END

If I understood sp_getapplock correctly, myapplockmanagement will be locked exclusively thus locking all other threads out. If the application lock could be aquired I can safely check my tbl_dialog and eventually insert a new row in tbl_lock, right?

So the next Procdure would be:

Create procedure RegisterDialogUsage 
    @dialogname nvarchar(10),
    @lockname nvarchar(10)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @result int;
    DECLARE @registersuccess int = 0;

    begin tran;

    EXEC @result = sp_getapplock @Resource = 'myapplockmanagement', @LockMode = 'Exclusive', @LockTimeout=3000;
    if @result>=0
    BEGIN
        -- Ensure that no lock is set.
        if not EXISTS(select 1 from tbl_lock where lockname=@lockname)
        BEGIN
            insert into tbl_dialog (dialogname) values (@dialogname);
            set @registersuccess = 1;
        END
        commit;
    END
    ELSE
    BEGIN
        rollback;
    END

    return @registersuccess;
END

So, if I am not mistaken these two SPs do exactly what I like:

  1. Locks can only be set in tbl_lock, if there are no users are registered in tbl_dialog
  2. Dialog usage can only be registerd in tbl_dialog if not lock ist set in tbl_lock
  3. Thanks to sp_getapplock there wont't be concurrency problems.

Is that right? (Corresponding SPs for removing the entries in tbl_lock and tbl_dialog had to be implemeted two...)

Sascha

Upvotes: 1

Views: 1460

Answers (1)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

I would use application locks:

http://technet.microsoft.com/en-us/library/ms189823.aspx

http://sqlblog.com/blogs/louis_davidson/archive/2007/05/07/using-application-locks-to-implement-a-critical-section-in-t-sql-code.aspx

Upvotes: 2

Related Questions