mysqllock

mysqllock provides process-distributed mutual exclusion using MySQL's named lock primitives GET_LOCK and RELEASE_LOCK.

Table of contents

Part of nurago, a collection of independent Go packages for backend services.

import "github.com/tecnickcom/nurago/pkg/mysqllock"

Package mysqllock provides process-distributed mutual exclusion using MySQL’s named lock primitives GET_LOCK and RELEASE_LOCK.

MySQLLock.Acquire requests a lock by key and returns a ReleaseFunc that must be called to release it.

Usage:

db, err := sql.Open("mysql", dsn)
if err != nil {
	log.Fatal(err)
}
defer db.Close()

locker := mysqllock.New(db)
release, err := locker.Acquire(ctx, "daily-reconciliation", 10*time.Second)
if err != nil {
	if errors.Is(err, mysqllock.ErrTimeout) {
		// Another instance is holding the lock.
		return
	}

	log.Fatal(err)
}
defer func() {
	if err := release(); err != nil {
		log.Printf("failed to release lock: %v", err)
	}
}()

// Perform the critical section while lock is held.

Detecting lock loss

A named lock lives only for as long as its owning MySQL session. If that session is dropped (idle-timeout reaping by MySQL or an intermediary proxy, network failure, server restart), MySQL releases the lock even though the caller still holds a ReleaseFunc. To keep the session active a periodic keep-alive query is run while the lock is held; if that query fails the lock is presumed lost. Each keep-alive attempt is bounded by a per-attempt timeout (WithKeepAlivePingTimeout, default 10s), so a connection that hangs (rather than resets) is also detected as lock loss instead of stalling silently.

Pass WithLostLockHandler to MySQLLock.Acquire to be notified (with an error wrapping ErrLockLost) the moment a specific lock is lost, so the critical section can be aborted:

release, err := locker.Acquire(ctx, key, timeout,
	mysqllock.WithLostLockHandler(func(err error) {
		cancelCriticalSection() // stop work; the lock is no longer held
	}))

Features

  • Single-call acquisition API: MySQLLock.Acquire returns a release closure, so lock lifetime can be scoped with defer. The returned closure is idempotent and safe to call more than once, including concurrently.
  • Explicit timeout handling: ErrTimeout is returned when GET_LOCK does not acquire the lock within the requested timeout.
  • Input validation: empty or over-long keys yield ErrInvalidKey and non-positive timeouts yield ErrInvalidTimeout, instead of surfacing an opaque server error.
  • Dedicated lock connection: each successful lock acquisition is tied to a dedicated SQL connection, matching MySQL’s lock semantics.
  • Connection keep-alive: a periodic query keeps the lock-owning connection active for long-running critical sections; the interval is configurable with WithKeepAliveInterval and each attempt is bounded by WithKeepAlivePingTimeout.
  • Lock-loss notification: a keep-alive failure is reported through the per-acquisition WithLostLockHandler and the instance-wide WithKeepAliveErrorHandler, both receiving an error wrapping ErrLockLost. Handler panics are recovered so a faulty handler cannot crash the process.
  • Bounded release: releasing the lock uses its own timeout (configurable with WithReleaseTimeout) so a wedged connection cannot block the caller forever.
  • Context-aware acquisition: caller context controls acquisition cancellation.
  • Zero external dependencies at runtime: relies only on database/sql and MySQL lock functions.

Behavior Notes

The lock key namespace is per MySQL server instance. Use stable, descriptive keys (for example, “service:job:daily-reconciliation”). Always call the returned ReleaseFunc, ideally with defer, to avoid holding locks longer than intended. MySQL limits lock names to 64 characters; keys outside 1..64 characters are rejected with ErrInvalidKey.

If the returned ReleaseFunc is never called, the keep-alive goroutine and its connection stay alive (and the lock stays held) until the process exits; there is no finalizer backstop, so releasing is the caller’s responsibility.

The GET_LOCK timeout is passed to MySQL with sub-second (fractional) precision; MySQL 5.7.5 and later accept fractional timeouts (older servers truncate to whole seconds). The timeout must be positive; a non-positive value is rejected with ErrInvalidTimeout (MySQL would otherwise treat a negative timeout as an effectively unbounded wait). This lock timeout is distinct from the caller context: if ctx is canceled or its deadline is shorter than timeout, acquisition fails with a wrapped context error rather than ErrTimeout, so callers that distinguish “held by another instance” from “my own deadline” should also test for context errors.

Because a released connection is returned to the pool rather than closed, the explicit RELEASE_LOCK is required to free the named lock. In the rare case where RELEASE_LOCK fails on an otherwise-healthy connection, that connection can return to the pool still holding the lock; the same is true if the caller context is canceled at the instant GET_LOCK grants the lock (the acquire path issues a time-bounded best-effort RELEASE_LOCK to mitigate this without delaying the canceled acquisition, but cannot guarantee it if the connection is already unusable). Releasing also relies on the driver leaving the connection usable after canceling an in-flight keep-alive query. Configuring db.SetConnMaxLifetime bounds how long any such leaked lock can persist.

When To Use

  • A scheduled job runs on every replica but must execute only once.
  • You already depend on MySQL and do not want to add a coordination service.
  • The lock must be released automatically if the holder’s connection dies.

Example

// A real program passes the *sql.DB of a MySQL connection. The mock
// stands in for the GET_LOCK and RELEASE_LOCK round trips.
db, mock, err := sqlmock.New()
if err != nil {
	fmt.Println(err)

	return
}

defer func() { _ = db.Close() }()

// GET_LOCK returns 1 when the lock is granted.
mock.ExpectQuery("SELECT COALESCE\\(GET_LOCK").
	WillReturnRows(sqlmock.NewRows([]string{"result"}).AddRow(1))
mock.ExpectQuery("SELECT COALESCE\\(RELEASE_LOCK").
	WillReturnRows(sqlmock.NewRows([]string{"result"}).AddRow(1))

lock := mysqllock.New(db)

// Acquire blocks until the lock is granted or timeout elapses. The
// returned release function is idempotent.
release, err := lock.Acquire(context.TODO(), "nightly-report", 5*time.Second)
if err != nil {
	fmt.Println("acquire:", err)

	return
}

// Only one process across the fleet reaches this point for a given key.
fmt.Println("running exclusive work")

fmt.Println("release:", release())

// Output:
// running exclusive work
// release: <nil>

Full source is in example_mysqllock_test.go. More runnable examples are on pkg.go.dev.

Dependencies

This package reaches no external module: it uses only the Go standard library.