Can I rollback/abort a transaction if no rows were inserted?

Question

I have a transaction in which I want to do something like this:

START TRANSACTION;

INSERT INTO table1 (...)
SELECT ... FROM table 2;

... if select count(*) from table1 returns 0 abort/rollback ...

COMMIT;

Is this possible?

Answer 1

Sure. In MySQL, you can only use if in stored procedures, so this is typically done client side. Pseudo-code:

int rows = db.ExecuteScalar(
    "start transaction; " +
    "insert into table1 (col1) select col1 from table2; " +
    "select count(*) from table1");
if (rows == 0) {
    db.ExecuteNonQuery("abort transaction");
} else {
    db.ExecuteNonQuery("commit transaction");
}

Rolling back a transaction that did nothing (inserted no rows) doesn't make a whole lot of sense, but I'm assuming you simplified reality for this question.