Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
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
}