Determine which user has locked a record

[David C. Barber]

I'm trying to determine which user has locked a given record from VB6.

I know I can use sp_lock and sp_who, and match up the data to determine
which users have locked records in my database, however I haven't seen a way
to match the specific user to the specific record. What am I missing here?

Thanks!

*David*

[dbmonitor]

Use the sysprocesses table and check the blocked column. This column
will contain the process id of the process holding the lock. A simple
check for that process should tell you who has the lock.

--
David Rowland
MS SQL Server DBMonitor author
http://dbmonitor.tripod.com

[Dan Guzman]

You can get to the table and index level by using the objid and indid.
However, many lock resources are actually hashed values so you'll have
difficulty identifying a specific row.

However, the need to do this sort of thing in application code may be a
larger design issue. Pessimistic locking is bad for both performance and
concurrency. It is best to avoid open transactions when waiting for user
input because users may get interrupted or go to lunch. This can lead to
serious blocking issues.
--
Hope this helps.

Dan Guzman
SQL Server MVP

"David C. Barber" <da***@NOSPAMdbarber.com> wrote in message
news:4e********************@comcast.com...

I'm trying to determine which user has locked a given record from VB6.

I know I can use sp_lock and sp_who, and match up the data to determine
which users have locked records in my database, however I haven't seen a
way
to match the specific user to the specific record. What am I missing
here?

Thanks!

*David*

[Erland Sommarskog]

David C. Barber (da***@NOSPAMdbarber.com) writes:

I'm trying to determine which user has locked a given record from VB6.

I know I can use sp_lock and sp_who, and match up the data to determine
which users have locked records in my database, however I haven't seen a
way to match the specific user to the specific record. What am I
missing here?

To echo what Dan said: you are barking up the wrong tree. The locking
mechanism in SQL Server is there to ensure atomicity of transactions.
If you have business requirements that makes it necessary one user to
reserve a record, and another user to see who has it reserved, you should
roll your own.

To this end, you probably need a few columns to the table with username,
time for the reservation, and possibly some more information. One thing that
you need to consider is that a reservation may become stale. That is, the
user who makes the reservation never completes the operation.

You should in no way rely on SQL Server locking mechanism, and you should
absolutely not keep locks while waiting for user input. This can cause
your application to grind to a standstill.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[dbmonitor]

Another problem that you could face with this approach is that when a
user retrieves data, you are not sure as to if they retrieve it for
update or for display only. Also, some IDEs allow the user to retrieve
multiple records from the database into a local buffer allowing the
user to scroll through them locally rather then keeping cursors open on
the database. The buffers can then be updated with a large transaction
at the end updating all changes for all records at once. Sort of the
same approach users take when updating a spreadsheet or word document.
Open the file, make changes then save the changes.

All this needs to be taken into concideration when deciding on the
locking mechanism used in your applications.

--
David Rowland
MS SQL Server DBMonitor author
http://dbmonitor.tripod.com

[Andy O'Neill]

"David C. Barber" <da***@NOSPAMdbarber.com> wrote in message
news:4e********************@comcast.com...

I'm trying to determine which user has locked a given record from VB6.

I know I can use sp_lock and sp_who, and match up the data to determine
which users have locked records in my database, however I haven't seen a
way
to match the specific user to the specific record. What am I missing
here?

Thanks!

*David*

Further to the good answers you already have.

Should you find that pessimistic locking is a real must for some reason.
There's another approach to consider.
This is called soft locking.
It ain't really a lock in database terms at all.

With this you have one or two extra columns appended to each table.
User (optional ) and datetime.
When the user wants to mark data as locked, you lock, read, update the user
and datetime stamp.
When the changes are applied back or the user wants to free up the record,
the user is blanked and the datetime set to null.

The datetime is there so you can check to see if anyone's gone and left
their data locked for some inordinate amount of time.

This approach can be handy for those sort of apps where someone wants to
copy some data to a laptop, go off on a site visit or somesuch, update the
data on their laptop at clients or in transit and then eventually write it
all back when they're next in the office.

The downsides of softlocking are that you write updates to lock and unlock;
you can end up with everyone leaving locks all over the show if you're not
careful and your users don't match up to expectations.

Often, pessimistic is suffiicient.
--
Regards,
Andy O'Neill