Ensure MySQL transaction fails on COMMIT

2
votes

I want to test a script what it does when MySQL transaction fails. So I want to simulate transaction failure on COMMIT.

Image the script opens a transaction, makes some queries, and then commits the transaction. I want to implant some extra query to the transaction, to ensure the transaction will fail on COMMIT. So I can test how the script recovers from the transation failure.

I don't want to use explicit ROLLBACK. I want to simulate some real-life case when a commit fails. The exact DB structure is unimportant. It may adapt to the solution.

Edit: I need the transaction to fail on COMMIT, not on some prior query. Therefore answers that rollback prior to COMMIT are not what I want. Such as this one: How to simulate a transaction failure?

My unsuccessful attempts:

  • Inserting a row with invalid PK or FK fails immediately with insert. Temporarily disabling FK checks with FOREIGN_KEY_CHECKS=0 won't help as they won't be rechecked on COMMIT. If it was psql, defferable constraints would help. But not in mysql.

  • Opening two parallel transactions and inserting a row with the same PK (or any column with unique constaint) in both transactions locks the later transaction on insert and waits for the former transaction. So the transaction rolls back on insert not on commit.

1
mysqltestingtransactions
stackoverflow.com/questions/3960189/… This might help , I like the torching your hard drive idea – P.Salmon
@P.Salmon thanks for the link. I seen those answers, but all of them are too theoretical. And approaches with constaints and timeouts seem not work in MySQL. I'm starting to be curious if COMMIT can actually fail at all in MySQL. – martin.macko.47
So far I managed COMMIT to fail only if I killed mysql server before the COMMIT. But that's not very convenient for automated tests :-/ – martin.macko.47

1 Answers

0
votes

So I believe you can try the following

  1. Two phase commit : Use two database ( First and second) and make use of two phase commit. When the commit statement is supposed to be executed you can shutdown the second db. This way your commit operation will fail and transaction will rollback.
  2. You executed several inserts and before your commit your database server dies. A transaction may fail if it doesn't receive commit .

Hopefully it helps!