MySQL Transaction handling with Insert Ignore (or similar) and Unique IDs

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!

---

Maybe there is another approach - making sure the same UUID isn't read twice, instead of relying on a query failure.

I am guessing you get the UUID from another table before you process it and insert. You can use a transaction and then SELECT ... FOR UPDATE when you read the records. This way the records you read are locked. When you get all the data update a status column to "processed" and COMMIT the transaction. Finally, make sure the process doesn't read records with status "processed".

Hope this helps...

---

Just stumbled upon this. And it is an old question but I would try to give an answer.

First: Make sure your payment job only runs one at a time. Guess that could save you a lot of trouble.

Second: If your update statement fails in php, put it in a try-catch Block. That way your mysql query fails but will the error will not be shown to the user and you can handle it.

try {
   ... your mysql code here ....
} catch (Exception $e) {
   ... do whatever needs to be done in case of problem ...
}

Keep in mind that there are a lot of possible source for an exception. Never take a failing insert as the reason for granted.