How to properly terminate a session on context timeout?

[ryanrazsa]

I have a service that is writing batch insert statements with a context timeout.

models = make([]struct, 200)
timeoutCtx, cancel := context.WithTimeout(ctx, 10*time.Second)
defer cancel()

var gorm *gorm.DB

tx := gorm.
    Session(&gorm.Session{SkipDefaultTransaction: true, Context: timeoutCtx}).
    Table(TableName("tableName")).
    Clauses(clause.OnConflict{DoNothing: true}). 
    Create(models)
if tx.Error != nil {
    return tx.Error
}
return nil

Every once in a while the batch inserts cause the timeout context to hit and the application connection is reused for another batch insert into the DB. However, on the DB side, the session/connection remains open for however long the actual insert takes. Each of these are taking up a connection slot while in the worst case, GORM is creating new sessions to the db that also timeout and eventually all the slots are taken, locking the database.

Is there a way to ensure that a session is closed on context timeout and reduce this session leak?

[ryanrazsa]

I was able to reproduce this with the following code

type Model struct {
	Id       string    `gorm:"primaryKey"`
	Location string    `gorm:"primaryKey"`
	Creation time.Time `gorm:"primaryKey;index;type:DATE"`
	Price    int64     `gorm:"type:MEDIUMINT;index"`
	ValueA   int64     `gorm:"type:MEDIUMINT;index"`
	ValueB   int64     `gorm:"type:MEDIUMINT;index"`
	ValueC   int64     `gorm:"type:MEDIUMINT;index"`
}

func main() {
        var mysqlConfig mysql.Config{}
        gormConfig := &gorm.Config{
		Logger: logger.Default.LogMode(logger.Silent),
	}

	gormClient, err := gorm.Open(mysql.New(*mysqlConfig), gormConfig)
	if err != nil {
		panic(err)
	}
	db, err := gormClient.DB()
	if err != nil {
		panic(err)
	}

	err = db.Ping()
	if err != nil {
		panic(err)
	}

	db.SetMaxIdleConns(2)
	db.SetMaxOpenConns(2)
	db.SetConnMaxLifetime(5 * time.Second)

	err = gormClient.AutoMigrate(Model{})
	if err != nil {
		panic(err)
	}

	// Preload table with ~1M rows before this

	var wg sync.WaitGroup
	for f := 0; f < 1000; f++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for s := 0; s < 60; s++ {
				models := make([]*Model, 0)
				for i := 0; i < 200; i++ {
					models = append(models, generateRandomModel())
				}
				err := Put(context.Background(), models, gormClient)
				if err != nil {
					fmt.Println(err)
				}
			}
		}()
	}
	wg.Wait()

}

func Put(ctx context.Context, models []*Model, db *gorm.DB) error {
	timeoutCtx, cancel := context.WithTimeout(ctx, 500*time.Millisecond)
	defer cancel()
	tx := db.
		Session(&gorm.Session{SkipDefaultTransaction: true, Context: timeoutCtx}).
		Table("models").
		Clauses(clause.OnConflict{DoNothing: true}).
		Create(models)
	if tx.Error != nil {
		return tx.Error
	}
	return nil
}

func generateRandomModel() *Model {
	locations := map[int64]string{
		0: "US",
		1: "CANADA",
		2: "MEXICO",
	}
	id, _ := rand.Int(rand.Reader, big.NewInt(1000000000000000000))
	dc, _ := rand.Int(rand.Reader, big.NewInt(3))
	days, _ := rand.Int(rand.Reader, big.NewInt(10))
	now := time.Now()
	rD := now.Add(time.Duration(days.Int64()+60) * time.Hour * 24)
	price, _ := rand.Int(rand.Reader, big.NewInt(1000000))
	a, _ := rand.Int(rand.Reader, big.NewInt(1000000))
	b, _ := rand.Int(rand.Reader, big.NewInt(1000000))
	c, _ := rand.Int(rand.Reader, big.NewInt(1000000))

	return &Model{
		Id:       strconv.Itoa(int(id.Int64())),
		Location: locations[dc.Int64()],
		Creation: rD,
		Price:    price.Int64(),
		ValueA:   a.Int64(),
		ValueB:   b.Int64(),
		ValueC:   c.Int64(),
	}
}

This is a scaled down approximation of what is happening for me. I ran this using a docker version of mariadb 10.6 on my local machine. Even though the mysql driver max and idle connection is set to 2, the database quickly becomes saturated in client threads up to the current set limit of 100. From my understanding, there should only be 2 connections created, which locally it looks like that is what is happening. (db.Stats().OpenConnections)
However, the long running connections are still open on the server.

Can anyone shed some light on if I am doing something wrong?