Locked when i try to append association in transaction mode via `gorm/gen`

[alan890104]

GORM Playground Link

It's really hard to simulate by gen.go in gorm playground repo, but this issue is indeed critical. Thanks to @peterxcli for discovering this bug.

Description

First, we define User and Role in model package. A user has many roles, and a role can assign to multiple users, so they are many2many relation.

type User struct {
	ID    string  `json:"id" gorm:"primaryKey"`
	Name  string  `json:"name" gorm:"unique"`
	Roles []*Role `json:"roles" gorm:"many2many:user_roles;"`
}

func (u *User) BeforeCreate(db *gorm.DB) error {
	if u.ID == "" {
		u.ID = uuid.NewString()
	}
	return nil
}

type Role struct {
	ID     string `json:"id" gorm:"primaryKey"`
	Policy string `json:"policy" gorm:"unique"`
}

func (r *Role) BeforeCreate(db *gorm.DB) error {
	if r.ID == "" {
		r.ID = uuid.NewString()
	}
	return nil
}

And then, we generate the query for these two structs

func main() {
	g := gen.NewGenerator(gen.Config{
		OutPath: "./query",
		Mode:    gen.WithDefaultQuery | gen.WithQueryInterface,
	})

	gormdb, err := gorm.Open(mysql.Open("myuser:mypassword@(127.0.0.1:3306)/mydatabase?charset=utf8mb4&parseTime=True&loc=Local"))
	if err != nil {
		log.Fatal(err)
	}
	g.UseDB(gormdb)
	g.ApplyBasic(model.User{}, model.Role{})
	g.Execute()

	// auto migrate
	gormdb.AutoMigrate(&model.User{}, &model.Role{})
}

Next, in our main function, we insert the default rules

        db, err := gorm.Open(mysql.Open("myuser:mypassword@(127.0.0.1:3306)/mydatabase?charset=utf8mb4&parseTime=True&loc=Local"))
	if err != nil {
		log.Fatal(err)
	}
	if err = db.AutoMigrate(&model.User{}, &model.Role{}); err != nil {
		log.Fatal(err)
	}

	// create default roles
	roles := []model.Role{
		{Policy: "admin"},
		{Policy: "user"},
		{Policy: "guest"},
	}
	if err = db.CreateInBatches(&roles, 100).Error; err != nil {
		log.Println("roles already exist")
	}

However, if i try to use a transaction to append roles to a single user, the transaction will lock until timeout

	q := query.Use(db)
	if err := q.Transaction(func(tx *query.Query) error {
		user := model.User{
			Name: "alan",
		}
		if err := tx.User.WithContext(context.Background()).Create(&user); err != nil {
			return err
		}
		log.Println("User created with id:", user.ID)
		// append roles
		roleIDs := []string{roles[0].ID, roles[1].ID}
		userRoles := make([]*model.Role, len(roleIDs))
		for i, id := range roleIDs {
			userRoles[i] = &model.Role{ID: id}
		}
		if err := q.User.Roles.Model(&user).Append(userRoles...); err != nil {
			return err
		}
		return nil
	}); err != nil {
		log.Fatal(err)
	}

[bkmeneguello]

I have similar issues with transaction. I'm using sqlite for unit tests and have SetMaxOpenConns(1). When I try to use gen.Transaction it locks, but gorm.DB.Transaction works.