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

[kentestforjob]

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

[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.

[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.

[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.