eth_getTransactionByHash reports own confirmed transaction as not included in the block

[gituser]

Description
When sending many transactions via eth_sendRawTransaction sometimes there is a bug, when certain transaction gets included in the block, but if you query this particular transaction by using eth_getTransactionByHash you will get:

blockHash       : null
blockNumber     : null
transactionIndex: null

If you try to get this transaction from the block by using eth_getBlockbyHash it will be there, also it will report as included in certain block if you use eth_getTransactionReceipt method.

Steps to Reproduce
In order to replicate the behavior, please provide a detailed list of steps:

1. Send many transactions from the same address at the same time by using eth_sendRawTransaction
2. Observe that some of the transactions are not included in the block according to eth_getTransactionByHash RPC call.

Actual behavior
eth_getTransaction reports confirmed transaction that it has:

blockHash       : null
blockNumber     : null
transactionIndex: null

Expected behavior
eth_getTransactionByHash should report correct status of the transaction if it was included in the block.

Desktop (please complete the following information):
Please provide the following information regarding your setup:

• Operating System: Linux
• Version: latest version v1.27.0, also old versions, e.g. v1.25.4 is affected too
• Installation Method: built from source
• Consensus Client: Lighthouse v5.2.1

Additional Context
This issue only happens to your own transactions sent out by using eth_sendRawTransaction.
if I switch my node to another Nethermind mode, this particular transaction has correct blockNumber, blockHash if I query it via eth_getTransactionByHash.

If you restart Nethermind eth_getTransactionByHash reports correct information for that transaction about block and transactionIndex.

Logs
I've checked logs nothing interesting there regarding this particular transaction.

[rjnrohit]

Looking into now!

[LukaszRozmej]

@marcindsobczak So the root cause looks like local transaction isn't being deleted from tx pool after block inclusion