Memory DB with transaction lost on canceled Context due to lack of SessionResetter, Validator

[mgabeler-lee-6rs]

If a memory DB has an operation in progress in a transaction with a Context, and that context is canceled, the entire memory DB will be lost.

What?!

When a transaction is opened with a context, a background goroutine is started to roll back the transaction as soon as the context is Done(): https://github.com/golang/go/blob/release-branch.go1.16/src/database/sql/sql.go#L1786

If that rollback fires, it conditionally will close the corresponding connection based on whether the driver implements both driver.SessionResetter and driver.Validator: https://github.com/golang/go/blob/release-branch.go1.16/src/database/sql/sql.go#L1764-L1766

Since this package implements neither of those, any time a transaction against a memory connection is rolled back due to context cancellation, the entire database is lost ... unless you have other connections open and are using shared cache mode I guess. But that combination leads to endless "table is locked" errors and so is oft avoided.

My guess is that implementing these two interfaces would be pretty easy for SQLite?

[ljluestc]

package main

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"fmt"
	"sync"
	"time"

	sqlite3 "github.com/mattn/go-sqlite3"
)

// SQLiteDriverWithSessionReset wraps the mattn/go-sqlite3 driver to implement
// driver.SessionResetter and driver.Validator.
type SQLiteDriverWithSessionReset struct {
	*sqlite3.SQLiteDriver
}

// Open delegates to the underlying SQLite driver.
func (d *SQLiteDriverWithSessionReset) Open(name string) (driver.Conn, error) {
	conn, err := d.SQLiteDriver.Open(name)
	if err != nil {
		return nil, err
	}
	return &sqliteConnWithSessionReset{Conn: conn}, nil
}

// sqliteConnWithSessionReset wraps a SQLite connection to implement
// driver.SessionResetter and driver.Validator.
type sqliteConnWithSessionReset struct {
	driver.Conn
}

// ResetSession implements driver.SessionResetter to reset the connection state.
func (c *sqliteConnWithSessionReset) ResetSession(ctx context.Context) error {
	// Execute ROLLBACK to clear any active transaction
	_, err := c.Exec("ROLLBACK", nil)
	if err != nil && !isNoTransactionError(err) {
		return fmt.Errorf("rolling back transaction: %w", err)
	}

	// Reset any connection-specific settings (e.g., PRAGMA settings)
	// For simplicity, we assume default settings are sufficient.
​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​