What object locked the table?

Astinia

Hello guru,

NAV 2009

I have a problem, in our logs I'm frequently see that something is locking tables.
I want to found out what object does that.

first, I made a simple codeunit which uses :

MyTable.LOCKTABLE;
MyTable.FINDFIRST;
SLEEP(30*1000);
MyTable."Version No." += 1;
MyTable.MODIFY;
SLEEP(30*1000);

I have made a lock - I can't manually change a record in the Table.
I can see that in the Database Sessions I have blocked session.

The second step, I had a look into SQL Server with such a query:
SELECT blocked.wait_resource,
resource_description,
blocked.user_id as [who is waiting],
blocked.wait_type
--blockingSessions.user_id as [who locked]
, blockingSessions.session_id
, blockingSessions.program_name
, blockingSessions.login_name
, blockingSessions.login_time
, blockingSessions.host_process_id
, blockingSessions.host_name
, Processes.kpid as [Windows thread ID]


-- OBJECT_NAME(p.[object_id]) BlockedObject
FROM sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests AS blocked
ON blocking.session_id = blocked.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS waitstats
ON waitstats.session_id = blocked.session_id
INNER JOIN sys.dm_exec_sessions as blockingSessions
ON blockingSessions.session_id = blocked.blocking_session_id
INNER JOIN sys.sysprocesses as Processes
ON Processes.spid = blocked.blocking_session_id
where waitstats.blocking_session_id IS NOT NULL
and blocked.database_id = @DBID

the output:
KEY: 5:72057888025411584 (c77a8afba10f)
keylock hobtid=72057888025411584 dbid=5 id=lock16fb13f00 mode=X associatedObjectId=72057888025411584
1
LCK_M_U
105
Microsoft Dynamics NAV Classic client
MYHOST\myUser
2016-09-09 16:36:55.830
5000
NAVTEST
0

with the select *
from sys.partitions P
where P.hobt_id = 72057888025411584
i found that the object is MyTable.

No information about my codeunit, which causes the lock.

So, I can:
found the locked session in SQL
found the session which is locking current

but i can't found the object in the NAV which related to blokcing session.

Any advice?

Thanks!

Aquir

Hi,

you should use sys.dm_tran_locks link instead because it's an open transaction which is blocked not a task/exec

Cheers!

kriki

I am afraid that is not possible. The only way is to immediately call the person and ask what he is doing. In combination what he is doing and everything that is locked, you might get to the object that is locking it.
In NAV2013+, you don't even have the user name anymore....

Aquir

Yes, kriki has right. You can find the lock itself but not the person who's locking
There is no - visible - connection between the SQL session and the NAV session
(it would be nice to see the SQL Session ID in NAV...)

kylehardin

Aquir speaks truth. We have had a discussion about the lack of connection between the SQL Session and NAV Session, if you'd like to have a read: http://forum.mibuso.com/discussion/66876/is-there-a-way-to-link-nav-2015-session-id-to-sql-spid

Astinia

Thanks a lot!
We have a lot of users working in the same time plus around 5-7 of jobs going in a background.
So, with asking will be some problems...
Thanks for link, read it before.

kylehardin

If you truly are trying to track down a particular object that is locking, but you think you know which user, make a second service tier and move that user (and only that user) to the new NST. Then you can do some SQL profiler magic that might at least narrow down which SQL statements are involved in the locking.

vaprog

You might want to combine your investigations with an SQL Server Trace.

Some information to get you going:
Dynamics NAV Team Blog: Example of How to use SQL Tracing Feature to Profile AL Code

Astinia

kylehardin wrote: »

If you truly are trying to track down a particular object that is locking, but you think you know which user, make a second service tier and move that user (and only that user) to the new NST. Then you can do some SQL profiler magic that might at least narrow down which SQL statements are involved in the locking.

The problem is - I don't know the user and I don't know the process..

Astinia

vaprog wrote: »

You might want to combine your investigations with an SQL Server Trace.

Some information to get you going:
Dynamics NAV Team Blog: Example of How to use SQL Tracing Feature to Profile AL Code

I have 20 users and at least 5 jobs running at one time. It's really hard to make it by hands