October 26, 2016 at 7:07 pm
Hi,
I am experiencing an lock issue.
The head blocker query is the following:
INSERT INTO DIMENSIONHIERARCHYLEVEL (DIMENSIONHIERARCHY,DIMENSIONATTRIBUTE,LEVEL_,TEMPORARYDIMENSIONHIERARCHYLEVEL,RECVERSION,PARTITION,RECID) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7)
Pretty simple huh ?
In my troubleshooting, I ran SQL Profile and executed the function in the system. I can see the following eventclass
SP:StmtCompleted
RPC:Completed
My understanding is that StmtCompleted indicates that a Transact-SQL statement has completed. However, I can not see the record in my table. Not seing the record makes sense because the query seems to be stuck (i don't know why), but I dont understand why i would have StmtCompleted for that query.
What am I missing ?
Thank you
October 26, 2016 at 8:50 pm
October 27, 2016 at 9:50 am
Thanks for your answer,
I am not a developer so I have not checked the code yet.
Do you mean if the query is being part of a transaction (ttsbegin -ttscommit) ?
Are you thinking that the faulty transaction is not the one I see in SQL ?
Thank you
October 27, 2016 at 11:00 am
from the database side you can see if there's still a lock on the object:
SELECT s.host_name, s.login_name, s.status,
DB_NAME(l.resource_database_id) AS database_name,
OBJECT_SCHEMA_NAME(l.resource_associated_entity_id, l.resource_database_id) AS schema_name,
OBJECT_NAME(l.resource_associated_entity_id, l.resource_database_id) AS object_name,
l.request_mode, l.request_type, l.request_status, l.request_session_id, l.request_owner_type
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
WHERE l.resource_type = 'OBJECT'
October 27, 2016 at 12:44 pm
Yes, I can see the lock.
ObjectName : DIMENSIONHIERARCHY
Request_mode: IS
Request_owner_type: Transaction
Session ID :71
ObjectName : DIMENSIONHIERARCHY
Request_mode: IX
Request_owner_type: Transaction
Session ID : 66
ObjectName : DIMENSIONHIERARCHYLEVEL
Request_mode: IX
Request_owner_type: Transaction
Session ID : 66
Head Blocker = Session 66
Session 71 is blocked by 66
DBCC INPUTBUFFER (66)
INSERT INTO DIMENSIONHIERARCHYLEVEL (DIMENSIONHIERARCHY,DIMENSIONATTRIBUTE,LEVEL_,TEMPORARYDIMENSIONHIERARCHYLEVEL,RECVERSION,PARTITION,RECID) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7)
I dont understand why the INSERT does not go through ??
October 27, 2016 at 12:52 pm
The status of session 66 is SLEEPING and the cmd is AWAITING COMMAND.
I think is is an issue in the code, I think SQL is waiting for the application to do something, a commit for example.
November 16, 2016 at 5:01 pm
Hey guys
I found the solution. I had to enable Snapshot Isolation on my database
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
Thank you for all the answers
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply