Locking in DTS Jobs | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Locking in DTS Jobs

What kind of locking is imposed when a DTS job is run ?
Can Record level locking be enabled ?
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.
… and also depends on the data import/export size. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
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?
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
]]>