blocked transaction in s4hana

[philip0929]

Hi guys,

I just want to ask because we have a job (with child jobs in it) that keeps on failing due to an ABAP dump. Its about block transaction.

Now, we want to know what other program is blocking our job.

Any idea how can we check the program that is causing the block of transaction?

[simon25]

Hi Phillip,

Can you share the ABAP Dump details and error text please ?

Try attach the full dump file itself.

Kind Regards

Simon

[philip0929]

Hi simon25 - here it is

Category External Error

Runtime Errors DBSQL_SQL_INTERNAL_DB_ERROR

Date and Time 04/27/2023 02:41:22

Short Text

SQL error "SQL code: 131" occurred while accessing table "DFKKCOLFILE_H_W".

What happened?

An internal error has occured on the database while preparing or executing an SQL command. You can find more details from the SQL

error.

Database error number: SQL code: 131

Database error text: "SQL message: transaction rolled back by lock wait

timeout: TrexColumnUpdate failed on table 'SAPABAP1:DFKKCOLFILE_H_W' with

error: transaction rolled back by lock wait timeout: Lock-wait time out

exceeded [OWNER=4516289737, TYPE=RECORD_LOCK, CURRENT_MODE="

[philip0929]

st22-dump-1.txt

simon25 - see attached

[simon25]

Hi Philip,

Yeah this a typical lock-wait. Check KBA 3066406 - SQL code: 131" occurred while accessing table

Category External Error

Runtime Errors DBSQL_SQL_INTERNAL_DB_ERROR

Date and Time 04/27/2023 02:41:48

Short Text

SQL error "SQL code: 131" occurred while accessing table "DFKKCOLFILE_H_W".

Database Interface Information

C E execute() of C_0432, #rec=0, rc=1, rcSQL=131 [/bas/753_REL/src/dbs/hdb/dbsdbsql.cpp#3856]

C E EXECUTE C_0432 on connection 0, rc=131 [/bas/753_REL/src/dbs/hdb/dbslsdb.cpp#8615]

C 1 SQLCODE : 131 [/bas/753_REL/src/dbs/hdb/dbsdbtrc.cpp#610]

C 1 SQLERRTEXT : transaction rolled back by lock wait timeout: TrexColumnUpdate failed on table 'S

C 1 _H_W' with error: transaction rolled back by lock wait timeout: Lock-wait time ou

C 1 516289737, TYPE=RECORD_LOCK, CURRENT_MODE=EXCLUSIVE, REQUESTED_MODE=EXCLUSIVE], r

C 1 sc_p=7f651cb89c30,no=432,idc_p=7f651cbb5050,con=0,act=1,slen=53,smax=256,#vars=4,stmt=7f525880

C 1 prepared=0,lastPrepareTime=20230427020443,nativeSQL=0,ignoreStmtId=0 [/bas/753_REL/src/dbs/hdb

C 1 stmtid = <13/DFKKCOLFILE_H_W /32768/20110908141819> [/bas/753_REL/src/

C 1 INSERT INTO "DFKKCOLFILE_H_W" VALUES( ? , ? , ? , ? ); [/bas/753_REL/src/dbs/dbfun/dbsc.c#2428

B E ***LOG BYO=> deadlock aborts transaction [dbsh#1233]

B 1 SQL code: 131, SQL text: transaction rolled back by lock wait timeout: TrexColumnUpdate failed

B E ***LOG BY4=> sql error 131 performing INS on table DFKKCOLFILE_H_W [dbdbslst#5138]

B E ***LOG BY0=> transaction rolled back by lock wait timeout: TrexColumnUpdate failed on table 'S

Check the same in the underlying tenant DB indexserver trace, you should see more information including the BLOCKER connection.

You can use this script 'HANA_Locks_Transactional_LockWaits_2.00.060+.txt' from Note 1969700 (The /* Modification section */ can be used to filter down on historic timestamps.)

This should show the waiting and blocking statement hashes.

[EXAMPLE OUTPUT]

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|WAIT_TIMESTAMP |WAIT_S |SCHEMA|OBJECT_NAME|LT|LM|F|A|WAIT_CONN|WAIT_UTID |BLK_CONN |BLK_UTID |BLK_CLIENT_HOST_PID|BLK_APP_SOURCE|BLK_STATEMENT_HASH|HOST |PORT |RECORD_ID |

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|2016/12/29 14:44:44| 15|SAPERP|S060 |R |E |X| | | 2240287263| 416874| 2240286646|sapabap1 / 11500 |RSM13000:11436| |saphana0|30003|OID=0x000002AF, PARTID=0x0, OFFSET=0xA579D752|

Kind Regards
Simon