Which should take initiative task on rollback, App or DB?

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!

Secede

Your application decided to commit after an error... It could have tried again, or rolled back. In some cases, the failure of some statements executed during the transaction (e.g. logging, or debug) is acceptable. It's up to your application to decide. – Headwards

Do you know any DB do handle rollback by itself in addition to by App? Theoretically it can be, right? – Secede

i added the scenario that DB handles rollback on deadlock. – Secede

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 :)

Basrhin

According to the "atomicity" rule of ACID the transaction( a unit of works ) should complete fail if it can't complete succeed, so the boundary for the transaction to rollback (undo all) to the previous state is already deterministic to an ACID compliant database such as MySql InnoDB. i can't see the need for App to interfere the rollback. In contrast to commit (do it all) there IS a need for App to determine the boundary of works. – Secede

I think we're saying the same thing, the App determines the boundary, or unit of work, hence the App determines when to commit or rollback. – Basrhin

i added the scenario that DB handles rollback on deadlock. – Secede