how to detect if a row is locked in sql server table?

Suppose if I explicitly lock a row through sql like

B开发者_运维问答EGIN 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.

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)
)

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)

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