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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy