A Distributed Lock in Go and MySQL, and Every Way the Session Can Betray You

How the nurago mysqllock package builds a distributed lock on MySQL GET_LOCK: a pinned connection, a bounded keep-alive, deadlock-free release, and a precise account of what it cannot promise.

- Nicola Asuni

Table of contents

Some work must happen at most once across an entire fleet: a nightly reconciliation, an idempotent migration, a cache rebuild. When several instances of a service could each try to run it, you need a lock that spans processes, and an in-memory mutex cannot span processes. One tempting answer, if you already depend on MySQL, is its built-in named locks: GET_LOCK('key', timeout) grants a server-wide lock, RELEASE_LOCK('key') frees it, and you have avoided standing up a separate coordination service. The mysqllock package in nurago is under six hundred lines built on exactly that primitive. This post is about the gap between “call GET_LOCK” and “a lock I can reason about”, because a MySQL named lock has one property that quietly invalidates the naive version: it lives and dies with the database session that holds it. What follows is a catalogue of the ways that session betrays you, and what the package does about each one.

The shape of it

locker := mysqllock.New(db) // db is an existing *sql.DB

release, err := locker.Acquire(ctx, "daily-reconciliation", 10*time.Second,
    mysqllock.WithLostLockHandler(func(err error) {
        cancelWork() // the lock is gone; stop the critical section
    }))
if err != nil {
    if errors.Is(err, mysqllock.ErrTimeout) {
        return nil // another instance holds it
    }

    return err
}

defer func() { _ = release() }()

Acquire validates its input first (ErrInvalidKey for keys outside MySQL’s 1..64 character limit, ErrInvalidTimeout for non-positive timeouts) and returns a ReleaseFunc you scope with defer. ErrTimeout cleanly signals “someone else has it”, distinct from a real failure and from your own context expiring, which surfaces as a wrapped context error instead. That is the whole API. Everything else is defending it against the ways a session, a connection, and a pool conspire to lose your lock without telling you.

Betrayal one: the lock dies with its session

MySQL guarantees that at most one session holds a named lock at a time. It says nothing about your process. The session can be dropped by MySQL’s wait_timeout reaper, an intermediary proxy’s idle timeout, a network partition, or a server restart, and the instant it dies MySQL releases the lock and will happily grant it to another instance. Your code still holds a ReleaseFunc and still believes it owns the lock; the critical section keeps running with no protection at all.

The package cannot prevent this, so it does two things: it works to keep the session alive, and it tells you when it fails. A keep-alive goroutine runs a trivial query on the lock-owning connection at a configurable interval (WithKeepAliveInterval, default 30 seconds), and any failure is treated as “the lock is presumed lost”. The failure is reported through the per-acquisition WithLostLockHandler and the instance-wide WithKeepAliveErrorHandler, both receiving an error wrapping ErrLockLost and naming the key, so the critical section can be aborted the moment the loss is detected rather than at the end when it is too late. A panic in either handler is recovered, so a faulty handler cannot take down the keep-alive goroutine or the process.

Betrayal two: the pool shuffles sessions under you

database/sql hands out connections from a pool. Run GET_LOCK through the pool directly and the very next statement, including your eventual RELEASE_LOCK, may execute on a different connection, a different session, which does not hold the lock. Meanwhile the connection that does hold it drifts back into the pool, carrying the lock with it into unrelated queries.

So the first thing mysqllock does is check a dedicated connection out of the pool and run GET_LOCK on it:

conn, err := l.getLockedConn(ctx, key, timeout)

That *sql.Conn is reserved for the lock’s lifetime; the keep-alive pings it, and release uses it. Release is also why closing the connection is not enough: closing an *sql.Conn returns it to the pool rather than ending the MySQL session, so the named lock would stay held. mysqllock therefore issues an explicit RELEASE_LOCK first, bounded by its own timeout (WithReleaseTimeout, default 10 seconds), and treats any result other than “released by this session” as evidence the lock had already been lost, surfacing it as ErrLockLost. Only then is the connection closed and returned.

Betrayal three: a connection can hang while looking open

A connection that resets returns an error promptly, and the keep-alive catches it on the next tick. A connection that hangs, wedged behind a stalled proxy or a black-holed network path, returns nothing at all. A keep-alive that simply waits for its query to come back would stall forever, never failing, never raising the alarm, while the session times out server-side and the lock passes to someone else. So each keep-alive attempt is bounded by its own per-attempt timeout (WithKeepAlivePingTimeout, default 10 seconds):

func pingConnection(ctx context.Context, conn *sql.Conn, timeout time.Duration) error {
	pingCtx, cancel := context.WithTimeout(ctx, timeout)
	defer cancel()

	_, err := conn.ExecContext(pingCtx, keepAliveSQLQuery)
	if err != nil {
		return fmt.Errorf("unable to keep mysql connection alive: %w", err)
	}

	return nil
}

The query behind keepAliveSQLQuery is DO 1, which evaluates an expression without producing a result set, so there are no rows to drain or close. With the bounded context, a hung connection surfaces as a timeout error, exactly like a reset one, instead of silently disabling the health check. The same idea protects the exit path: release runs RELEASE_LOCK under WithReleaseTimeout, so a wedged connection cannot block the caller indefinitely either.

Betrayal four: release races the lost-lock handler

This one only appears once you wire up lost-lock notification. The natural handler is “if I lose the lock, stop the work and release”. That handler calls release(). But release() must first stop the keep-alive goroutine and wait for it to let go of the pinned connection, otherwise the keep-alive query and RELEASE_LOCK could run on the same *sql.Conn concurrently, a concurrent-use violation in database/sql. The goroutine signals “I am done with the connection” by closing a done channel. And the lost-lock handler is invoked by that same goroutine. Notify first, close done after, and a handler calling release() blocks on a done that cannot close until the handler returns. Classic self-deadlock.

mysqllock orders the endgame precisely (quoted from keepConnectionAlive):

// A failure not caused by ctx cancellation (the release path) is a lost lock.
// Decide the verdict before closing done so a racing release() cannot flip it.
lost := kerr != nil && ctx.Err() == nil

// Close done (signaling that conn is no longer in use) BEFORE notifying: a
// lost-lock handler may call the release function, which uses conn and waits
// on done. Closing first lets it proceed instead of self-deadlocking, and is
// safe because no further keep-alive queries run once a ping has failed.
close(done)

if lost {
	notify(kerr)
}

The verdict is recorded first, so a release() racing in and cancelling the context cannot retroactively turn a genuine loss into a silent shutdown. Then done closes, declaring the connection free. Only then does the handler run, so its release() proceeds immediately. The release itself is guarded by a sync.Once: the first caller performs the teardown and every later or concurrent caller, handler and defer alike, gets the first caller’s result. Both properties are pinned by tests, including one that calls release() from inside the handler and fails the suite if it deadlocks.

Betrayal five: cancellation lands at the moment of grant

The caller’s context can be cancelled while GET_LOCK is in flight. The driver reports a context error, Acquire fails, and everyone moves on, except that the server may have granted the lock a moment before the cancellation reached it. Now a pooled connection holds a named lock that no code path knows about, blocking every future acquirer of that key.

When the GET_LOCK scan fails with a context error, the acquire path runs a best-effort RELEASE_LOCK on that same connection before closing it, on a fresh context bounded to at most two seconds (the release timeout, capped), so a cancelled acquisition returns promptly instead of stalling on cleanup. The outcome is deliberately ignored: the lock may never have been granted, and if the connection is already unusable the release cannot succeed. This is mitigation, not a guarantee. The backstop the package documentation recommends is db.SetConnMaxLifetime, which bounds how long any leaked lock can survive on a pooled connection.

Whether to use it at all

Everything above is about using a MySQL named lock dependably; whether to reach for one in the first place is a question of its own. The case for it rests on a fairly specific set of circumstances: MySQL is already part of the stack, every contender reaches the same server, contention is occasional rather than constant (scheduled jobs, migrations, cache rebuilds), and the protected work is idempotent or fenced, so that a rare overlap during the detection window wastes some effort instead of corrupting data. Within those bounds the appeal is real: one fewer piece of infrastructure to deploy, secure, and monitor, in exchange for the failure modes this post has been cataloguing.

Outside those bounds, other tools are likely to serve better. When overlap would damage data rather than waste work, a coordination service built on consensus, such as etcd, ZooKeeper, or Consul, offers sessions, leases, and fencing tokens: a number that increases with every grant, which the protected resource can check in order to reject writes from a stale holder. On Kubernetes, the built-in Lease objects provide leader election with no new dependency. And sometimes the strongest answer is no lock at all: a unique constraint, an idempotency key, or an insert into a job table makes “at most once” a property the database enforces transactionally, with no session left to betray anyone.

One caution against overcorrecting, though. Moving to a dedicated lock service does not by itself remove the overlap window: a holder that is paused, partitioned, or merely slow can still act after its lease has expired, whatever granted the lock, as Martin Kleppmann’s analysis of distributed locking sets out. The dependable defence is fencing on the resource side. A different lock changes how narrow the window is and how promptly you learn it has opened, not whether it exists. mysqllock sits at the modest end of that spectrum, and is most at home guarding work that could, in the worst case, survive running twice.

The corners that remain

A lock this exposed cannot promise perfection, and the package does not pretend to. What stays on your side of the line:

  • Calling release. If the 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; releasing is deliberately the caller’s responsibility.
  • The detection window. Between the session actually dying and the handler firing, up to a full keep-alive interval plus a ping timeout can pass (40 seconds at the defaults), and MySQL may grant the lock to another instance the moment the old session drops. During that window two critical sections can overlap. Tuning the interval narrows the window; nothing closes it. If overlap is unacceptable, the work itself must be idempotent or fenced, for example with a version check on every write.
  • Stopping the work. The lost-lock handler is a notification, not a brake. Until your handler cancels the critical section, it keeps running unprotected.
  • One server. The lock namespace is per MySQL server instance. Replicas and separate primaries do not share it; every contender must go through the same server.
  • Residual leaks. If RELEASE_LOCK itself fails on an otherwise-healthy connection, the lock can return to the pool still held; db.SetConnMaxLifetime bounds how long that lasts.

That precision is the point. A distributed lock built on a shared database is a genuinely useful tool, but only if you know which failure modes it closes by construction, which it merely narrows, and which it hands back to you. mysqllock earns trust not by claiming the session can never betray you, but by naming every way it can and handling each one deliberately.