Gorm Transaction Problem - single connection in restful api but a transaction can be only used once

Your Question

I am using GIN framework and Gorm to write restful APIs with Clean Architecture pattern in different layers (controller, usecase, repository, domain) but facing a problem that single connection use in all functions. Once I use transaction begin and commit/ rollback in one function. Then client request another api will cause sql transaction has already been committed or rolled back (I know that a transaction can be only used once) so how to handle in this use case? Thanks

Case: 1. call api/dummy/update 2. call api/dummy/list

Result: app/repositories/dummy_repository.go:46 sql: transaction has already been committed or rolled back

main.go

package main

import (
    "myapp/app/di"
    "myapp/app/repositories/db"

    "github.com/gin-gonic/gin"
    "github.com/spf13/viper"
)



func main() {

    // Set the router as the default one provided by Gin
    route_engine = gin.Default()

    // initial database connection - mysql
    dbConn := db.ConnectMysqlGormDatabase()

    // depdency injection
    di.InitializeAPIs(dbConn, route_engine)

    // Start serving the application
    route_engine.Run(viper.GetString("server.address"))
}

dbconnection.go // for connect mysql

package db

import (
    "gorm.io/driver/mysql"
    "github.com/spf13/viper"
    "gorm.io/gorm"
)

// var db *gorm.DB

func ConnectMysqlGormDatabase() (db *gorm.DB) {

    dbHost := viper.GetString(`database.host`)
    dbPort := viper.GetInt(`database.port`)
    dbUser := viper.GetString(`database.user`)
    dbPass := viper.GetString(`database.pass`)
    dbName := viper.GetString(`database.name`)

    dsn := fmt.Sprintf("%s:%s@tcp(%s:%v)/%s?charset=utf8mb4&collation=utf8mb4_unicode_520_ci&parseTime=True&loc=Local", dbUser, dbPass, dbHost, dbPort, dbName)

    gormDB, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
        DisableForeignKeyConstraintWhenMigrating: true,
    })

    if err != nil {
        fmt.Println("connection to mysql failed:", err)
        panic("exit")

    }

    MysqlMigration(gormDB)

    return gormDB
}

di.go // declare all repositories, usecases, controllers and routes

/*
dependency injection -
*/
package di

import (
    "myapp/app/deliveries/httpDelivery"
    "myapp/app/repositories"
    "myapp/app/usecases"
    "myapp/middlewares"

    "github.com/gin-gonic/gin"
    "gorm.io/gorm"
)

func InitializeAPIs(dbConn *gorm.DB, route_engine *gin.Engine) {

    // repositories
    dummyRepository := repositories.NewDummy(dbConn)

    // usecase
    dummyUseCase := usecases.NewDummyUseCase(
        dbConn, 
        dummyRepository,
    )

    // controller
    dummyHandler := httpDelivery.NewDummyHandler(
        route_engine,
        dummyUseCase,
    )

    api := route_engine.Group("/api")
    api.POST("/dummy/update", dummyHandler.PostUpdate)
    api.GET("/dummy/list", dummyHandler.GetList)

}

dummy_handler.go // controller

package httpDelivery

import (
    "net/http"
    "myapp/app/usecases"

    "github.com/gin-gonic/gin"
    "github.com/go-playground/validator"
)

type DummyHandler struct {
    dummy_usecase usecases.InterfaceDummyUsecase
}

func NewDummyHandler(route_engine *gin.Engine,
    dummy_usecase usecases.InterfaceDummyUsecase,

) *DummyHandler {

    handler := &DummyHandler{
        dummy_usecase: dummy_usecase,
    }
    return handler
}

func (dh *DummyHandler) PostUpdate(ctx *gin.Context) {

    resp_obj := NewCustomResponseJson(ctx.Request.Context())

    var request_body struct {
        UserId uint32 `json:"user_id" validate:"required"`
        Email  string `json:"email" validate:"required"`
    }

    err := ctx.ShouldBindJSON(&request_body)
    if err != nil {
        ctx.JSON(http.StatusOK, resp_obj.Fail(err))
        return
    }
    validate := validator.New()
    err = validate.Struct(&request_body)
    if err != nil {
        ctx.JSON(http.StatusOK, resp_obj.Fail(err))
        return
    }

    dummy, err := dh.dummy_usecase.FindById(request_body.UserId)
    if err != nil {
        ctx.JSON(http.StatusOK, resp_obj.Fail(err))
        return
    }

    dummy.Email = request_body.Email
    err = dh.dummy_usecase.Update(&dummy)
    if err != nil {
        ctx.JSON(http.StatusOK, resp_obj.Fail(err))
        return
    }
    result := resp_obj.Success()
    ctx.JSON(http.StatusOK, result)
}

func (dh *DummyHandler) GetList(ctx *gin.Context) {

    resp_obj := NewCustomResponseJson(ctx.Request.Context())

    list, err := dh.dummy_usecase.FindAll()
    if err != nil {
        ctx.JSON(http.StatusOK, resp_obj.Fail(err))
        return
    }

    result := resp_obj.Success()
    result["list"] = list
    ctx.JSON(http.StatusOK, result)
}

dummy_usecase.go

package usecases

import (
    "fmt"
    "reflect"
    "myapp/app/domains"
    "myapp/app/repositories"

    "gorm.io/gorm"
)

type InterfaceDummyUsecase interface {
    WithTrx(*gorm.DB) InterfaceDummyUsecase

    FindAll() ([]domains.Dummy, error)
    Update(model *domains.Dummy) error
}

type dummyUseCase struct {
    db        *gorm.DB
    dummyRepo repositories.InterfaceDummyRepository
}

func NewDummyUseCase(db *gorm.DB, repo repositories.InterfaceDummyRepository) InterfaceDummyUsecase {
    return &dummyUseCase{
        db:        db,
        dummyRepo: repo,
    }
}

func (m *dummyUseCase) WithTrx(trxHandle *gorm.DB) InterfaceDummyUsecase {
    m.dummyRepo = m.dummyRepo.WithTrx(trxHandle)
    return m
}

func (m *dummyUseCase) FindAll() ([]domains.Dummy, error) {
    newssubscription_list, err := m.dummyRepo.FindAll()
    return newssubscription_list, err
}

func (m *dummyUseCase) Update(model *domains.Dummy) error {
    tx := m.db.Begin()
    if err := tx.Error; err != nil {
        fmt.Println("tx.Error;")
        return err
    }
    // ....
    // other usecase code
    // ....
    err := m.dummyRepo.WithTrx(tx).Update(model)
    if err != nil {
        tx.Rollback()
        return err
    }
    // ....
    // other usecase code
    // ....
    err = tx.Commit().Error
    if err != nil {
        fmt.Println("Rollback: sync error 2")
        tx.Rollback()
        return err
    }
    fmt.Println("Commit")
    return err
}

// dummy_repository.go

package repositories

import (
    "log"
    "myapp/app/domains"

    "gorm.io/gorm"
)

type InterfaceDummyRepository interface {
    WithTrx(*gorm.DB) InterfaceDummyRepository

    FindAll() ([]domains.Dummy, error)
    FindById(id uint32) (domains.Dummy, error)
    Update(model *domains.Dummy) error
}

type dummyRepository struct {
    Conn *gorm.DB
}

// GORM - delcare repository
func NewDummy(conn *gorm.DB) InterfaceDummyRepository {
    return &dummyRepository{
        Conn: conn,
    }
}

func (m *dummyRepository) WithTrx(trxHandle *gorm.DB) InterfaceDummyRepository {
    if trxHandle == nil {
        log.Print("Transaction Database not found")
        return m
    }
    m.Conn = trxHandle
    return m
}

func (m *dummyRepository) FindAll() ([]domains.Dummy, error) {
    var dummy_list []domains.Dummy
    err := m.Conn.Find(&dummy_list).Error

    return dummy_list, err
}

func (m *dummyRepository) FindById(id uint32) (domains.Dummy, error) {
    var dummy domains.Dummy
    err := m.Conn.First(&dummy, id).Error

    return dummy, err
}

func (m *dummyRepository) Update(model *domains.Dummy) error {
    err := m.Conn.Omit("id").Updates(&model).Error
    return err
}

func (m *dummyRepository) UpdateModel(model *domains.Dummy) error {
    err := m.Conn.Debug().Model(&model).Omit("id").Updates(&model).Error
    return err
}

The document you expected this should be explained

Expected answer

Comment From: a631807682

Can you provide a simpler repro code? Or put the appeal file into a git repository and make sure it works. Actually, I don't have enough time to watch the business code if you don't do that.

Comment From: kentestforjob

@a631807682 Thanks your help. I just create a repository in github https://github.com/kentestforjob/gorm-transactionerror You can follow the instructions in readMe to run the program. Thanks a lot.

Comment From: a631807682

This has nothing to do with gorm. In fact, you should not reuse dummyRepo. First, WithTrx will modify the referenced db instance. Usually, the http framework will create goroutine for each connection, which will also cause data race.