Search code examples
mysqltransactionsinnodb

Can MySQL transaction rollback without ROLLBACK query?

I'm working on a financial system and I've got a problem with the MySQL transactions.

The system is a simple stock exchange, where users can buy and sell virtual shares. To keep integrity in buy and sell process I use transactions. The problem is that in some cases (I don't know what it depends on) some of transactions are rolled back (or not commited), but next queries are processed.

The process is following:

  1. User wants to buy shares for 1000 USD
  2. In orderbook there are 4 offers for 250 USD
  3. START TRANSACTION
  4. For every offer:
  5. Script does a UPDATE query (moving USD from one user to another and shares in the opposite way). Then script INSERTs entries to history tables.
  6. Users pay a fee (UPDATE balances).
  7. Repeat 5 and 6 for the next offer.
  8. COMMIT

Now the key part - in some cases changes from point 5 are not saved, but from the 6 they are (I see that fee was paid, but there is no transaction in the history). I'm not using ROLLBACK during this transactions and the script is not breaking (because in this case fee wouldn't be paid).

Is there any possibility that transaction is rolling back without ROLLBACK query? Or can MySQL COMMIT only few newest queries instead of all?

Solution

  • It's long time since the question was asked, but the problem actually was that I didn't check for the SQL errors for every query.

    Actually at some points, when I should rollback transaction, I didn't do it.

    If your are looking for answer - check again if you testing ALL queries in your transaction for successful execution and don't trust that the framework you are using is doing it for you automatically (just check it again).