I'm pretty new to SQL servers, so this may be a silly question, but here goes anyway…
When a user tries to edit a record (SELECT * FROM mytable WHERE key = 'mykey' FOR UPDATE NOWAIT) and it is locked by another user, I can trap the error and tell the user it is locked. No problems there. But is it possible to find out what other user (name or ip address?) is doing the locking? I've done lots of web searching and it seems like it's considered a bad idea to lock records in database applications - but I don't want my users to spend ages editing, say, a customer's address or notes, only to be told they can't save the changes because somebody else has edited it in the meantime.
Is there an easy way (or any way for that matter) to find out who owns the lock on a specific record or a table?