1

I'm running a big transaction on my MySQL database and sometimes it causes deadlocks. I was thinking in running a nested transaction in order to prevent it from happening.

So my question is, if I run a nested transaction, the rows that were locked by this smaller transaction will be "released" as soon as the transaction finish, independently of the outer transaction?

My goal is to release the lock of a table as soon as possible

Giovani Grifante
  • 452
  • 3
  • 14
  • 1
    I guess if MySQL *would* [support nested transactions](https://stackoverflow.com/q/1306869), an rollback could release the locks (although I would expect an implementation where it would keep the lock), a commit cannot (as the outer transaction still can rollback, including the inner transactions). – Solarflare Jan 08 '19 at 13:03
  • 1
    I'd expect the changes a nested transaction made not to be (finally) committed before the parent transaction is committed. The changes of the nested transaction wouldn't make sense without the outer transaction. So I doubt your assumption holds true for commits. But it should hold true for rollbacks as long as the locks aren't also needed by the parent or sibling transactions. Maybe you want independent transactions instead of nested ones. – sticky bit Jan 08 '19 at 13:10

0 Answers0