November 19, 2001 at 7:34 am
Dear all,
How can I determine which user has a row level lock against a specified record?
I've managed to create the lock itself but after trawling sysprocesses, syslockinfo, syslocks, sysobjects etc can only seem to find references back to the table and not the record itself. If more than one user is accessing the table, I've no idea as to who is holding the lock.
Ideally I'm looking to create an sp. which will give the name of the person holding the record at the point a new request to it, is made.
Thanks in advance
Chris Fenton
November 19, 2001 at 10:08 am
sp_lock doesn't work?
This should return the sPId and the objid and type. As for matching these with specific rows for an insert, that might take some doing. The resource column in sp_lock gives the row identifier.
Steve Jones
November 19, 2001 at 6:21 pm
This sp
sp_nrLocks
Will give you a list of the locks held and spid info and can give the commands that the spid is executing.
You can maybe get the page that is being locked and use dbcc page to find the record - sounds like a lot of effort though.
If you just want to find which user is holding a lock on a record you can usually do it by trying to access therecord from another spid with an incompatible lock - you can then easily find which spid is blocking you and trace that back to the user.
Another way is to just kill spids or tell users to get off the system until the record is released.
Cursors never.
DTS - only when needed and never to control.
November 21, 2001 at 10:16 am
Although sp_locks does give the lock details how can I determine which record is actually being held by the process. The resource attrib. does indeed provide the rid but how can I use this to determine the record with in my table? I know that within other RDBMS each row in every table is given a unique internal key (RowId) and that this can be referenced at querying level. Is this true of the RID in SQL Server (7.0) and if so how is this referenced in query selects. e.g. Select * FROM <<Table>> where RID = (06003f7fd0fb).
As for the lock itself, perhaps an example makes my problem a little clearer..
Given the sp...
CREATE PROC usp_Select_Employee( @EmpId INT)
AS
SELECT *
FROM Employee (UPDLOCK)
WHERE EmpId = @EmpId
If User A submits a call to this sp via the following..
BEGIN TRAN Get_Emp
EXEC usp_Select_Employee 1
Now, viewing sp_lock or E.M I see that the lock is created and that it is given it's own spid. If User B now submits the same proc. call I get a locking contention and a block is created until either User A submits
rollback/commit tran or User B cancels their tran or is timed out.
However, if I were to use the READPAST option with in my sp. and the same scenario
is applied User A creates the lock but User B returns nothing. No block occurs as B has ignored all locked records.
Once the block has occured, I appreciate that I can track down the blocking processes and determine the culprit but I would like to avoid any hanging as this either involves a very active DBA or some background process running round deciding who to kill off.
One consideration I had was to create another sp. to be called prior to the first..
CREATE PROC usp_Check_Lock_Status(@EmpId INT,
@Blckd_By VARCHAR(50) OUTPUT)
AS
IF NOT EXISTS
(SELECT 'x'
FROM Correspondence (updlock, readpast)
WHERE Correspondence_Id = @p_Crsp_Id)
-- Record is locked by user
SET @p_Blocked_By = 'User'
to query the system tables and derive the name of the owner of the lock but so far I've had no luck.
Is it possible therefore to manage locking contentions pro-actively rather than re-actively when blocks have already occured and the system is hanging?
Once again, Thanks in advance
Chris
November 21, 2001 at 10:37 am
If you are holding this lock for any period of time, then you have a desgin problem. You should not begin a transaction that needs to wait for any input or response from anyone. It defeats the purpose of SQL Server. If you need to prevent updates to a row while a user does something, add a flag to the row and set it as a semaphore.
The ROWID is unique, it is how SQL actually addresses the row, but to map that back to the data is not easy. You would have to find the page, run a dbcc command to get the page data and parse that to find some column value. Not a good idea in a stored procedure.
Steve Jones
November 22, 2001 at 2:59 am
Cheers Steve/Nigel
Flags it is then.
Regards
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply