The convention on these lists is to inline or bottom post (and to trim the reply to just the pertinent parts).
On Wed, Feb 9, 2022 at 12:33 PM Brent Wood <Brent.Wood@xxxxxxxxxx> wrote:
Because both statements are in the transaction, the delete is not fully actioned until the commit. So it still exists in the table when you try to insert the record with the duplicate key.
A transaction makes your actions invisible (more or less) to other concurrent sessions in the system. But so far as your own session is concerned subsequent commands get to see the changes made during previous commands.
Check if the error is generated during the transaction or at the commit stage, run it without the commit, rollback instead to check this.
You cannot defer uniqueness checks to transaction commit so either it is going to fail on the insert or it will not fail at all.
I don't see how you can do this within a transaction, someone else might?
That should be a good indicator that you are missing something, because the presence or absence of a transaction should not be impacting this at all. This is much more likely operator error rather than a system bug, and so reproducing the error is the only reasonable first step - since the pseudo-code that was provided and the error simply do not make sense.
David J.
