Locks and hints

A lock holds rows until the transaction ends, so locks belong inside a Tx.

Row locks

err := db.Tx(ctx, func(tx *AppDB) error {
	user, err := tx.Users.Where(ant.Eq(&U.ID, id)).ForUpdate().Single(ctx)
	if err != nil {
		return err
	}

	user.Age++

	return tx.SaveChanges(ctx)
})

Method	Means
ForUpdate()func (q *Query[T]) ForUpdate() *Query[T]ForUpdate locks the selected rows until the transaction ends.	Lock for writing. Others wait
ForShare()func (q *Query[T]) ForShare() *Query[T]ForShare locks the selected rows against changes until the transaction ends, while allowing other readers.	Lock against writers. Other readers are fine
NoWait()func (q *Query[T]) NoWait() *Query[T]NoWait makes a lock fail at once instead of waiting for another transaction.	Fail at once on a locked row
SkipLocked()func (q *Query[T]) SkipLocked() *Query[T]SkipLocked leaves out rows that another transaction has locked, which suits job queues.	Leave locked rows out

ForUpdate is always sent to the primary, even with replicas.

A job queue

SkipLocked lets each worker claim a row nobody else holds, so no job runs twice.

err := db.Tx(ctx, func(tx *AppDB) error {
	job, err := tx.Jobs.OrderBy(&J.ID).ForUpdate().SkipLocked().First(ctx)
	if err != nil {
		return err
	}

	job.Done = true

	return tx.SaveChanges(ctx)
})

Row locks depend on the database: see databases.

Optimizer hints

Hintfunc (q *Query[T]) Hint(hint string) *Query[T]Hint adds an optimizer hint, written /*+ hint */ before the statement, in the form extensions such as pg_hint_plan read. writes an optimizer hint at the start of a statement. PostgreSQL ignores hints unless you install the pg_hint_plan extension. A hint tends to outlive the reason for it, so prefer fixing the query.

users, err := db.Users.Query().Hint("SeqScan(users)").All(ctx)

Query comments

Tagfunc (q *Query[T]) Tag(name string) *Query[T]Tag adds a comment to the SQL, so a query can be found in database logs. adds a comment to the SQL, so a slow query is easy to spot in database logs. Neither Tag nor Hint can break out of its comment.

users, err := db.Users.Where(ant.Gt(&U.Age, 18)).Tag("dashboard: adults").All(ctx)

See it tested: tests/features/querying/locks_test.go