Post reply

How does SSIS lock source tables

  • March 17, 2015 at 9:31 am

    #315839

    Hi All,

    I'm writing a package to move the bulk of a table from one server to another, which amounts to about 25m rows. I'm not so worried about how long it takes as how it locks the source table. I certainly want the source table to be readable by other SPIDs, and I don't want to use nolock on the source table.

    Now I could set up an explicit batching structure to hit the table for, say, 100,000 records, move those, then come back for the next batch, but if SSIS is already smart enough to establish and release granular locks on the source table, I'm sure that's more efficient. Does anyone know if that's the case? Or do I really need to set up some sort of explicit batching?

    Executive Junior Cowboy Developer, Esq.[/url]

  • March 17, 2015 at 3:53 pm

    #1784616

    JeeTee (3/17/2015)

    Hi All,

    I'm writing a package to move the bulk of a table from one server to another, which amounts to about 25m rows. I'm not so worried about how long it takes as how it locks the source table. I certainly want the source table to be readable by other SPIDs, and I don't want to use nolock on the source table.

    Now I could set up an explicit batching structure to hit the table for, say, 100,000 records, move those, then come back for the next batch, but if SSIS is already smart enough to establish and release granular locks on the source table, I'm sure that's more efficient. Does anyone know if that's the case? Or do I really need to set up some sort of explicit batching?

    Quick thought, in simple terms if SSIS is only reading the table, others should be able to read the table at the same time.

    😎

  • March 18, 2015 at 1:58 am

    #1784667

    I would assume that SSIS only takes read locks on the table, but I'm not sure on what level.

    How the database handles those locks is dependant on the kind of isolation level used.

    Ask your DBA to monitor the table, you'll find out soon enough.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply