Detecting Lock on the record .

Question

  if a row has lock(exclusive(x)) Lock then How to detect if a record in databse table has a lock?

 Plz rpl..

Answer 1

SSY.DM_TRAN_LOCKS DMV in SQL 2005 and above provides such capabilities.

---

Arthur My Blog

Answer 2

  how about sql 2000.? Plz. give me query example for using above(consider i set lock on row having Empname "Jack  Laymen" databse Employee and table personel info) 

Answer 3

In SQL Server 2000 you can use sp_lock or SP_WHO2

You do not set a lock easily at will, SQL Server manages this for you, and SQL Server 2000 does not have an actual row level locking fully implemented per se

---

Arthur My Blog

Answer 4

 ok..plz tell me how to use sp_lock and sp_who2 in sql server2005 and plz give me example to detect if row has lock or not?

Answer 5

It is not very straighforward topic to cover in a short forum reply, but I have found several excellent links for you:

http://www.mssqltips.com/sqlservertip/1359/locking-and-blocking-scripts-in-sql-server-2000-vs-sql-server-2005/ and

http://www.simple-talk.com/sql/database-administration/the-dba-as-detective-troubleshooting-locking-and-blocking/

---

Arthur My Blog