处理现实世界中的交易

Question

I'm rewriting this question because it got no responses.

I'm trying to figure out the correct way to work with db transactions. Everything I see about how to do transactions is very basic, along the lines of:

1. query "begin" to start transaction
2. run your queries.
3. if everything ran fine, commit transaction.

I get that, but it's error (deadlock) handling I don't get. I've heard of two options:

1. Show the user an error and say "try again"
2. Try again on the spot until it succeeds.

To me, telling the user to try again because of a technical issue like this seems bad - do real applications do this regularly? Is that the "oops, something went wrong" one-off errors I sometimes see? This is for a website, so users shouldn't even be aware of the database.

So I have a few questions:

1. Which failure handling approach should I take with data involving multiple users at once?
2. If I do the "instant retry" option, what does that entail for a complex PHP script? Restart the whole request from the top? I'm worried this will cause more problems than it solves.
3. Is there a third option I haven't seen?

Answer 1

A deadlock during a transaction would usually indicate that you were trying to update something that someone else also updated at the same time as you. Exactly how you handle that would be specific to the operation that the user was performing.

In some cases it wouldn't be a problem and you would retry the operation, but I would think that in most cases you would generate an error to the user since you have know way of knowing which update was the "correct" one.