The GoFrame ORM supports database nested transactions, which are commonly used in business projects, especially in mutual calls between business modules, to ensure that the database operations of each business module are within a transaction. This is achieved by implicitly passing and associating the same transaction object through the context. It should be noted that database services often do not support nested transactions but rely on the ORM component layer to implement this using the Transaction Save Point feature. Similarly, we recommend using the Transaction closure method to implement nested transactions. For completeness, we will still introduce nested transaction operations starting from the most basic transaction operation methods here.

1. Example SQL

A simple example SQL, containing two fields id and name:

  1. CREATE TABLE `user` (
  2.   `id` int(10) unsigned NOT NULL COMMENT 'User ID',
  3.   `name` varchar(45) NOT NULL COMMENT 'User Name',
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. Regular Operations (Not Recommended)

  1. db := g.DB()
  3. tx, err := db.Begin()
  4. if err != nil {
  5.     panic(err)
  6. }
  7. if err = tx.Begin(); err != nil {
  8.     panic(err)
  9. }
  10. _, err = tx.Model(table).Data(g.Map{"id": 1, "name": "john"}).Insert()
  11. if err = tx.Rollback(); err != nil {
  12.     panic(err)
  13. }
  14. _, err = tx.Model(table).Data(g.Map{"id": 2, "name": "smith"}).Insert()
  15. if err = tx.Commit(); err != nil {
  16.     panic(err)
  17. }

1. db.Begin and tx.Begin

In our nested transaction example, we see db.Begin and tx.Begin as two ways to start transactions. What is the difference between them? db.Begin actually starts a transaction operation on the database service and returns a transaction operation object tx. All subsequent transaction operations are managed through this tx transaction object. tx.Begin starts a nested transaction in the current transaction operation, and by default uses automatic naming for the nested transaction SavePoint. The naming format is transactionN, where N represents the nesting level. If you see the log showing SAVEPOINT `transaction1` , it indicates the current nesting level is 2 (starting from 0).

2. More Detailed Logs

goframe‘s ORM has a very comprehensive logging mechanism. If you enable SQL logging, you will see the following log information displaying the detailed execution process of the entire database request:

  1. 2021-05-22 21:12:10.776 [DEBU] [  4 ms] [default] [txid:1] BEGIN
  2. 2021-05-22 21:12:10.776 [DEBU] [  0 ms] [default] [txid:1] SAVEPOINT `transaction0`
  3. 2021-05-22 21:12:10.789 [DEBU] [ 13 ms] [default] [txid:1] SHOW FULL COLUMNS FROM `user`
  4. 2021-05-22 21:12:10.790 [DEBU] [  1 ms] [default] [txid:1] INSERT INTO `user`(`id`,`name`) VALUES(1,'john')
  5. 2021-05-22 21:12:10.791 [DEBU] [  1 ms] [default] [txid:1] ROLLBACK TO SAVEPOINT `transaction0`
  6. 2021-05-22 21:12:10.791 [DEBU] [  0 ms] [default] [txid:1] INSERT INTO `user`(`id`,`name`) VALUES(2,'smith')
  7. 2021-05-22 21:12:10.792 [DEBU] [  1 ms] [default] [txid:1] COMMIT

Here, [txid:1] indicates the transaction ID recorded by the ORM component, and each real transaction operation will have a different ID, while nested transactions within the same real transaction share the same transaction ID.

After execution, query the database result:

  1. mysql> select * from `user`;
  2. +----+-------+
  3. | id | name  |
  4. +----+-------+
  5. |  2 | smith |
  6. +----+-------+
  7. 1 row in set (0.00 sec)

You can see that the first operation was successfully rolled back, and only the second operation was successfully executed and committed.

3. Closure Operations (Recommended)

We can also implement nested transactions through closure operations using the Transaction method.

  1. db.Transaction(ctx, func(ctx context.Context, tx gdb.Tx) error {
  2.     // Nested transaction 1.
  3.     if err := tx.Transaction(ctx, func(ctx context.Context, tx gdb.Tx) error {
  4.         _, err := tx.Model(table).Ctx(ctx).Data(g.Map{"id": 1, "name": "john"}).Insert()
  5.         return err
  6.     }); err != nil {
  7.         return err
  8.     }
  9.     // Nested transaction 2, panic.
  10.     if err := tx.Transaction(ctx, func(ctx context.Context, tx gdb.Tx) error {
  11.         _, err := tx.Model(table).Ctx(ctx).Data(g.Map{"id": 2, "name": "smith"}).Insert()
  12.         // Create a panic that can make this transaction rollback automatically.
  13.         panic("error")
  14.         return err
  15.     }); err != nil {
  16.         return err
  17.     }
  18.     return nil
  19. })

In the nested transaction closure, you do not necessarily have to use the tx object and can instead directly use the db object or dao package. This approach is more common, especially in method-level calls, as it means developers do not have to worry about passing the tx object or determine whether the current transaction should be nested; everything is automatically managed by the component, greatly reducing the mental workload for developers. However, be sure to pass the ctx context variable through every level. For example:

  1. db.Transaction(ctx, func(ctx context.Context, tx gdb.Tx) error {
  2.     // Nested transaction 1.
  3.     if err := db.Transaction(ctx, func(ctx context.Context, tx gdb.Tx) error {
  4.         _, err := db.Model(table).Ctx(ctx).Data(g.Map{"id": 1, "name": "john"}).Insert()
  5.         return err
  6.     }); err != nil {
  7.         return err
  8.     }
  9.     // Nested transaction 2, panic.
  10.     if err := db.Transaction(ctx, func(ctx context.Context, tx gdb.Tx) error {
  11.         _, err := db.Model(table).Ctx(ctx).Data(g.Map{"id": 2, "name": "smith"}).Insert()
  12.         // Create a panic that can make this transaction rollback automatically.
  13.         panic("error")
  14.         return err
  15.     }); err != nil {
  16.         return err
  17.     }
  18.     return nil
  19. })

If you have enabled SQL logging, you will see the following log information displaying the detailed execution process of the entire database request:

  1. 2021-05-22 21:18:46.672 [DEBU] [  2 ms] [default] [txid:1] BEGIN
  2. 2021-05-22 21:18:46.672 [DEBU] [  0 ms] [default] [txid:1] SAVEPOINT `transaction0`
  3. 2021-05-22 21:18:46.673 [DEBU] [  0 ms] [default] [txid:1] SHOW FULL COLUMNS FROM `user`
  4. 2021-05-22 21:18:46.674 [DEBU] [  0 ms] [default] [txid:1] INSERT INTO `user`(`id`,`name`) VALUES(1,'john')
  5. 2021-05-22 21:18:46.674 [DEBU] [  0 ms] [default] [txid:1] RELEASE SAVEPOINT `transaction0`
  6. 2021-05-22 21:18:46.675 [DEBU] [  1 ms] [default] [txid:1] SAVEPOINT `transaction0`
  7. 2021-05-22 21:18:46.675 [DEBU] [  0 ms] [default] [txid:1] INSERT INTO `user`(`name`,`id`) VALUES('smith',2)
  8. 2021-05-22 21:18:46.675 [DEBU] [  0 ms] [default] [txid:1] ROLLBACK TO SAVEPOINT `transaction0`
  9. 2021-05-22 21:18:46.676 [DEBU] [  1 ms] [default] [txid:1] ROLLBACK

ORM Transaction - Nested - 图1warning

If the ctx context variable is not passed through each layer, the nested transaction will fail. Let’s look at an erroneous example:

  1. db.Transaction(ctx, func(ctx context.Context, tx gdb.Tx) error {
  2.     // Nested transaction 1.
  3.     if err := db.Transaction(ctx, func(ctx context.Context, tx gdb.Tx) error {
  4.         _, err := db.Model(table).Ctx(ctx).Data(g.Map{"id": 1, "name": "john"}).Insert()
  5.         return err
  6.     }); err != nil {
  7.         return err
  8.     }
  9.     // Nested transaction 2, panic.
  10.     if err := db.Transaction(ctx, func(ctx context.Context, tx gdb.Tx) error {
  11.         _, err := db.Model(table).Data(g.Map{"id": 2, "name": "smith"}).Insert()
  12.         // Create a panic that can make this transaction rollback automatically.
  13.         panic("error")
  14.         return err
  15.     }); err != nil {
  16.         return err
  17.     }
  18.     return nil
  19. })

Open the SQL execution log, and after execution, you will see the following log content:

  1. 2021-05-22 21:29:38.841 [DEBU] [  3 ms] [default] [txid:1] BEGIN
  2. 2021-05-22 21:29:38.842 [DEBU] [  1 ms] [default] [txid:1] SAVEPOINT `transaction0`
  3. 2021-05-22 21:29:38.843 [DEBU] [  1 ms] [default] [txid:1] SHOW FULL COLUMNS FROM `user`
  4. 2021-05-22 21:29:38.845 [DEBU] [  2 ms] [default] [txid:1] INSERT INTO `user`(`id`,`name`) VALUES(1,'john')
  5. 2021-05-22 21:29:38.845 [DEBU] [  0 ms] [default] [txid:1] RELEASE SAVEPOINT `transaction0`
  6. 2021-05-22 21:29:38.846 [DEBU] [  1 ms] [default] [txid:1] SAVEPOINT `transaction0`
  7. 2021-05-22 21:29:38.847 [DEBU] [  1 ms] [default] INSERT INTO `user`(`id`,`name`) VALUES(2,'smith')
  8. 2021-05-22 21:29:38.848 [DEBU] [  0 ms] [default] [txid:1] ROLLBACK TO SAVEPOINT `transaction0`
  9. 2021-05-22 21:29:38.848 [DEBU] [  0 ms] [default] [txid:1] ROLLBACK

You can see that the second INSERT operation INSERT INTO `user`(`id`,`name`) VALUES(2,'smith') has no transaction ID printed, indicating it was not used within a transaction and hence will be truly committed to the database execution and cannot be rolled back.

4. SavePoint/RollbackTo

Developers can also flexibly use the Transaction Save Point feature and implement custom SavePoint naming and designated Point rollback operations.

  1. tx, err := db.Begin()
  2. if err != nil {
  3.     panic(err)
  4. }
  5. defer func() {
  6.     if err := recover(); err != nil {
  7.         _ = tx.Rollback()
  8.     }
  9. }()
  10. if _, err = tx.Model(table).Data(g.Map{"id": 1, "name": "john"}).Insert(); err != nil {
  11.     panic(err)
  12. }
  13. if err = tx.SavePoint("MyPoint"); err != nil {
  14.     panic(err)
  15. }
  16. if _, err = tx.Model(table).Data(g.Map{"id": 2, "name": "smith"}).Insert(); err != nil {
  17.     panic(err)
  18. }
  19. if _, err = tx.Model(table).Data(g.Map{"id": 3, "name": "green"}).Insert(); err != nil {
  20.     panic(err)
  21. }
  22. if err = tx.RollbackTo("MyPoint"); err != nil {
  23.     panic(err)
  24. }
  25. if err = tx.Commit(); err != nil {
  26.     panic(err)
  27. }

If you open the SQL log, you will see the following log information displaying the detailed execution process of the entire database request:

  1. 2021-05-22 21:38:51.992 [DEBU] [  3 ms] [default] [txid:1] BEGIN
  2. 2021-05-22 21:38:52.002 [DEBU] [  9 ms] [default] [txid:1] SHOW FULL COLUMNS FROM `user`
  3. 2021-05-22 21:38:52.002 [DEBU] [  0 ms] [default] [txid:1] INSERT INTO `user`(`id`,`name`) VALUES(1,'john')
  4. 2021-05-22 21:38:52.003 [DEBU] [  1 ms] [default] [txid:1] SAVEPOINT `MyPoint`
  5. 2021-05-22 21:38:52.004 [DEBU] [  1 ms] [default] [txid:1] INSERT INTO `user`(`id`,`name`) VALUES(2,'smith')
  6. 2021-05-22 21:38:52.005 [DEBU] [  1 ms] [default] [txid:1] INSERT INTO `user`(`id`,`name`) VALUES(3,'green')
  7. 2021-05-22 21:38:52.006 [DEBU] [  0 ms] [default] [txid:1] ROLLBACK TO SAVEPOINT `MyPoint`
  8. 2021-05-22 21:38:52.006 [DEBU] [  0 ms] [default] [txid:1] COMMIT

After execution, query the database result:

  1. mysql> select * from `user`;
  2. +----+------+
  3. | id | name |
  4. +----+------+
  5. |  1 | john |
  6. +----+------+
  7. 1 row in set (0.00 sec)

You can see that by saving a SavePoint named MyPoint after the first Insert operation, the subsequent operations were all rolled back using the RollbackTo method, so only the first Insert operation was successfully committed and executed.

5. Reference Example of Nested Transactions in a Project

To simplify the example, let’s use a user module-related example, such as user registration, saving user’s basic information (user) and detailed information (user_detail) to two tables through transaction operations. If any table operation fails, the entire registration operation will fail. To demonstrate the nested transaction effect, we separate the basic information and detailed information management into two dao objects.

Assume our project follows the goframe standard project structuring divided into three layers: api-service-dao; our nested transaction operation might look like this.

controller

  1. // User registration HTTP interface
  2. func (*cUser) Signup(r *ghttp.Request) {
  3.     // ....
  4.     service.User().Signup(r.Context(), userServiceSignupReq)
  5.     // ...
  6. }

Indicates the handling of the HTTP request and passes the Context context variable to subsequent processes.

service

  1. // User registration business logic handling
  2. func (*userService) Signup(ctx context.Context, r *model.UserServiceSignupReq) {
  3.     // ....
  4.     dao.User.Transaction(ctx, func(ctx context.Context, tx gdb.Tx) error {
  5.         err := dao.User.Ctx(ctx).Save(r.UserInfo)
  6.         if err != nil {
  7.             return err
  8.         }
  9.         err := dao.UserDetail.Ctx(ctx).Save(r.UserDetail)
  10.         if err != nil {
  11.             return err
  12.         }
  13.         return nil
  14.     })
  15.     // ...
  16. }

As you can see, the user table and user_detail table internally use nested transactions to perform unified transaction operations. Note that within the closure, use the Ctx method to pass the context variable to the next level. If there are calls to other service objects within the closure, you also need to pass the ctx variable, for example:

  1. func (*userService) Signup(ctx context.Context, r *model.UserServiceSignupReq) {
  2.     // ....
  3.     dao.User.Transaction(ctx, func(ctx context.Context, tx gdb.Tx) (err error) {
  4.         if err = dao.User.Ctx(ctx).Save(r.UserInfo); err != nil {
  5.             return err
  6.         }
  7.         if err = dao.UserDetail.Ctx(ctx).Save(r.UserDetail); err != nil {
  8.             return err
  9.         }
  10.         if err = service.XXXA().Call(ctx, ...); err != nil {
  11.             return err
  12.         }
  13.         if err = service.XXXB().Call(ctx, ...); err != nil {
  14.             return err
  15.         }
  16.         if err = service.XXXC().Call(ctx, ...); err != nil {
  17.             return err
  18.         }
  19.         // ...
  20.         return nil
  21.     })
  22.     // ...
  23. }

dao

The code for the dao layer can be fully automated and maintained by goframe cli tools.