How to know the lock status for a row, without updating the record
I am using the readcommited isolation level to update a row in one transaction. From other transaction, How should I find there is a lock placed on the same row without trying to update the row.
3 Solutions collect form web for “How to know the lock status for a row, without updating the record”
The only way to check for a lock is by obtaining the lock your self. This is not specific to databases, is a general issue with concurrency. Any form of API that ‘checks’ if a lock is held or not is fundamentally broken because by definition any action performed based on the result of this API check is wrong, as is base don stale, obsolete information. Knowing that lock was not held when the API checked for it does not mean is not held by the time the API returned.
You cannot possibly write a correct program if you inquire about locks. the only way to write a correct program is to acquire locks. True in SQL as in any other programming language.
Nothing stops you from attempting to acquire a lock with instant timeout (
SET LOCK_TIMEOUT 0) and handle the lock timeout error that occurs on conflict.
To get the information about lock, you can execute
VIEW SERVER STATE permission is required.
In SQL Server 2008, you can use sys.dm_tran_locks dynamic management view.
I think you can find the lock on table level not on row level
try below code it will give the table which is currently lock.
SELECT t.name as [Table Name] FROM sys.dm_tran_locks as t1 Join sys.objects o1 on o1.object_id = t1.resource_associated_entity_id Join sys.tables t on o1.object_id = t.object_id