automatically mark mempool transactions with conflicting nonces as failed

[rafaelcr]

The API returns a bunch of mempool transactions that are impossible to mine given that they conflict with other confirmed transactions from the same senders with the same nonce, so they end up being stuck for days (until they get pruned after 256 blocks). For example, this transaction with nonce 4461 is still pending:

However, that sender STX address already has a confirmed transaction with the same nonce (in this case a coinbase, but the same happens with any tx type):

These stuck transactions can cause user confusion but they most definitely affect the fee estimation algorithms. (I'm not sure why the Stacks node does not drop them automatically.)

• We should add logic to the API that upon a tx confirmation, it checks the mempool table for txs from that sender with that nonce and marks them as failed or pruned.

[smcclellan]

@zone117x for feedback.

[zone117x]

We should add logic to the API that upon a tx confirmation, it checks the mempool table for txs from that sender with that nonce and marks them as failed or pruned.

This makes sense to me. The only thing to be careful about is "restoration" / un-pruning txs during re-orgs when necessary, otherwise it can give the appearance of txs "disappearing", when they are not included in a canonical block and are also pruned from the mempool.