Locks
qb includes a few methods to help you lock certain rows when executing
select statements.Note: For locks to work properly, they must be nested inside a
transaction. qb does not handle any of the transaction lifecycle for you.Name | Type | Required | Default | Description |
No arguments | | | | |
A shared lock prevents the selected rows from being modified until your transaction is committed.
QueryBuilder
query.from( "users" )
.where( "id", 1 )
.sharedLock();
MySQL
SQL Server
Postgres
Oracle
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" = ?
Name | Type | Required | Default | Description |
|---|---|---|---|---|
skipLocked | Boolean | false | false | |
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.QueryBuilder
query.from( "users" )
.where( "id", 1 )
.lockForUpdate();
MySQL
SQL Server
Postgres
Oracle
SELECT *
FROM `users`
WHERE `id` = ?
FOR UPDATE
SELECT *
FROM [users] WITH (ROWLOCK,UPDLOCK,HOLDLOCK)
WHERE [id] = ?
SELECT *
FROM "users"
WHERE "id" = ?
FOR UPDATE
SELECT *
FROM "USERS"
WHERE "ID" = ?
FOR UPDATE
When using the
skipLocked flag, the query will skip over locked records and only return and lock available records.QueryBuilder
query.from( "users" )
.where( "id", 1 )
.lockForUpdate( skipLocked = true )
.orderBy( "id" )
.limit( 5 );
MySQL
SQL Server
Postgres
SELECT *
FROM `users`
WHERE `id` = ?
ORDER BY `id`
LIMIT 5
FOR UPDATE SKIP LOCKED
SELECT TOP 5 *
FROM [users] WITH (ROWLOCK,UPDLOCK,HOLDLOCK,READPAST)
WHERE [id] = ?
ORDER BY [id]
SELECT *
FROM "users"
WHERE "id" = ?
ORDER BY "id"
LIMIT 1
FOR UPDATE SKIP LOCKED
Name | Type | Required | Default | Description |
No arguments | | | | |
noLock will instruct your grammar to ignore any shared locks when executing the query.Currently this only makes a difference in SQL Server grammars.
QueryBuilder
query.from( "users" )
.where( "id", 1 )
.noLock();
SQL Server
SELECT *
FROM [users] WITH (NOLOCK)
WHERE [id] = ?
Name | Type | Required | Default | Description |
value | string | true | | The custom lock directive to add to the query. |
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.
Name | Type | Required | Default | Description |
No arguments | | | | |
Clears any lock directive on the query.
Last modified 4mo ago