If there's a re-org and the transaction goes back into the mem-pool, would it be confirmed automatically later?
Almost certainly. That's WHY it went back in the mem-pool instead of being deleted. However, in MOST cases, it will already be confirmed in the re-org.
so I can conclude that if I pay a solid fee amount and wallet is not faulty, the odds of the transaction being confirmed are > 99.99%?
and the say there's a re-org, which is also rare (1/2000 blocks? less?), then still, it will almost certainly be confirmed eventually
which would make the total odds extremely low.
If you are spending outputs that are all confirmed, are using the default settings of major wallet software, and include a fee that is consistent with the market rate for transactions to be included in blocks, for all intents and purposes, your transaction will eventually get confirmed. There is the chance that, after you broadcast your transaction, tx fees suddenly increase substantially and do not come back down, your transaction may not confirm.
If you are using the default behavior of a major wallet software, your biggest concern for your transaction confirming is the transaction fee rate. There are many websites that can estimate the transaction fee rate you should include in order for a transaction to confirm within a certain number of blocks, and they tend to over estimate the necessary transaction fee rate.
Three weeks, because I INTENTIONALLY paid an insufficient transaction fee. I was not in a hurry, and just kept re-broadcasting the transaction until transaction fee requirements dropped.
which wallets can make you do that?
This is custom behavior that some people will employ in order to save on transaction fee costs. Most major wallet software will
allow you to do this, but will not do so by default. [/list][/list]