May 3, 2016 at 12:30 am
Hi All,
I am using a data load via SSIS, and was expecting row locks on the destination table.
However when I see sys.dm_tran_locks I see resource_type as File locked in X mode, however the resource_description and resource_associated_entity_id are all 0.
[Attached is the screenshot.]
My question is why this File lock appeared here ?
May 4, 2016 at 8:33 am
er.mayankshukla (5/3/2016)
Hi All,I am using a data load via SSIS, and was expecting row locks on the destination table.
However when I see sys.dm_tran_locks I see resource_type as File locked in X mode, however the resource_description and resource_associated_entity_id are all 0.
[Attached is the screenshot.]
My question is why this File lock appeared here ?
I think table locking is the default behavior of SSIS.
If you want row locks you have to change destination property.
I know OLE DB has row locking.
File lock can appear if your DB has 1 table in the .mdf file.
May 5, 2016 at 12:55 am
I think table locking is the default behavior of SSIS.
File lock can appear if your DB has 1 table in the .mdf file.
Table locking is default behavior when I use the fast load option in the destination, but I didn't use that.
The insert operations in SQL always acquire row level locks and are not escalated.
Also this is an AdventureWorks 2012 DW database having many tables.
Confused why this lock appeared ?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply