SQL Server Performance Forum – Threads Archive
Detecting another lock on a record
Here’s the conundrum I’m faced with: <br /><br />I have a centralized db and distributed apps that all connect to the db. When a user selects a product for editing in the db, I need to prevent anyone else from updating the record. This is accomplished simply enough with a transaction and an update lock. The problem I’ve run into is when another user selects the same product for editing. I would like to examine the current locks, determine if there’s a lock on that product and inform the second user that the record has been locked for editing (and then maybe allowing them to view the data in a non-edit mode).<br /><br />With the below query I can get a list of all the locks and examine the key (record) locks for a particular object (table); however there doesn’t seem to be a way to decode the hash back to the primary key (which would of course allow me to do what I want).<br /><br />USE MASTER<br />GO<br /><br />SET NOCOUNT ON<br /><br />DECLARE @objid int,<br /> @indid int,<br /> @dbid int,<br /> @string Nvarchar(255)<br /><br />DECLARE @databaseName varchar(64)<br />SET @databaseName = ‘myDB'<br /><br />CREATE TABLE #locktable (spid smallint, loginname nvarchar(20), dbid int, dbname nvarchar(20), <br />objId int, ObjName nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, Type nvarchar(4), Resource nvarchar(16), Mode nvarchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />)<br /><br />INSERT #locktable (spid, loginname, dbid, dbname, objId, ObjName, Type, Resource, Mode)<br />SELECT convert (smallint, l.req_spid), coalesce(substring (s.loginame, 1, 20),”), <br />l.rsc_dbid, substring (db_name(l.rsc_dbid), 1, 20), l.rsc_objid, ”, substring (v.name, 1, 4), <br />substring (l.rsc_text, 1, 16), substring (u.name, 1, <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />FROM master.dbo.syslockinfo l, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u, master.dbo.sysprocesses s<br />WHERE l.rsc_type = v.number<br />AND v.type = ‘LR'<br />AND l.req_status = x.number<br />AND x.type = ‘LS'<br />AND l.req_mode + 1 = u.number<br />AND u.type = ‘L'<br />AND req_spid = s.spid<br />ORDER BY spID<br /><br />DECLARE lock_cursor CURSOR<br />FOR SELECT dbid, ObjId FROM #locktable WHERE Type <>’DB’ and Type <> ‘FIL'<br /><br />OPEN lock_cursor<br />FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId<br />WHILE @@FETCH_STATUS = 0<br /> BEGIN<br /><br /> SELECT @string =<br /> ‘USE ‘ + db_name(@dbid) + char(13)<br /> + ‘update #locktable set ObjName = name'<br /> + ‘ from sysobjects where id = ‘ + convert(varchar(32),@objid)<br /> + ‘ and ObjId = ‘ + convert(varchar(32),@objid)<br /> + ‘ and dbid = ‘ + convert(varchar(32),@dbId)<br /><br /> EXECUTE (@string)<br /><br /> FETCH NEXT FROM lock_cursor INTO @dbid, @ObjId<br /> END<br />CLOSE lock_cursor<br />DEALLOCATE lock_cursor<br /><br />SELECT loginname, objName, resource FROM #locktable<br />WHERE dbname = @databaseName<br />AND type = ‘Key'<br />AND mode = ‘U'<br /><br />DROP TABLE #locktable<br />GO<br /><br />I’m delving into new territory for myself in SQL and SQL Server, so I feel like there must be something readily available to help me determine the record that was locked. Is there or do I need to pursue some other route?<br /><br />Thanks,<br />Jason Acosta<br />Hi Jason, You’d have to write your own thing to do this. You can’t rely on SQLServer locking since it uses optemistic locking, ie when the user does the select a shared lock is taken out for the reading itself, but is released fairly quickly. Then when the user does the update it will update the data, taking out an update/exclusive lock, but again only briefly. So you’d need to add your own columns to record who is editing this record, and perhaps a datetime as to when they started to edit this record… BUT you then also have the problem of what happens when someone goes to lunch or just leaves their PC never to return… You’d have to have a way for someone to be able to unlock the record Cheers
Twan
Twan, Thanks for the rapid response. I had definitely considered creating columns to track which record was being locked and when and the thought of folks going to lunch and leaving it locked crossed my mind too. I was really hoping there was was to manage this through the database itself (complete with a forced timeout of locks). -Jason
nope I’m not aware of any database which satisfactorily offers this as an out of the box feature Cheers
Twan
]]>