I'd been doing some stress testing of my mysql application today,
and I was hitting some weird cases. Several transactions were
deadlocking - this was expected - but the number of records that
got inserted into my table was more than the number that I
expected after subtracting errors.
My test was fairly simple:
- Fork 15 processes
- Insert and update 100 records in each process, running each INSERT/UPDATE pair inside one transaction
- ROLLBACK on error
Either the INSERT or the UPDATE was expected to fail due to
deadlock, and the whole transaction should have rolled back
leaving no record in the table.
Before I go on, I should mention that I was using InnoDB, which
does support transactions.
What I expected was that the total number of records in the table
+ the total number of INSERT/UPDATE aborts due to deadlock should
be equal to 1500 (15*100). What …