How to get unique ID for the concurrent jobs

General
Enthusiast

How to get unique ID for the concurrent jobs

Hi:

One of our prod table is facing duplicate id issue. Please give me suggestions on how can we avoid the duplicate id generation.

Here is the senario:

we have 4 differrent SPs and they are on active for 24/7.

Each proc loads the data to same target table from 4 diff stage tables whenever the data is available.

Below are 4 queries which run by SP's.

Somepoint 2 or 3 of the these jobs are running at the same time with millisec diff  and getting the same ID from db.tab1 table and loading into it. This is causing the duplicate id for 2 diff records.

Is there anyway we can avoid this.

We can not apply teh IDENTITY design

We can not change the table definition(make it as UPI)

We can not control the schedule of the jobs

  INSERT INTO DB.tab1

      ( ID

      )

     SELECT

           COALESCE(b.ID,SUM(1) OVER (ROWS UNBOUNDED PRECEDING) + CAST(COALESCE((SELECT MAX(a.ID) FROM DB.tab1 a WHERE JOB_ID < 100000000   ),0)  AS INTEGER))

    FROM DB.tab2 b

    LEFT OUTER JOIN DB.tab1 a

    ON COALESCE(b.ID,-9999) = a.ID

    WHERE a.TEST_ID IS NULL

    AND  b.FILEDATAIND = 'N'

    ;

  INSERT INTO DB.tab1

      ( ID

      )

     SELECT

           COALESCE(b.ID,SUM(1) OVER (ROWS UNBOUNDED PRECEDING) + CAST(COALESCE((SELECT MAX(a.ID) FROM DB.tab1 a WHERE JOB_ID < 100000000   ),0)  AS INTEGER))

    FROM DB.tab3 b

    LEFT OUTER JOIN DB.tab1 a

    ON COALESCE(b.ID,-9999) = a.ID

    WHERE a.TEST_ID IS NULL

    AND  b.FILEDATAIND = 'N'

    ;

  INSERT INTO DB.tab1

      ( ID

      )

     SELECT

           COALESCE(b.ID,SUM(1) OVER (ROWS UNBOUNDED PRECEDING) + CAST(COALESCE((SELECT MAX(a.ID) FROM DB.tab1 a WHERE JOB_ID < 100000000   ),0)  AS INTEGER))

    FROM DB.tab4 b

    LEFT OUTER JOIN DB.tab1 a

    ON COALESCE(b.ID,-9999) = a.ID

    WHERE a.TEST_ID IS NULL

    AND  b.FILEDATAIND = 'N'

    ;

  INSERT INTO DB.tab1

      ( ID

      )

     SELECT

           COALESCE(b.ID,SUM(1) OVER (ROWS UNBOUNDED PRECEDING) + CAST(COALESCE((SELECT MAX(a.ID) FROM DB.tab1 a WHERE JOB_ID < 100000000   ),0)  AS INTEGER))

    FROM DB.tab5 b

    LEFT OUTER JOIN DB.tab1 a

    ON COALESCE(b.ID,-9999) = a.ID

    WHERE a.TEST_ID IS NULL

    AND  b.FILEDATAIND = 'N'

    ;

any suggestion would be greatly appreciated.

8 REPLIES
Junior Contributor

Re: How to get unique ID for the concurrent jobs

Can you show an Explain?

If you Insert into the same table you Select from there should be a WRITE lock, which prevents other sessions from reading.

Btw, why do you use a Left Join on ID?

Enthusiast

Re: How to get unique ID for the concurrent jobs

Hi Dieter:

Thank you for taking time to reply on this.

Actually, here DB.tab1 is not a base table, it is one to one base view with row level access lock. Due to this optimizer not placing any write lock on the table.

These jobs are bulit from wherescape tool, where they are using the views to process the loads.

Since all these 4 jobs run for 24/7 and load the data whenever incoming source files are in place.

We see  lot of blockings if we use the base table directly for read and write to load..rt.

So looking for the solution with row level access only design

I do know the application requirement why it is using LJ on ID, I will chk with them and let ypu know.

Thanks,

-Vani

Enthusiast

Re: How to get unique ID for the concurrent jobs

Concurrent jobs are running and able to read the table at the same time as the jobs are using the view with row access lock.

So we have issue with read operation.

Enthusiast

Re: How to get unique ID for the concurrent jobs

I am seeing very strange issue in the Explain Plan of teh query. It seems we have stats optimizer issue on the system.

I do not see any write lock on DB.tab1 in EP, but when i drop the stats for all LOJ tables(DB.tab2,DB.tab3...ect) ,EP shows the write lock for DB.tab1 table.

Any advise

Enthusiast

Re: How to get unique ID for the concurrent jobs

Vani,

Can you post the explain plans?  At first blush, dropping the stats should exhibit more expensive queries, longer duration and locks as TD tries to complete your join requests.  The optimizer is cost based and stats are the price tags for your costs, so your forcing the optimizer to call out "Price check on aisle X"  for every join.

Cheers

Teradata Employee

Re: How to get unique ID for the concurrent jobs

Yes, it would be interesting to compare the EXPLAINs. It seems that in one case the optimizer is certain that at most one row will be inserted and opts to use a RowHash Write lock (which is not shown in the explain text) for the INSERT; without the stats it generates a different plan and takes the more conservative approach of applying Table Write lock. 

Add an explicit LOCK TABLE <base table for DB.tab1> WRITE modifier to the request to avoid concurrent updates.

If you acquired Table Read lock instead then you would get consistent results, but may have occasional deadlocks where two sessions each already hold Table Read (acquired at start of transaction) and want to acquire RowHash Write at the time of the actual INSERT merge.

Enthusiast

Re: How to get unique ID for the concurrent jobs

Thank you all for the suggestions . I was on vacation and could not responde to the updates. 

When we verified this with TD GSC they said the query id doing a group-AMP MERGE on target table.

if a query needs to read and update on the same table concurrently, the Optimizer chooses a group-AMP MERGE step to improve the performance.

A group-AMP MERGE step places locks on group-AMPs, not all-AMPs.

So, if running the same query at the same time, duplicate ID creation could happen.

The current locking behavior for the query is working as designed.

So, to avoid the issue,  they gave 2 suggestions  1)add “lock <target table> for write” in the query to place the table level lock on the target table expicitly.

2)use diagnostic groupamp not on for session; on top query to avoid the gorup-amp merge step

Now we are fine.

Teradata Employee

Re: How to get unique ID for the concurrent jobs

Thank you Vani for posting your finding as well. This helped me in a similar situation.