Which should take initiative task on rollback, App

Question

MySql InnoDB is set autocommit off and used default isolation level REPEATABLE READ. There are two scenarioes that two distinct transactions T1 and T2 run in the time sequence below,

1)

time    T1                  T2

t1  update row 1->OK
t2                      update row 2->OK
t3  update row 2->wait->timeout error
t4  commit or rollback or retry t3

T1 gets timeout error at t3 because it can't grap the write lock on row 2 that T2 doesn't release yet, nevertheless, if T1 commits at t4 it leads to "partial" update for T1, i.e. row 1 is updated but row 2 not, thus the "atomicity" rule of ACID is violated by this practice.

According to the "atomicity" rule of ACID a transaction should either "complete" succeed or fail but not partially.

APP must either request T1 to rollback or retry the timed out update till success before commit at t4 upon receiving the error at t3, thus achieve the atomicity rule.

2)

time    T1              T2

t1  update row 1->OK
t2                  update row 2->OK
t3  update row 2->wait
t4                  update row 1-> DB detects deadlock then forces T2 rolled back
      wait->OK

In 1) DB only delivers the timeout error to APP and it is up to APP that decides to rollback T1 or not, but in 2) not only DB detects the deadlock error but also acts to rollack the would-be deadlocker T2.

Theoretically, in 1) DB can also act to rollback T1, but in 2) DB might only cancel the operation that would cause a deadlock then deliver deadlock error to APP, and it is up to APP that decides to rollback T2 or not.

The question lies on what concrete conditions are matched for DB to choose whether APP or itself should handle rollbacks when errors are detected firstly on DB level.

Thanks a lot!

Answer 1

Rollbacks should always be handled by the client application, not the DB. The client may be performing many different operations as a single "unit of work", therefore, the client should have control over when that work is comitted to the database or rolled back.

References
You can refer to this helpful link from Tom Kyte, who feels so strongly about this issue that he's even suggesting removing commit/rollback from PL/SQL (Oracle's procedural language; I know your DB is mysql, but the concept remains the same).

another compelling reason for the CLIENT APPLICATION, the only thing that can really control transaction flow, should either

a) commit or b) rollback

its work. (along with triggers, autonomous transactions and when others, I would do away with commit and rollback in plsql if I had my way :)