0

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. Any idea?

Muthukumar Palaniappan
  • 1,622
  • 5
  • 25
  • 49

3 Answers3

2

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thanks for the design information. Can you please detail or explain some ways about acquiring locks. – Muthukumar Palaniappan Jul 13 '12 at 06:31
  • 1
    use [lock hints](http://msdn.microsoft.com/en-us/library/ms187373.aspx). Explain your scenario and maybe we can give more details how to do it. – Remus Rusanu Jul 13 '12 at 06:49
  • 1
    As a side comment: inquiring for a row lock using the DMVs is basically impossible because you must know exactly the locking protocol and the physical structure of the database. Case in point: checking to see if row with key 'foo' is locked or no by looking up the key lock resource (and good luck compiling *that*) is not enough. A concurent transaction may had used page granularity and only locked the *page* containing the row. You cannot win this. – Remus Rusanu Jul 13 '12 at 06:53
  • I have a view screen and update screen. The first operation in the update screen is a select operation to display the data for editing, which must be blocked for the concurrent transaction. but View screen 'select' must be allowed. – Muthukumar Palaniappan Jul 14 '12 at 09:10
  • You cannot hold locks for as long as user edit screen anyway. Use [optimistic concurrency](http://en.wikipedia.org/wiki/Optimistic_concurrency_control) instead. At worst, use explicit [app locks](http://msdn.microsoft.com/en-us/library/ms189823.aspx). Absolutely do not use data locks for 'edit' screens. – Remus Rusanu Jul 14 '12 at 09:30
1

To get the information about lock, you can execute sp_lock procedure.

NOTE: VIEW SERVER STATE permission is required.

In SQL Server 2008, you can use sys.dm_tran_locks dynamic management view.

Akash KC
  • 16,057
  • 6
  • 39
  • 59
1

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
Asif
  • 2,657
  • 19
  • 25