So you're using explicit transactions. Everything appears to work
(every query gives the expected result) until you get to
Then you get an exception thrown from COMMIT. What happened?
Usually this would be because the server has been shut down, or you've lost the connection.
The problem is, that you can't assume that the commit failed, but you also can't assume it succeeded.
A robust application must make NO ASSUMPTION about whether a failed commit did, indeed, commit the transaction or not. It can safely assume that either all or none of it was committed, but can't easily tell which.
So the only way to really know is to have your application somehow remember that the transaction MIGHT have failed, and check later.
- Ignore it and deal with any inconsistencies manually, or
decide that you don't care :)