Table comparison transform causes table locking

dataservices job design
1

In a data flow i am using a table comparison transform. My data flow
looks something like this

src tbl => qry transform => case => for default case => qry transform
=>table comaprison => dest tbl.

src tbl is the incremental extract of facts.
dest tbl is the fact table.
table comparison is with the fact table using a sorted input and
recordsare loaded into fact table.

When i run the data flow, it runs for a certain n records and then stops.
In sql server enterprise manager when i check, a read process is blocking
the insert/update process. The read process is the process initiated by
the table comparison process itself. So basically the table comparison
processes read is blocking its update.

How to prevent the locking. I have tried two things. I added a map
operation after the table comparison. This did not help. Then i added a
data transfer transform after the query transform and before the table
comparison transform. This caused the sql process to lock at two
stages.So this increased the problem.Each time i have to kill the sql
process at the database level for the process to exit. Else the processes
remain in a deadlocked state and the job does not exit. I also tried to
run the table comparison as a seperate process. This too does not help.

Using Data Services 12.0 and source destination are on sql 2000.

Any help is appreciated.

Anup

anup_kumar_n :us: (BOB member since 2008-04-29)

2

Please search, this has been asked many times before.

The most likely culprits:

  • You don’t have proper indexing on the target table to support the table compare.
  • SQL 2000 locking isn’t as good as SQL 2005. Upgrade if you can.

dnewton :us: (BOB member since 2004-01-30)

3

I searched the forums again. Read many posts the last couple of days. I saw many reasons for this problem and many proposed solutions.

  1. Indexing - no problem with table indexes
  2. use sql transform with NOLOCK - dont want to use SQL transform. We want to retain metadata information
  3. DSCONFIG - ReadUncommited - Not sure how to set this
  4. set transaction isoation level - Did not help. As i use DI 12.1 we have a session parameter option for a datastore. I set this here, but did not help.

I tried row-by-row instead of sorted input. This allowed the job to finish but took a very long time to complete. The only thing that works for me now is autocorrect load. Takes a little bit more time than TC (when TC with sorted input works) but far less than TC with row-by-row select.

The process seems to get stuck in the sort of records required by TC for sorted input, where it is reading as well as writing at the same time. I could not find a permanent resolution anywhere to use TC with sorted input to a sql 2000 target table. Correct me if i am wrong.

anup_kumar_n :us: (BOB member since 2008-04-29)

4

What about setting the transform to cached mode?

Werner Daehn :de: (BOB member since 2004-12-17)