Home Can I lock 2 tables at the same time?

# Can I lock 2 tables at the same time?

Sascha
1#
Sascha Published in 2013-11-21 14:29:51Z
 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: Locks can only be set in tbl_lock, if there are no users are registered in tbl_dialog Dialog usage can only be registerd in tbl_dialog if not lock ist set in tbl_lock 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
Bogdan Sahlean
2#
Bogdan Sahlean Reply to 2013-11-21 15:40:59Z
 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
 You need to login account before you can post.
Processed in 0.297412 second(s) , Gzip On .