Loading to the same target table simultaneously

Database
Enthusiast

Loading to the same target table simultaneously

Hi All,

I am new to Teradata. I am facing an issue while trying to load to the same TD target table from multiple staging tables simultaneously. I searched the net but got confusing inputs. Please help me clarify what my options are regarding the following. We are using TD 15.

I want to load the same target table by reading from multiple staging tables simultaneously. For e.g. one job will read from source table StoreA and load (update/insert) to target table T. Simultaneously there will be another job that will read from staging table StoreB and load (insert/update) to the same target table T at the same time. I want both the jobs to run simultaneously and update/insert the target T without locking. There is no DELETE required. Here is what I think my options are :

  1. Use TD T-Pump utility to load to the same target simultaneously. This will allow loads to the same table parallelly with Row-Hash level locking by different jobs . However I am unsure as to if we can use T-Pump to read from a TD Staging table instead of an input file. Also I am uncertain about the performance considerations.
  2. Use INSERT INTO /SELECT FROM .... to load (new records) simultaneously into the same table by both the jobs. However, will this lock the table and prevent the other job from writing simultaneously ?
  3. Use UPDATE to modify the target table (for changes records) simultaneously by both the jobs ? However, will this lock the table and prevent the other job from writing simultaneously ?
  4. Use ANSI MERGE to insert/update the same table simultaneously by multiple jobs running parallel. Would this option accomplish my requirement of updating/inserting the same table simultaneously by different jobs running parallelly ?

Note: As far as data is concerned each of the Staging tables will have data from different stores so there is no possibility that a job will attempt to overwrite another job's data while loading to the same target table simultaneously.

Please advise. Kindly let me know if additional information is required.

Thanks.

5 REPLIES
Senior Apprentice

Re: Loading to the same target table simultaneously

#1: The data is already in the system, so why trying to use TPump?

#2/#4: INSERT/SELECT and MERGE always applies a table lock, so the jobs will insert sequentially

#3: Unless it's a PI-based UPDATE it will apply a table-level lock, too

Why do you think that running those inserts sequentially will be much slower?

You should partition the table to avoid full-table-scans for every insert (a future release (15.10?) will finally implement partition level locking.)

Enthusiast

Re: Loading to the same target table simultaneously

Thanks for you reply dnoeth. So isnt there any way by which a table could be updated/inserted concurrently by multiple sessions in TD15 ?

So if I understand correctly for TD15, concurrent updates (to the same table) will be possible if its PI based

and

concurrent inserts will be possible if the table is partitioned ?

Please clarify.

Enthusiast

Re: Loading to the same target table simultaneously

You could build a union view on the source tables and insert into the target table from the union view.  As Dieter pointed out, you shouldn't need any utility to load the data once it's in the system.

TD15 or otherwise, updates have to respect any object locks in place at their level, table, row or access.  The PI based insert will make the lock duration shorter, but won't eliminate the lock.  The PI based update eliminates the need to re-hash the incoming data for distribution in the target table.

Enthusiast

Re: Loading to the same target table simultaneously

So are we saying that if its a PI "value" based update then it will be a row-level lock. Otherwise if the update is performed on the target table even by joining to a Staging table by applying a Join condition on the PI columns it will result in a table-lock ?

So to put it clearly,

Assume tables T1 and T2 both have a PI of column C1.



The following request will use a row-hash lock since its a "value" based update on the PI:

UPDATE T2

SET T2.C2 = new-value

WHERE T2.C1 = a-value;



The following request will NOT use a row-hash lock (it will use a table-level lock):

UPDATE T2

  SET T2.C2 = T1.C2

WHERE T2.C1 = T1.C1;

The second update even though it uses a join to T1 on PI column C1 will still result in a table-lock ?

Re: Loading to the same target table simultaneously

On a similar note, Having multiple insert queries fired to insert into the same table, will it result in dead lock by any means?