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 …