SQL Server Performance

Locking in DTS Jobs

Discussion in 'SQL Server DTS-Related Questions' started by nilayinc, Jul 2, 2003.

  1. nilayinc New Member

    What kind of locking is imposed when a DTS job is run ?
    Can Record level locking be enabled ?
  2. bambola New Member

    I tried to run a DTS package specifying a query and using a rowlock an it worked.
    Take into consideration that row level lock takes more recources and at a certain point will escalate to table lock. Consider also using page lock.

    Bambola.
  3. satya Moderator

    ... and also depends on the data import/export size.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  4. nilayinc New Member

    My question was, if I dont specify any Lock option in query in DTS, Is by default the row is locked?? In fact my requierment doesnt require locking as I am just reading the information from the source table(s) and inserting/updating in destination table(s). And while I am inserting/updating in destination table(s), nobody is working on destination table. So actually my requirement doesnt require locking of table but does by default Data Transformation Services (DTS) in SQL Server locks the table that it operates on?
  5. satya Moderator

    A long-running query can block other queries. And its again depends on the source table acitivty, if the table is huge and simultaneous update operations and select records might trigger blocking.

    In any other case locking is not by default on using DTS for SELECT based queries.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page