Golang+MySQL Cloud调用REST API重复出现Invalid Transaction错误排查

问题描述 #

首次调用创建房间的REST API时运行正常，但第二次调用时反复出现「Invalid Transaction」错误。

相关代码 #

Golang控制器代码 #

func (c ChatApiService) CreateRoom(ctx context.Context, room openapi.Room) (openapi.ImplResponse, error) {

    // Create the Room
    createRoom, err := service.CreateRoom(room, c.db)

    if err != nil {
        return openapi.ImplResponse{}, err
    }

    // Add users to the room
    if room.Users != nil && len(room.Users) > 0 {
        for _, userId := range room.Users {
            err := service.AssignUserToRoom(userId, createRoom.Id, models.RoomRoleMember, c.db)
            if err != nil {
                return openapi.ImplResponse{}, err
            }
        }
    }

    return openapi.ImplResponse{
        Code: 200,
        Body: createRoom,
    }, nil

}

Golang服务层函数代码 #

func CreateRoom(room openapi.Room, db *gorm.DB) (openapi.Room, error) {

    var roomType models.RoomType

    roomId := uuid.New().String()

    switch room.RoomType {
    case "ORGANIZATION":
        roomType = models.RoomTypeOrganization
    case "GROUP":
        roomType = models.RoomTypeGroup
    case "PRIVATE":
        roomType = models.RoomTypePrivate
    case "ADMIN":
        roomType = models.RoomTypeAdmin
    default:
        return openapi.Room{}, errors.New("Invalid Room Type")
    }

    // Create Room
    newRoom := models.Room{
        ID:         roomId,
        Title:      room.Title,
        RoomType:   roomType,
        CreatedBy:  room.CreatedBy,
        Status:     models.StatusActive,
        ModifiedAt: time.Now(),
        CreatedAt:  time.Now(),
    }

    logger.Logger.Info("Create Room", zap.Any("newRoom", newRoom))

    savedRoom := db.Create(&newRoom)
    if savedRoom.Error != nil {
        logger.Logger.Error("Error saving room", zap.Error(savedRoom.Error))
        return openapi.Room{}, savedRoom.Error
    }

    savedRoom.Commit()

    if room.RoomType == "ORGANIZATION" {
        err := CreateRoomOrganization(newRoom.ID, room, db)
        if err != nil {
            return openapi.Room{}, err
        }
    } else if room.RoomType == "GROUP" {
        err := CreateGroupRoom(newRoom.ID, room, db)
        if err != nil {
            return openapi.Room{}, err
        }
    } else if room.RoomType == "PRIVATE" {
        err := CreatePrivateRoom(newRoom.ID, room, db)
        if err != nil {
            return openapi.Room{}, err
        }
    } else if room.RoomType == "ADMIN" {
        err := CreateAdminRoom(newRoom.ID, room, db)
        if err != nil {
            return openapi.Room{}, err
        }
    } else {
        return openapi.Room{}, errors.New("invalid Room Type")
    }

    return dto.RoomDTO(newRoom), nil
} 

完整错误信息 #

"error":"invalid transaction; invalid transaction; invalid transaction; invalid transaction; invalid transaction; invalid transaction; invalid transaction","stacktrace":"bityagi/src/service.CreateRoom\n\t/Users/mabeloza/GolandProjects/patshala/backend/src/service/room_service.go:51\nbityagi/src/handlers.ChatApiService.CreateRoom\n\t/Users/mabeloza/GolandProjects/patshala/backend/src/handlers/room_handler.go:32\nbuild/code/spec/src.(*ChatAPIController).CreateRoom\n\t/Users/mabeloza/GolandProjects/patshala/backend/build/generated/src/api_chat.go:121\nnet/http.HandlerFunc.ServeHTTP\n\t/opt/homebrew/Cellar/go/1.22.0/libexec/src/net/http/server.go:2166\ngithub.com/go-chi/chi/v5.(*Mux).routeHTTP\n\t/Users/mabeloza/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/mux.go:444\nnet/http.HandlerFunc.ServeHTTP\n\t/opt/homebrew/Cellar/go/1.22.0/libexec/src/net/http/server.go:2166\ngithub.com/go-chi/cors.(*Cors).Handler-fm.(*Cors).Handler.func1\n\t/Users/mabeloza/go/pkg/mod/github.com/go-chi/cors@v1.2.1/cors.go:228\nnet/http.HandlerFunc.ServeHTTP\n\t/opt/homebrew/Cellar/go/1.22.0/libexec/src/net/http/server.go:2166\ngithub.com/go-chi/chi/v5/middleware.init.0.RequestLogger.func1.1\n\t/Users/mabeloza/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/middleware/logger.go:54\nnet/http.HandlerFunc.ServeHTTP\n\t/opt/homebrew/Cellar/go/1.22.0/libexec/src/net/http/server.go:2166\ngithub.com/go-chi/chi/v5.(*Mux).ServeHTTP\n\t/Users/mabeloza/go/pkg/mod/github.com/go-chi/chi/v5@v5.0.10/mux.go:90\nnet/http.serverHandler.ServeHTTP\n\t/opt/homebrew/Cellar/go/1.22.0/libexec/src/net/http/server.go:3137\nnet/http.(*conn).serve\n\t/opt/homebrew/Cellar/go/1.22.0/libexec/src/net/http/server.go:2039"}

日志信息 #

{"level":"info","ts":1716237267.974571,"caller":"service/room_service.go:47","msg":"Create Room","newRoom":{"id":"21758551-f470-4532-92c9-2f857ff0e989","title":"Johnny & Jane","description":{"String":"","Valid":false},"image_url":{"String":"","Valid":false},"room_type":"PRIVATE","last_message":{"String":"","Valid":false},"status":"ACTIVE","created_by":"814caa91-3494-46ba-8316-f3ae0d11a34c","created_at":"2024-05-20T15:34:27.974549-05:00","modified_at":"2024-05-20T15:34:27.974549-05:00"}}
{"level":"error","ts":1716237267.974646,"caller":"service/room_service.go:51","msg":"Error saving room","error":"invalid transaction; invalid transaction; invalid transaction; invalid transaction; invalid transaction; invalid transaction; invalid transaction","stacktrace":"bityagi/src/service.CreateRoom\n\t/Users/mabeloza/Golan

问题原因分析 #

核心问题在于服务层代码中手动调用了savedRoom.Commit()：

• GORM的db.Create()操作在非事务环境下会自动执行提交，无需手动调用Commit。
• 第一次调用时，手动Commit会导致当前DB连接的事务状态被异常修改；第二次复用同一个DB连接时，连接处于无效事务状态，因此触发「Invalid Transaction」错误。

解决方案 #

快速修复 #

直接删除服务层代码中的savedRoom.Commit()行即可：

// 移除这行无效的手动提交代码
// savedRoom.Commit()

事务化改造（可选） #

如果需要对创建房间及后续关联操作（如CreateRoomOrganization等）开启原子性事务，应使用GORM的事务API手动管理：

func CreateRoom(room openapi.Room, db *gorm.DB) (openapi.Room, error) {
    // ... 其他类型转换、对象初始化代码 ...

    // 开启事务
    tx := db.Begin()
    defer func() {
        if r := recover(); r != nil {
            tx.Rollback()
        }
    }()

    // 事务内创建房间
    if err := tx.Create(&newRoom).Error; err != nil {
        tx.Rollback()
        logger.Logger.Error("Error saving room", zap.Error(err))
        return openapi.Room{}, err
    }

    // 事务内执行后续关联操作，需传入tx而非原始db
    if room.RoomType == "ORGANIZATION" {
        err := CreateRoomOrganization(newRoom.ID, room, tx)
        if err != nil {
            tx.Rollback()
            return openapi.Room{}, err
        }
    }
    // ... 其他房间类型分支代码 ...

    // 提交事务
    if err := tx.Commit().Error; err != nil {
        return openapi.Room{}, err
    }

    return dto.RoomDTO(newRoom), nil
}

内容的提问来源于stack exchange，提问作者Mabel Oza