URGENT >> Locking Problem

Angelo

Hi Master,

Navision always show error locking by another user when doing something.for eg, posting transaction.When I check in windows performance of the server, I see Lock Request/sec for instance Key and Pages always ON TOP. why?Anybody can help?Pls....

garak

Do you have checked which tables are locked (possible with SQL Profiler) and how your C/AL is designed:?:

Regards

Angelo

Hi,

How to check locking table using sql profiler?Until now,user still can not posting anything.weird....never happen before.
pls advice....

Asallai

Did you try to restart the server?
I've found similar thing a few months ago, sometimes a user is stucked in between a transaction.
After that should be check the data correction and structure!

Angelo

this happened after restarting server. Thats why I'm confuse why this happened after restrarting the server. Anybody can tell me how to check locking table using sql profiler?

idiot

Look at the session to see who is being blocked, then check what the blocking user is doing...

garak

to check, which user is blocked you can read the "sys.dm_tran_locks" for example with

select name from sysobjects where id in (select resource_associated_entity_id from sys.dm_tran_locks where request_type = 'LOCK' and request_session_id = HERETHESESSIONID and resource_type = 'OBJECT')

now you see which tables are locked.
In profiler you activate following Events

Stored Procedures
- PRC:Completed
- SP:Completed
- SP:StmtCompleted

TSQL
- SQL:BatchCompleted
- SQL:BatchStarting

Filter:
Session -> the Sessionid of the user to viw only the activity of the blocking user or
Reads -> over 5000 //view all Reads over 5000
or
Duration -> 2000 ms //view all processes over 2seconds

Regards

kriki

garak wrote:

to check, which user is blocked you can read the "sys.dm_tran_locks" for example with

This might be even easier: http://www.mibuso.com/howtoinfo.asp?FileID=19

garak

i know your solution kriki. It's like my old solution in Nav.
But i don't knwo if he has free objects in Nav, so i've posted the SQL solution ;-)

Other way:

Stryk's solution: http://www.mibuso.com/dlinfo.asp?FileID=958

So, many ways, but the same goal
8)

Angelo

Wow, Many thanks for all your support. I will try the tool..... \:D/