example for issue 18081

examplepool.go

package examplepool

import (
	"context"
	"errors"
	"fmt"
	"sync"
	"time"

	_ "github.com/alexbrainman/odbc"
	"github.com/jmoiron/sqlx"
)

type Examplepool struct {
	username string
	password string

	connsMutex sync.Mutex
	conns      []*connection
}

type connection struct {
	*sqlx.DB
	inUse bool
	ep    *Examplepool
}

var (
	ErrNotInUse  = errors.New("Connection is not in use, can't release")
	ErrNotInPool = errors.New("Connection to release not actually in pool")
)

func New(username, password string) (*Examplepool, error) {
	ep := &Examplepool{
		username: username,
		password: password,
		conns:    make([]*connection, 0),
	}

	// Have at least one connection ready
	c, err := ep.Acquire()
	if err != nil {
		return nil, err
	}
	err = c.Release()
	if err != nil {
		return nil, err
	}
	return ep, nil
}

func (ep *Examplepool) newConnection() (*connection, error) {
	db, err := sqlx.Open("odbc", fmt.Sprintf("dsn=mydsn;uid=%s;pwd=%s;tracing=0", ep.username, ep.password))
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)

	return &connection{
		db,
		false,
		ep,
	}, nil
}

func (ep *Examplepool) Acquire() (*connection, error) {
	ep.connsMutex.Lock()
	defer ep.connsMutex.Unlock()

	for i, c := range ep.conns {
		if !c.inUse {
			// Log.Debugf("Acquire() returning id %d", i)
			ep.conns[i].inUse = true
			return c, nil
		}
	}

	c, err := ep.newConnection()
	if err != nil {
		return nil, err
	}

	// Log.Debugf("Acquire() returning new connection, index: %d", len(ep.conns))
	c.inUse = true
	ep.conns = append(ep.conns, c)
	return c, nil
}

func (c *connection) Release() error {
	c.ep.connsMutex.Lock()
	defer c.ep.connsMutex.Unlock()

	for i, co := range c.ep.conns {
		if co == c {
			// Log.Debugf("Release() found connection, index: %d", i)
			if !co.inUse {
				// Log.Error(ErrNotInUse.Error())
				return ErrNotInUse
			}
			c.ep.conns[i].inUse = false
			return nil
		}
	}

	// Log.Error(ErrNotInPool.Error())
	return ErrNotInPool
}

func (ep *Examplepool) StartKeepAlive(ctx context.Context) {
	go func() {
		select {
		case <-time.After(15 * time.Minute):
			ep.connsMutex.Lock()
			defer ep.connsMutex.Unlock()

			for _, c := range ep.conns {
				if !c.inUse {
					// Log.Debugf("KeepAlive() for index: %d", i)
					c.KeepAlive()
				} else {
					// Log.Debugf("No KeepAlive() for index: %d (in use)", i)
				}
			}

		case <-ctx.Done():
			return
		}
	}()
}

func (c *connection) KeepAlive() error {
	rows, err := c.Query(`SELECT CURRENT_TIMESTAMP()`)
	if err != nil {
		return err
	}
	rows.Close()
	return nil
}