# Row locks

> Control concurrent row access with FOR UPDATE, FOR SHARE, skipLocked, noWait, table targeting, and transaction-only enforcement.

URL: https://better-drizzle.com/docs/advanced/locks

Applies to: postgresql, mysql

Use a row lock when a read decides a write that must not race another transaction: claiming a job, reserving stock, moving money. The `lock` option on every read helper wraps Drizzle's `FOR UPDATE`, `FOR SHARE`, and related clauses.

Locks are supported on **PostgreSQL** and **MySQL**. SQLite has no row-level locking and throws `LOCK_NOT_SUPPORTED`. For complete patterns (job queues, cursor pages, includes, lock errors), see [locking patterns](/docs/advanced/lock-patterns).

## Lock rows in a read

The simplest form is a string shorthand:



**better-drizzle**

```ts  
const users = await client.users.findMany({
	where: { active: true },
	lock: 'update',
});
```

**Drizzle**

```ts  
import { eq } from 'drizzle-orm';

const activeUsers = await db
	.select()
	.from(users)
	.where(eq(users.active, true))
	.for('update');
```

This generates `SELECT ... FOR UPDATE`.

The object form gives access to every option:



**better-drizzle**

```ts  
const users = await client.users.findMany({
	where: { active: true },
	lock: {
		mode: 'update',
		skipLocked: true,
	},
});
```

**Drizzle**

```ts  
import { eq } from 'drizzle-orm';

const activeUsers = await db
	.select()
	.from(users)
	.where(eq(users.active, true))
	.for('update', { skipLocked: true });
```

## `skipLocked` and `noWait`

These control what happens when a locked row is already held by another transaction:

* **`skipLocked: true`** - skip rows that are locked, return only unlocked rows
* **`noWait: true`** - fail immediately if any requested row is locked

They are **mutually exclusive**.
Enabling both throws an error.



**better-drizzle**

```ts  
// Skip locked rows
const available = await client.jobs.findMany({
	where: { status: 'pending' },
	lock: {
		mode: 'update',
		skipLocked: true,
	},
});
```

**Drizzle**

```ts  
import { eq } from 'drizzle-orm';

// Skip locked rows
const available = await db
	.select()
	.from(jobs)
	.where(eq(jobs.status, 'pending'))
	.for('update', { skipLocked: true });
```

With `noWait`, the query fails right away instead of waiting for the lock:



**better-drizzle**

```ts  
// Fail fast if locked
try {
	const row = await client.jobs.findFirst({
		where: { id: 1 },
		lock: {
			mode: 'update',
			noWait: true,
		},
	});
} catch (error) {
	// LOCK_TIMEOUT - the row was locked by another transaction
}
```

**Drizzle**

```ts  
import { eq } from 'drizzle-orm';

// Fail fast if locked
try {
	const [row] = await db
		.select()
		.from(jobs)
		.where(eq(jobs.id, 1))
		.limit(1)
		.for('update', { noWait: true });
} catch (error) {
	// PostgreSQL 55P03 / MySQL 3572 - the row was locked by another transaction
}
```

**PostgreSQL only:**

## Lock specific tables (PostgreSQL only)

On PostgreSQL you can scope the lock to specific tables using `tables`.
This is useful in joins where you only want to lock certain tables:



**better-drizzle**

```ts  
const posts = await client.posts.findMany({
	where: { published: true, author: { is: { active: true } } },
	include: { author: true },
	lock: {
		mode: 'update',
		tables: ['posts'],
	},
});
```

**Drizzle**

```ts  
import { and, eq, getColumns } from 'drizzle-orm';

const publishedPosts = await db
	.select({ ...getColumns(posts), author: getColumns(users) })
	.from(posts)
	.innerJoin(users, eq(posts.authorId, users.id))
	.where(and(eq(posts.published, true), eq(users.active, true)))
	.for('update', { of: posts });
```

`tables` accepts both TypeScript table keys and database table names.
Duplicate table references (by database name) are deduplicated automatically.

> **PostgreSQL only:** `tables` is only supported on PostgreSQL. Using it on MySQL or SQLite throws
> `LOCK_NOT_SUPPORTED`.

## Enforcing transaction-only locks

Outside a transaction, the lock is released as soon as the statement finishes, so it protects nothing.
You can enforce that locks only run inside explicit transactions:

```ts
const client = better(db, {
	locks: {
		transactionsOnly: true,
	},
});
```

With this config, any `lock` outside `client.transaction(...)` throws `LOCK_REQUIRES_TRANSACTION`:

```ts
// This throws
const users = await client.users.findMany({
	lock: 'update',
});

// This works
await client.transaction(async (tx) => {
	return tx.users.findMany({
		lock: 'update',
	});
});
```

## Reference

### Lock modes

| Mode            | SQL (PostgreSQL)    | SQL (MySQL)   |
| --------------- | ------------------- | ------------- |
| `'update'`      | `FOR UPDATE`        | `FOR UPDATE`  |
| `'share'`       | `FOR SHARE`         | `FOR SHARE`   |
| `'noKeyUpdate'` | `FOR NO KEY UPDATE` | not supported |
| `'keyShare'`    | `FOR KEY SHARE`     | not supported |

**PostgreSQL only:**

`noKeyUpdate` and `keyShare` are PostgreSQL-specific.
Using them on MySQL throws `LOCK_NOT_SUPPORTED`.

### Methods that accept `lock`

Locks work on every read helper that accepts `QueryArgs`:

| Method               | Accepts `lock` |
| -------------------- | -------------- |
| `findMany`           | Yes            |
| `findFirst`          | Yes            |
| `findOne`            | Yes            |
| `findUnique`         | Yes            |
| `paginate`           | Yes            |
| `cursor`             | Yes            |
| `count`              | No             |
| `exists`             | No             |
| all write operations | No             |

### Dialect support

| Feature               | PostgreSQL | MySQL    | SQLite   |
| --------------------- | ---------- | -------- | -------- |
| `lock: 'update'`      | Yes        | Yes      | rejected |
| `lock: 'share'`       | Yes        | Yes      | rejected |
| `lock: 'noKeyUpdate'` | Yes        | rejected | rejected |
| `lock: 'keyShare'`    | Yes        | rejected | rejected |
| `skipLocked`          | Yes        | Yes      | N/A      |
| `noWait`              | Yes        | Yes      | N/A      |
| `tables`              | Yes        | rejected | N/A      |
| `transactionsOnly`    | Yes        | Yes      | N/A      |

- [Transactions](/docs/advanced/transactions)

- [Query options](/docs/reference/query-options)

- [Support matrix](/docs/reference/support-matrix)

- [Error handling](/docs/advanced/error-handling)