Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

5.6 KB
Raw Blame
package store

import (
	"context"
	"database/sql"
	"strconv"
)

// AdvisoryLockKey defines lock identifiers for distributed coordination.
// Each scheduler/component needing cluster-wide mutex gets a unique key.
type AdvisoryLockKey int64

const (
	// AdvisoryLockKeyPendingScheduler is used by the pending task run scheduler
	// to ensure only one replica promotes PENDING → AVAILABLE at a time.
	AdvisoryLockKeyPendingScheduler AdvisoryLockKey = 1001
	// AdvisoryLockKeyMigration is used by the schema migrator to ensure only
	// one replica runs database migrations at a time.
	AdvisoryLockKeyMigration AdvisoryLockKey = 1002
	// AdvisoryLockKeySchemaSyncer is used by the schema syncer to ensure only
	// one replica runs periodic schema sync at a time.
	AdvisoryLockKeySchemaSyncer AdvisoryLockKey = 1003
	// AdvisoryLockKeyVCSProviderUser is used as the namespace for active VCS
	// provider user limit checks and upserts.
	AdvisoryLockKeyVCSProviderUser AdvisoryLockKey = 1004
	// AdvisoryLockKeyPlanIssueRollout serializes Plan review changes, linked
	// Bytebase Issue creation, and Rollout creation for the same Plan.
	AdvisoryLockKeyPlanIssueRollout AdvisoryLockKey = 1005
	// AdvisoryLockKeyIamPolicy serializes compare-and-swap writes to one
	// resource's IAM policy, including the first write, when there is no policy
	// row to lock yet.
	AdvisoryLockKeyIamPolicy AdvisoryLockKey = 1008
)

// AcquirePlanIssueRolloutAdvisoryLock serializes coordinated Plan, linked Issue,
// Rollout, and Plan Check Run transactions for one Plan.
func AcquirePlanIssueRolloutAdvisoryLock(ctx context.Context, tx *sql.Tx, projectID string, planUID int64) error {
	key := projectID + "/" + strconv.FormatInt(planUID, 10)
	return AcquireAdvisoryXactLockWithStringKey(ctx, tx, AdvisoryLockKeyPlanIssueRollout, key)
}

// AdvisoryLock holds a dedicated connection for a session-level advisory lock.
type AdvisoryLock struct {
	conn *sql.Conn
	key  AdvisoryLockKey
}

// TryAdvisoryLock attempts to acquire a session-level advisory lock using a
// dedicated connection. Returns (lock, true) if acquired, (nil, false) if
// already held by another session. Caller must call lock.Release() when done.
func TryAdvisoryLock(ctx context.Context, db *sql.DB, key AdvisoryLockKey) (*AdvisoryLock, bool, error) {
	conn, err := db.Conn(ctx)
	if err != nil {
		return nil, false, err
	}

	var acquired bool
	if err := conn.QueryRowContext(ctx, "SELECT pg_try_advisory_lock($1)", int64(key)).Scan(&acquired); err != nil {
		_ = conn.Close()
		return nil, false, err
	}

	if !acquired {
		_ = conn.Close()
		return nil, false, nil
	}

	return &AdvisoryLock{conn: conn, key: key}, true, nil
}

// TryAdvisoryXactLock attempts to acquire a transaction-level advisory lock.
// Returns true if acquired, false if already held by another transaction.
// The lock is automatically released when the transaction ends.
func TryAdvisoryXactLock(ctx context.Context, tx *sql.Tx, key AdvisoryLockKey) (bool, error) {
	var acquired bool
	if err := tx.QueryRowContext(ctx, "SELECT pg_try_advisory_xact_lock($1)", int64(key)).Scan(&acquired); err != nil {
		return false, err
	}
	return acquired, nil
}

// TryAdvisoryXactLockWithStringKey attempts to acquire a transaction-level
// advisory lock scoped by namespace and string key. Returns true if acquired,
// false if already held by another transaction. The lock is automatically
// released when the transaction ends.
func TryAdvisoryXactLockWithStringKey(ctx context.Context, tx *sql.Tx, namespace AdvisoryLockKey, key string) (bool, error) {
	var acquired bool
	if err := tx.QueryRowContext(ctx, "SELECT pg_try_advisory_xact_lock($1, hashtext($2))", int32(namespace), key).Scan(&acquired); err != nil {
		return false, err
	}
	return acquired, nil
}

// AcquireAdvisoryXactLock acquires a transaction-level advisory lock, blocking
// until the lock is available. The lock is automatically released when the
// transaction ends.
func AcquireAdvisoryXactLock(ctx context.Context, tx *sql.Tx, key AdvisoryLockKey) error {
	_, err := tx.ExecContext(ctx, "SELECT pg_advisory_xact_lock($1)", int64(key))
	return err
}

// AcquireAdvisoryXactLockWithStringKey acquires a transaction-level advisory
// lock scoped by namespace and string key, blocking until the lock is available.
// The lock is automatically released when the transaction ends.
func AcquireAdvisoryXactLockWithStringKey(ctx context.Context, tx *sql.Tx, namespace AdvisoryLockKey, key string) error {
	_, err := tx.ExecContext(ctx, "SELECT pg_advisory_xact_lock($1, hashtext($2))", int32(namespace), key)
	return err
}

// Release releases the advisory lock and returns the connection to the pool.
// Uses context.Background() to ensure cleanup completes even if parent ctx is cancelled.
func (l *AdvisoryLock) Release() error {
	if l.conn == nil {
		return nil
	}
	// Unlock then close; closing also releases but explicit unlock is cleaner
	_, _ = l.conn.ExecContext(context.Background(), "SELECT pg_advisory_unlock($1)", int64(l.key))
	return l.conn.Close()
}

// AcquireAdvisoryLock acquires a session-level advisory lock, blocking until
// the lock is available. Caller must call lock.Release() when done.
// This is useful for migrations where we want to wait rather than fail fast.
func AcquireAdvisoryLock(ctx context.Context, db *sql.DB, key AdvisoryLockKey) (*AdvisoryLock, error) {
	conn, err := db.Conn(ctx)
	if err != nil {
		return nil, err
	}

	// pg_advisory_lock blocks until the lock is acquired
	if _, err := conn.ExecContext(ctx, "SELECT pg_advisory_lock($1)", int64(key)); err != nil {
		_ = conn.Close()
		return nil, err
	}

	return &AdvisoryLock{conn: conn, key: key}, nil
}