Failure 9128 The transaction exceeded the maximum number of rowhash locks allowed

Database
GRB
N/A

Failure 9128 The transaction exceeded the maximum number of rowhash locks allowed

Hi,

I am getting the error while executing a simple insert select statement. I have a table Product_Change in NewDB database and a table PC in OldDB database.

insert into NewDB.Product_Change
(
change_app_code ,
change_number ,
abstract ,
Author_Person_App_Code ,
Author_Person_Id ,
owner_Person_App_Code ,
owner_Person_ID ,
Created_Datetime ,
Last_Change_Datetime ,
TARGET_CLOSE_DATETIME ,
Close_Reason_Code ,
Class_Code ,
Priority_Code ,
Customer_Impact_Code ,
Found_In_Activity_Code ,
Last_Change_Login_ID ,
primary_product_app_code ,
Primary_Product_ID ,
Primary_Component_ID ,
Change_Aspect_Code ,
Defect_Flag ,
SLA_Code ,
Became_Owner_Datetime ,
Found_Internally_Flag ,
Documentation_Change_Flag ,
Software_Change_Flag ,
Hardware_Change_Flag ,
Tech_Alert_Required_Flag ,
Special_Access_Flag ,
Workaround_Available_Flag ,
Impact_Level_Code ,
Old_Repl_By_Change_App_Code ,
Old_Repl_By_Change_Number ,
Old_Regr_Change_App_Code ,
Old_Regr_Change_Number ,
Root_Cause_Code ,
DR_Status

)
select
change_app_code ,
change_number ,
abstract ,
Author_Person_App_Code ,
Author_Person_Id ,
owner_Person_App_Code ,
owner_Person_ID ,
Created_Datetime ,
Last_Change_Datetime ,
TARGET_CLOSE_DATETIME ,
Close_Reason_Code ,
Class_Code ,
Priority_Code ,
Customer_Impact_Code ,
Found_In_Activity_Code ,
Last_Change_Login_ID ,
primary_product_app_code ,
Primary_Product_ID ,
Primary_Component_ID ,
Change_Aspect_Code ,
Defect_Flag ,
SLA_Code ,
Became_Owner_Datetime ,
Found_Internally_Flag ,
Documentation_Change_Flag ,
Software_Change_Flag ,
Hardware_Change_Flag ,
Tech_Alert_Required_Flag ,
Special_Access_Flag ,
Workaround_Available_Flag ,
Impact_Level_Code ,
Old_Repl_By_Change_App_Code ,
Old_Repl_By_Change_Number ,
Old_Regr_Change_App_Code ,
Old_Regr_Change_Number ,
Root_Cause_Code ,
DR_Status

from OldDB.PC where (change_app_code,change_number) not in (select change_app_code,change_number from NewDB.product_change);
*** Failure 9128 The transaction exceeded the maximum number of rowhash locks allowed.
Statement# 1, Info =0
*** Total elapsed time was 9 minutes and 39 seconds.

What would be wrong with this query ?

How do I move further ?

Thanks !!

Tags (1)
4 REPLIES
Enthusiast

Re: Failure 9128 The transaction exceeded the maximum number of rowhash locks allowed

Hi,

I also came across the same error today. Could find the details about this error in TD Error Messages pdf (1096) for v13.10. Pasting it here.

9128 The transaction exceeded the maximum

number of rowhash locks allowed.

Explanation: This error is returned when a transaction

has exceeded the number of rowhash locks

allowed. The threshold on the number of rowhash locks

allowed is defined in DBSControl via the General field

’MaxRowHashBlocksPercent’. This is done so that one

transaction does not fill up the lock table with rowhash

locks. The number of control blocks/locks that can fit

into an AMP lock table is defined by STDMAXLOCKBLOCKS

which is currently ~51860.

Generated By: LOK subsystem.

For Whom: End user.

Remedy: Reduce the number of rowhash locks that

the transaction is acquiring. An alternative is increase

the General field ’MaxRowHashBlocksPercent’ value in

DBSControl.

On the remedy part, I was able to understand increasing ’MaxRowHashBlocksPercent’ value in DBSControl but didn't understand how we can reduce the number of rowhash locks.

Please help.

Thanks in Advance

Nirmal

Re: Failure 9128 The transaction exceeded the maximum number of rowhash locks allowed

I am having the same issue. Using .NET to insert one million rows into teradata - errors out as follows:

Teradata.Client.Provider.TdException was caught

  HResult=-2147467259

  Message=[Teradata Database] [9128] The transaction exceeded the maximum number of rowhash locks allowed.

  Source=Teradata Database

  ErrorCode=-2147467259

  TxRolledBack=true

  StackTrace:

       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)

       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)

       at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)

       at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)

       at RMLogixConnector.db.teradata.TeradataFastloadAdapter.insert(String tableName, DataTable loadData) in ...

Senior Apprentice

Re: Failure 9128 The transaction exceeded the maximum number of rowhash locks allowed

Every single row Insert needs a RowHash-lock.

If there's one million rows inserted within a single transaction this will result in one million RowHash-locks.

So either don't do it within a single transaction or simply submit a LOCK TABLE tab FOR WRITE at the begin of the transaction.

Teradata Employee

Re: Failure 9128 The transaction exceeded the maximum number of rowhash locks allowed

Hi Dieter,

Write lock on table does not allow Hash Lock ? By default insert table has write lock and select table read lock.

please elaborate how placing write lock on source table help in this issue.Please correct me if i am wrong.