Recursive/nested transaction

Question

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 property (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.

score 2 · Answer 1 · answered Jun 01 '11 at 14:14

2

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.

answered Jun 01 '11 at 14:14

Marc B

356,200
43
426
500

They don't use the same tables, just nested transaction on different tables. Is there problem? – Dagang Jun 02 '11 at 14:49
Possibly if any of those tables have foreign keys against each other. But if they're fully independent, then no, should be ok. – Marc B Jun 02 '11 at 16:25

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

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

See this answer:

Mysql transactions within transactions

edited May 23 '17 at 12:21

Community

1
1

answered Nov 02 '13 at 23:22

Buttle Butkus

9,206
13
79
120

I answered that you can do it with a savepoint. I have seen many answers consisting mainly of a link with a short explanation. – Buttle Butkus Nov 07 '13 at 09:16
If I delete this answer do I get my points back? – Buttle Butkus Nov 07 '13 at 09:18
Yes, the reputation changes of any votes on a post are reversed when the post is deleted (except when the votes have been cast long ago). But the downvote has been reversed. – Bernhard Barker Nov 07 '13 at 09:25

score 0 · Answer 3 · answered Jun 01 '11 at 14:09

0

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

answered Jun 01 '11 at 14:09

Eric Petroelje

59,820
9
127
177

Recursive/nested transaction

3 Answers3