Recursive/nested transaction [duplicate]

This question already has answers here: Mysql transactions within transactions (7 answers) Closed 9 years ago.

I have 2 store procedures A, B, both are in a transaction block. The problems is A calls B, so, there is recursive/nested transaction. I'm not sure is there problem? What will happen when I call A?

Cited from "MySQL Stored Procedure Programming":

START TRANSACTION signifies the commencement of a new transaction. If an existing transaction is already in progress, then START TRANSACTION will issue an implicit COMMIT. When you issue START TRANSACTION, the autocommit pro开发者_运维技巧perty (described in the next section) is effectively and implicitly set to 0 until the transaction ends. We recommend that you explicitly commit or roll back existing transactions before any START TRANSACTION statements, since the implicit COMMIT might not be obvious to someone reading or maintaining your code.

---

If both procedures set/use locks on the same resource, then you'll get a deadlock. B holds a lock on some table/row/field that A also requires, meaning A can't get its own lock. So A will sit and wait to acquire the lock until the DBMS times it out and rolls things back.

---

As long as B doesn't also call A, you don't have a recursive situation, just a nested transaction - which should be fine.

---

Yes, it is possible. They just cannot overlap. You can do it using SAVEPOINT.

See this answer:

Mysql transactions within transactions