3

Suppose if I explicitly lock a row through sql like 

BEGIN TRANSACTION
select * from bbajobs WITH (ROWLOCK, UPDLOCK) where JID=40970

then how could I detect that the row is locked whose JID is 40970?

I search google and found some solution but those does not work. Please help me to detect row is locked or not. Thanks.

Andrzej Doyle
  • 102,507
  • 33
  • 189
  • 228
Thomas
  • 33,544
  • 126
  • 357
  • 626
  • Perhaps it would help if you referenced the solution you've tried, and why they didn't work *for you*. While Google can turn up a varying heap, well-written articles are generally correct, so discarding their advice at the first hurdle is likely to be suboptimal. – Andrzej Doyle Jan 10 '11 at 13:04
  • How are you planning to *use* this information? It's not possible, so far as I'm aware, so if we knew what you were actually trying to achieve, we might be able to help you better. – Damien_The_Unbeliever Jan 10 '11 at 13:39
  • [As requested here](http://stackoverflow.com/questions/4623436/sql-server-row-lock) please tell us your end goal. – Martin Smith Jan 10 '11 at 13:44

3 Answers3

3

Use the NOLOCK and READPAST locking hints. E.g. Assuming you have a table where the primary key is called Id

SELECT [Id] FROM [dbo].[TableName] WITH(NOLOCK)
WHERE [Id] NOT IN
(
    SELECT [Id] FROM [dbo].[TableName] WITH(READPAST)
)
Michael
  • 163
  • 1
  • 12
2

Maybe it's possible using a system view like sys.dm_tran_locks, but you usually find locks on a page and not a single row, then it would be a pain to find if a specific row is locked, if feasible at all.

Lock models are usually implemented in the application (either managed in memory in a centralized application server or in a lock table if you have independent client programs)

Alain Tésio
  • 489
  • 4
  • 8
  • 1
    Assuming that the table is not a heap the value of a rowlock in `sys.dm_tran_locks` will just be a hash and this is both not possible to map back to the original row efficiently (`%%lockres%%` can be used to do it inefficiently though) and [can have collisions](http://consultingblogs.emc.com/jamesrowlandjones/archive/2009/05/28/the-curious-case-of-the-dubious-deadlock-and-the-not-so-logical-lock.aspx). – Martin Smith Jan 10 '11 at 13:41
-1

You can get the list of non-committed transactions with the following query :

SELECT
    dm_tran_locks.request_session_id,
    dm_tran_locks.resource_database_id,
    DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
    dm_tran_locks.resource_associated_entity_id,
    dm_tran_locks.resource_type,
    dm_tran_locks.resource_description,
    dm_tran_locks.resource_associated_entity_id,
    dm_tran_locks.request_mode,
    dm_tran_locks.request_status
FROM sys.dm_tran_locks 
WHERE resource_associated_entity_id > 0
AND resource_database_id = DB_ID();

Then it's possible to get data from the locked row with :

SELECT *
FROM [your_table]
WHERE %%lockres%% = '[insert dm_tran_locks.resource_description value]';

So, you can have the number of locks on your row for your example : 

SELECT COUNT(1)
FROM sys.dm_tran_locks locks
INNER JOIN bbajobs
    ON bbajobs.%%lockres%% = locks.resource_description
WHERE
    resource_associated_entity_id > 0
AND
    -- Ensure the lock is on the same database
    resource_database_id = DB_ID()
AND
    -- Filter on row only
    resource_type = 'KEY'
AND
    -- Filter on request ID
    bbajobs.JID = 40970
Maxime
  • 14
  • 1