I'm using MySQL + PHP. I have some code that generates payments from an automatic payment table based on when they are due (so you can plan future payments... etc). The automatic script is run after activity on the site and sometimes gets run twice at the same time. To avoid this, we generate a uuid for a payment where there only can be on nth payment for a specific automatic payment. We also use transactions to encapsulate the whole payment generation process.
As part of this, we need the whole transaction to fail if there is a duplicate uuid, but getting an actual database error will show an error to the user. Can I use Insert Ignore in the payment insert SQL? Will the warning kill the transaction? If not, how can I kill the transaction when there is a duplicate uuid?
To clarify: if the INSERT fails, how can I get it to not throw a program-stopping error, but kill/rollback the transaction?
Mind you, the insert will fail on commit, not on initial execution in the php.
Thanks much!