All AMP active for Updates based on primary index

Database
Enthusiast

Re: All AMP active for Updates based on primary index

Hi Todd,

 

There was no locking specified at the beginning of the explain.  Just the following:

 

1) First, we execute the following steps in parallel.

 

This is followed by a number of "We do a single-AMP UPDATE from" statements.  I'm guessing we need to break these out and process them one by one in order to get the rowhash lock... would you agree?  I figured it was the inlist, but the single-AMP UPDATE statements threw me off a bit! :)  We have alot of processes that are updating based on NUSI values as well which is giving us some fits with the ensuing table lock.  I think doing something like a cursor is the best solution to assure a rowhash lock.

 

Thanks... always a pleasure hearing from you!

 

Joe

Enthusiast

Re: All AMP active for Updates based on primary index

Here is the actual explain plan:

 

explain UPDATE pingusb.bs_WGPrintQueue SET Status = 'Pending'
--WHERE WgProcessId = '201708280005'
--where BatchNumber = 938909
where WorkorderNumber IN ( 3653925 , 365 , 3518981 , 3655198 , 3655198 , 3654818 , 3654173 , 3654831 );

 

1) First, we execute the following steps in parallel.
1) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3654173" with no residual conditions.
2) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3654831" with no residual conditions.
3) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3654818" with no residual conditions.
4) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3655198" with no residual conditions.
5) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3518981" with no residual conditions.
6) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 365" with no residual conditions.
7) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3653925" with no residual conditions.
2) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.

Enthusiast

Re: All AMP active for Updates based on primary index

Sorry for the fragmented responses.... This is what viewpoint listed:

 

 
Estimated Time:
0:00:00.000
Estimated Rows:
0
Actual Time:
0:00:00.000
Actual Rows:
1
First, lock PINGUSB."pseudo table" for write on a row hash.
2
No Confidence
Blocked Blocked
Estimated Time:
0:00:00.000
Estimated Rows:
0
Actual Time:
Actual Rows:
Next, we lock PINGUSB.BS_WGPRINTQUEUE for write.
Teradata Employee

Re: All AMP active for Updates based on primary index

Hmmm...

Any chance we could see the query and explain together?

 

An update based on a NUSI has to be an all-AMP operation and all-amp table lock because we have no idea which amp(s) will have a qualifying row in the NUSI and we have to scan the NUSI to find the qualifying rows.

 

Depending on data distribution,... it may be appropriate to consider a hash index or join index with a PI of the NUSI fields to create an alternate single AMP (two AMP with the update) direct access path.

Enthusiast

Re: All AMP active for Updates based on primary index

Sure... here is the SQL and Explain:  (NUPI on bs_WGPrintQueue is WorkOrderNumber)

 

explain UPDATE pingusb.bs_WGPrintQueue SET Status = 'Pending'
--WHERE WgProcessId = '201708280005'
--where BatchNumber = 938909
where WorkorderNumber IN ( 3653925 , 365 , 3518981 , 3655198 , 3655198 , 3654818 , 3654173 , 3654831 );

1) First, we execute the following steps in parallel.
1) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3654173" with no residual conditions.
2) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3654831" with no residual conditions.
3) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3654818" with no residual conditions.
4) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3655198" with no residual conditions.
5) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3518981" with no residual conditions.
6) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 365" with no residual conditions.
7) We do a single-AMP UPDATE from pingusb.bs_WGPrintQueue by way
of the primary index "pingusb.bs_WGPrintQueue.WorkOrderNumber
= 3653925" with no residual conditions.
2) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.

 

As far as the NUSIs are concerned I was right with you.  I created a STJI with an alternate PI.  When updating using the alternate PI, the join index table was updated with rowhash but the base table was still updated with a table lock.  All stats good and current on both.

Teradata Employee

Re: All AMP active for Updates based on primary index

was the explain run from the same user with the same session attributes on the same systemand from the same interface (eg TDStudio) as the execution that was shown from viewpoint? The SQL is exactly the same when it is run?

What DBMS release just for my info?

 

was ROWID included in the STJI?

Enthusiast

Re: All AMP active for Updates based on primary index

No...I pulled the sql out of viewpoint and ran it myself.  The original SQL was paramaterized... I plugged in values of my own.  I'm pretty sure the original SQL was run as a batch process connecting with .net.  We are currently running on 15.00.

 

When I created the STJI I did not join back with a ROWID... do you think I could get a rowhash if I did?

Enthusiast

Re: All AMP active for Updates based on primary index

Forgot to mention that the queries were both run on the same system.

Junior Contributor

Re: All AMP active for Updates based on primary index

The original SQL was paramaterized

How was this IN-list parameterized, can you show the SP code?

Enthusiast

Re: All AMP active for Updates based on primary index

I posted this earlier but not seeing it now.  Apologies if it gets posted twice.

 

Development gave me the following response:

 

Here you go Joe, this is what we stash in our logs before calling Teradata.  This is 2 lines from the log.  First line is the SQL and the second line are the parameters that we pass.

 

719440:8/29/2017 8:50:40 AM -07:00 [33] Verbose  0: 2017/08/29 08:50:40:SSS [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [WorkorderService.DataService.SqlMaps.WorkorderCreation.UpdateWGPrintQueueToPending] PreparedStatement : [Update WGPrintQueue        Set Status = 'Pending'        Where        WgProcessId =  ?         AND BatchNumber =  ?             And             WorkorderNumber in             (                ?             )]

 

719441:8/29/2017 8:50:40 AM -07:00 [33] Verbose  0: 2017/08/29 08:50:40:SSS [DEBUG] IBatisNet.DataMapper.Commands.DefaultPreparedCommand - Statement Id: [WorkorderService.DataService.SqlMaps.WorkorderCreation.UpdateWGPrintQueueToPending] Parameters: [param0=[WGProcessId,201708290022], param1=[BatchNumber,940315], param2=[WorkorderNumbers[0],3658382]]

 

This is an example of passing only one PI value.  The example I used passed 8 values.  If I understood Todd correctly, Teradata cannot grant a rowhash lock when updating rows based on the PI values supplied in an inlist.  Am I understanding that correctly?  If so, there is a problem with the explain plan.  We try to get the developers to run an explain before any update statement to make sure they are getting a rowhash lock.  In this case, the explain shows eight seperate updates each with a rowhash lock applied.  The developer sees this and thinks all is good.  When the query actually runs, viewpoint shows it as a table lock causing many blocking issues.  I think you can see our issue.  If an inlist of PI values will always cause a table lock, that is good information to know.  My question is... will an inlist of PI values supplied to an update statement always cause a table lock.  If so, is the explain plan displayed in this thread wrong to display seperate rowhash locks for each update?  If that is the case, would the only solution be to update each row with one PI at a time similar to cursor processing?

 

Thanks,

 

Joe