How can I tell what Operator has a lock on a particular record that another Operator is trying to update? Using SQL Server 2012 and VBA ADOBE access method in MS Access.
This Forum is for SQL Server, Try: http://database.ittoolbox.com/groups/technical-functional/access-l/
Why is this question not appropriate here? The record is locked by SQL Server. Why can't I find out from SQL Server who has it locked?
Thanks, Eddie
My best guess would be that the "lock" has nothing to do with SQL server but is a "soft lock" at the APP end- i.e. Access itself. Might not be that way though, but I reckon that the locking that exists in SQL Server is unlikely to be the way that something like Access would lock a record. Holding locks long term (i.e. more than a few milliseconds) is not a good idea in SQL, so assuming you are talking about a lock that holds a record whilst, for example, an Operator edits it, and then Saves it, it would be very unusual to do that using SQL Server locks. The whole system would just grind to a halt.
Kristen, Thanks for your comments. Here's why I think it's a SQL Server lock... This problem has been an intermittent problem for over a year. During an Access update process, the application throws a Timeout Runtime Error on the same record after multiple attempts. (Not the same record every time it locks up, just the same record for a given update run.) Over night, apparently, the record lock on SQL Server times out or is somehow released for some other reason and, the next day, the process always runs successfully. Now, here's the kicker... I had the occasion to run a similar query that updated about 20,000 records directly in SQL Server using SQL Server Management Studio. This should have taken less than a minute. After about 30 minutes without finishing, I cancelled the query. Then I changed the query to update only the first 10,000 records... worked fine, very quickly. Then the next 5,000 successfully and so forth. When I got down to the last 100 records, it locked up. After waiting overnight (this was on the weekend so no operators were in the system at all), the update query worked successfully. What I'm trying to do here is, when a locked record causes the update to fail, find out who/what locked the record (I will know the unique Record ID when this happens) so I can determine who/what caused the locking so I can keep this from happening in the future. Does this help? Thanks again, Eddie
Has anyone come up with some more ideas on this issue? Is there somewhere else I could look to find the solution to this continuing problem? Perhaps an explanation on the locked tables in SQL Server???
The system stored procedure sp_who2 will show the spid of blocking processes.
Thanks. I'll check it out and let you all know what I find out.
By the way, I'm looking for a way to create/use a function I can call with a table name and unique RecordID (Identity field) and it will return the Operator who has that record locked. If you already have something like this, please let me know. Thanks again.
As I described before, SQL doesn't really work that way. There isn't the concept of locking a record and a particular UserId "owning" the lock on a RecordID, which is why I am doubting that is how your APP is doing it (given your description that it appears that a lock is getting "orphaned" in the ON setting, and that is preventing someone else from accessing that record).
You CAN make SQL work in a way similar to that, so its not impossible, but if your APP was implemented that way (and assuming you have more than a couple of concurrent users [who would probably not stumble over each others locked records anyway ...]) then a straight SQL record lock held DURING an edit of a record would (i reckon 
) manifest itself as people getting blocked fairly frequently.
But "its possible" that the APP is implemented that way.
In addition to sp_who2 I recommend looking at Adam Machanic's sp_whoisactive - its a bit of a Swiss Army Knife, so more options than you need and that may be a bit daunting, but when I have Person-A blocking Person-B I find its the best tool to see the actual blocking that is taking place.
If nothing else, if you use that next time someone is blocked and you do NOT see any Blocking reported than I think would 100% confirm that the Locking you are seeing is implemented in some other way (e.g. "soft" within the APP itself, or a DB table with UserID/RecordID - in which case you could easily implement a "Who has locked what" query / report on that table)
Here's the script I use to see Blocking
EXEC sp_WhoIsActive
@output_column_list = '[start_time][dd%][session_id][block%][wait_info][status][open_tran_count][login_name][tasks][tran_log%][cpu%][temp%][reads%][writes%][context%][physical%][query_plan][locks][sql_command][sql_text][%]'
, @sort_order = '[session_id] ASC,[start_time] ASC'
The column you need to review is the [blocking_session_id] if that is NULL on ALL rows then there is no SQL blocking going on. Keep re-running the command, whilst you are experiencing the problem, to see if it the [blocking_session_id] clears or, conversely, if more users get blocked.
The [blocking_session_id] is the ID of the session which is blocking that user (as indicated by the [session_id] column). You will find the [blocking_session_id] appears in the [session_id] column somewhere else in the list, and from that you can work out who that "owner" is, along with the person(s) who are blocked. There is a login_name column which may tell you the answer (but the APP may login with a single UserID rather than different SQL UserIDs for each person), and there is also a sql_text column which is XML - if you Copy & Paste that to, say, Notepad you may well be able to figure out from that who the user is - e.g. with luck you will be able to see a RecordID in the WHERE clause, or something helpful like that.