Locks

qb includes a few methods to help you lock certain rows when executing select statements.

sharedLock

A shared lock prevents the selected rows from being modified until your transaction is committed.

query.from( "users" )
    .where( "id", 1 )
    .sharedLock();

SELECT *
FROM `users`
WHERE `id` = ?
LOCK IN SHARE MODE

SELECT *
FROM [users] WITH (ROWLOCK,HOLDLOCK)
WHERE [id] = ?

SELECT *
FROM "users"
WHERE "id" = ?
FOR SHARE

LOCK TABLE "USERS"
IN SHARE MODE NOWAIT;

SELECT *
FROM "USERS"
WHERE "ID" = ?

lockForUpdate

A lock for update lock prevents the selected rows from being modified or selected with another shared lock until your transaction is committed.

The main difference between a sharedLock and lockForUpdate is that a lockForUpdate prevents other reads or selects as well as updates.

query.from( "users" )
    .where( "id", 1 )
    .lockForUpdate();

SELECT *
FROM `users`
WHERE `id` = ?
FOR UPDATE

SELECT *
FROM [users] WITH (ROWLOCK,UPDLOCK,HOLDLOCK)
WHERE [id] = ?

SELECT *
FROM "users"
WHERE "id" = ?
FOR UPDATE

LOCK TABLE "USERS"
IN ROW EXCLUSIVE MODE NOWAIT;

SELECT *
FROM "USERS"
WHERE "ID" = ?

noLock

noLock will instruct your grammar to ignore any shared locks when executing the query.

Currently this only makes a difference in SQL Server grammars.

query.from( "users" )
    .where( "id", 1 )
    .noLock();

SELECT *
FROM [users] WITH (NOLOCK)
WHERE [id] = ?

lock

The lock method will allow you to add a custom lock directive to your query. Think of it as the raw method for lock directives.

These lock directives vary from grammar to grammar.

clearLock

Clears any lock directive on the query.