Skip to content

Commit 2c90e1c

Browse files
Nopikpleerock
Nopik
and
pleerock
authored
feat: added support for NOWAIT & SKIP LOCKED in Postgres (#5927)
* Added support for NOWAIT & SKIP LOCKED in Postgres * fix merge typo Co-authored-by: Umed Khudoiberdiev <pleerock.me@gmail.com>
1 parent 90d31a0 commit 2c90e1c

File tree

5 files changed

+55
-39
lines changed

5 files changed

+55
-39
lines changed

‎docs/find-options.md‎

Lines changed: 33 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@ userRepository.find({ relations: ["profile", "photos", "videos", "videos.video_a
2323
* `join` - joins needs to be performed for the entity. Extended version of "relations".
2424

2525
```typescript
26-
userRepository.find({
26+
userRepository.find({
2727
join: {
2828
alias: "user",
2929
leftJoinAndSelect: {
@@ -40,7 +40,7 @@ userRepository.find({
4040
```typescript
4141
userRepository.find({ where: { firstName: "Timber", lastName: "Saw" } });
4242
```
43-
Querying a column from an embedded entity should be done with respect to the hierarchy in which it was defined. Example:
43+
Querying a column from an embedded entity should be done with respect to the hierarchy in which it was defined. Example:
4444

4545
```typescript
4646
userRepository.find({ where: { name: { first: "Timber", last: "Saw" } } });
@@ -57,7 +57,7 @@ userRepository.find({
5757
});
5858
```
5959

60-
will execute following query:
60+
will execute following query:
6161

6262
```sql
6363
SELECT * FROM "user" WHERE ("firstName" = 'Timber' AND "lastName" = 'Saw') OR ("firstName" = 'Stan' AND "lastName" = 'Lee')
@@ -66,7 +66,7 @@ SELECT * FROM "user" WHERE ("firstName" = 'Timber' AND "lastName" = 'Saw') OR ("
6666
* `order` - selection order.
6767

6868
```typescript
69-
userRepository.find({
69+
userRepository.find({
7070
order: {
7171
name: "ASC",
7272
id: "DESC"
@@ -79,28 +79,28 @@ userRepository.find({
7979
* `skip` - offset (paginated) from where entities should be taken.
8080

8181
```typescript
82-
userRepository.find({
82+
userRepository.find({
8383
skip: 5
8484
});
8585
```
8686

8787
* `take` - limit (paginated) - max number of entities that should be taken.
8888

8989
```typescript
90-
userRepository.find({
90+
userRepository.find({
9191
take: 10
9292
});
9393
```
9494

9595
** If you are using typeorm with MSSQL, and want to use `take` or `limit`, you need to use order as well or you will receive the following error: `'Invalid usage of the option NEXT in the FETCH statement.'`
9696

9797
```typescript
98-
userRepository.find({
99-
order: {
100-
columnName: 'ASC'
101-
},
102-
skip: 0,
103-
take: 10
98+
userRepository.find({
99+
order: {
100+
columnName: 'ASC'
101+
},
102+
skip: 0,
103+
take: 10
104104
})
105105
```
106106

@@ -120,7 +120,7 @@ userRepository.find({
120120
```
121121
or
122122
```ts
123-
{ mode: "pessimistic_read"|"pessimistic_write"|"dirty_read" }
123+
{ mode: "pessimistic_read"|"pessimistic_write"|"dirty_read"|"pessimistic_partial_write"|"pessimistic_write_or_fail" }
124124
```
125125

126126
for example:
@@ -131,15 +131,17 @@ userRepository.findOne(1, {
131131
})
132132
```
133133

134+
`pessimistic_partial_write` and `pessimistic_write_or_fail` are supported only on Postgres and are equivalents of `SELECT .. FOR UPDATE SKIP LOCKED` and `SELECT .. FOR UPDATE NOWAIT`, accordingly.
135+
134136
Complete example of find options:
135137

136138
```typescript
137-
userRepository.find({
139+
userRepository.find({
138140
select: ["firstName", "lastName"],
139141
relations: ["profile", "photos", "videos"],
140-
where: {
141-
firstName: "Timber",
142-
lastName: "Saw"
142+
where: {
143+
firstName: "Timber",
144+
lastName: "Saw"
143145
},
144146
order: {
145147
name: "ASC",
@@ -166,7 +168,7 @@ const loadedPosts = await connection.getRepository(Post).find({
166168
})
167169
```
168170

169-
will execute following query:
171+
will execute following query:
170172

171173
```sql
172174
SELECT * FROM "post" WHERE "title" != 'About #1'
@@ -182,7 +184,7 @@ const loadedPosts = await connection.getRepository(Post).find({
182184
});
183185
```
184186

185-
will execute following query:
187+
will execute following query:
186188

187189
```sql
188190
SELECT * FROM "post" WHERE "likes" < 10
@@ -198,7 +200,7 @@ const loadedPosts = await connection.getRepository(Post).find({
198200
});
199201
```
200202

201-
will execute following query:
203+
will execute following query:
202204

203205
```sql
204206
SELECT * FROM "post" WHERE "likes" <= 10
@@ -214,7 +216,7 @@ const loadedPosts = await connection.getRepository(Post).find({
214216
});
215217
```
216218

217-
will execute following query:
219+
will execute following query:
218220

219221
```sql
220222
SELECT * FROM "post" WHERE "likes" > 10
@@ -230,7 +232,7 @@ const loadedPosts = await connection.getRepository(Post).find({
230232
});
231233
```
232234

233-
will execute following query:
235+
will execute following query:
234236

235237
```sql
236238
SELECT * FROM "post" WHERE "likes" >= 10
@@ -246,7 +248,7 @@ const loadedPosts = await connection.getRepository(Post).find({
246248
});
247249
```
248250

249-
will execute following query:
251+
will execute following query:
250252

251253
```sql
252254
SELECT * FROM "post" WHERE "title" = 'About #2'
@@ -262,7 +264,7 @@ const loadedPosts = await connection.getRepository(Post).find({
262264
});
263265
```
264266

265-
will execute following query:
267+
will execute following query:
266268

267269
```sql
268270
SELECT * FROM "post" WHERE "title" LIKE '%out #%'
@@ -278,7 +280,7 @@ const loadedPosts = await connection.getRepository(Post).find({
278280
});
279281
```
280282

281-
will execute following query:
283+
will execute following query:
282284

283285
```sql
284286
SELECT * FROM "post" WHERE "likes" BETWEEN 1 AND 10
@@ -294,7 +296,7 @@ const loadedPosts = await connection.getRepository(Post).find({
294296
});
295297
```
296298

297-
will execute following query:
299+
will execute following query:
298300

299301
```sql
300302
SELECT * FROM "post" WHERE "title" IN ('About #2','About #3')
@@ -310,7 +312,7 @@ const loadedPosts = await connection.getRepository(Post).find({
310312
});
311313
```
312314

313-
will execute following query (Postgres notation):
315+
will execute following query (Postgres notation):
314316

315317
```sql
316318
SELECT * FROM "post" WHERE "title" = ANY(['About #2','About #3'])
@@ -326,7 +328,7 @@ const loadedPosts = await connection.getRepository(Post).find({
326328
});
327329
```
328330

329-
will execute following query:
331+
will execute following query:
330332

331333
```sql
332334
SELECT * FROM "post" WHERE "title" IS NULL
@@ -342,7 +344,7 @@ const loadedPosts = await connection.getRepository(Post).find({
342344
});
343345
```
344346

345-
will execute following query:
347+
will execute following query:
346348

347349
```sql
348350
SELECT * FROM "post" WHERE "likes" = "dislikes" - 4
@@ -359,7 +361,7 @@ const loadedPosts = await connection.getRepository(Post).find({
359361
});
360362
```
361363

362-
will execute following query:
364+
will execute following query:
363365

364366
```sql
365367
SELECT * FROM "post" WHERE "currentDate" > NOW()
@@ -379,7 +381,7 @@ const loadedPosts = await connection.getRepository(Post).find({
379381
});
380382
```
381383

382-
will execute following query:
384+
will execute following query:
383385

384386
```sql
385387
SELECT * FROM "post" WHERE NOT("likes" > 10) AND NOT("title" = 'About #2')

‎src/find-options/FindOneOptions.ts‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -40,11 +40,11 @@ export interface FindOneOptions<Entity = any> {
4040
/**
4141
* Enables or disables query result caching.
4242
*/
43-
lock?: { mode: "optimistic", version: number|Date } | { mode: "pessimistic_read"|"pessimistic_write"|"dirty_read" };
43+
lock?: { mode: "optimistic", version: number|Date } | { mode: "pessimistic_read"|"pessimistic_write"|"dirty_read"|"pessimistic_partial_write"|"pessimistic_write_or_fail" };
4444

4545
/**
4646
* Indicates if soft-deleted rows should be included in entity result.
47-
*/
47+
*/
4848
withDeleted?: boolean;
4949

5050
/**

‎src/find-options/FindOptionsUtils.ts‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -176,11 +176,11 @@ export class FindOptionsUtils {
176176
if (options.lock) {
177177
if (options.lock.mode === "optimistic") {
178178
qb.setLock(options.lock.mode, options.lock.version as any);
179-
} else if (options.lock.mode === "pessimistic_read" || options.lock.mode === "pessimistic_write" || options.lock.mode === "dirty_read") {
179+
} else if (options.lock.mode === "pessimistic_read" || options.lock.mode === "pessimistic_write" || options.lock.mode === "dirty_read" || options.lock.mode === "pessimistic_partial_write" || options.lock.mode === "pessimistic_write_or_fail") {
180180
qb.setLock(options.lock.mode);
181181
}
182182
}
183-
183+
184184
if (options.withDeleted) {
185185
qb.withDeleted();
186186
}

‎src/query-builder/QueryExpressionMap.ts‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -150,7 +150,7 @@ export class QueryExpressionMap {
150150
/**
151151
* Locking mode.
152152
*/
153-
lockMode?: "optimistic"|"pessimistic_read"|"pessimistic_write"|"dirty_read"|"for_no_key_update";
153+
lockMode?: "optimistic"|"pessimistic_read"|"pessimistic_write"|"dirty_read"|"pessimistic_partial_write"|"pessimistic_write_or_fail"|"for_no_key_update";
154154

155155
/**
156156
* Current version of the entity, used for locking.

‎src/query-builder/SelectQueryBuilder.ts‎

Lines changed: 17 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -967,12 +967,12 @@ export class SelectQueryBuilder<Entity> extends QueryBuilder<Entity> implements
967967
/**
968968
* Sets locking mode.
969969
*/
970-
setLock(lockMode: "pessimistic_read"|"pessimistic_write"|"dirty_read"|"for_no_key_update"): this;
970+
setLock(lockMode: "pessimistic_read"|"pessimistic_write"|"dirty_read"|"pessimistic_partial_write"|"pessimistic_write_or_fail"|"for_no_key_update"): this;
971971

972972
/**
973973
* Sets locking mode.
974974
*/
975-
setLock(lockMode: "optimistic"|"pessimistic_read"|"pessimistic_write"|"dirty_read"|"for_no_key_update", lockVersion?: number|Date): this {
975+
setLock(lockMode: "optimistic"|"pessimistic_read"|"pessimistic_write"|"dirty_read"|"pessimistic_partial_write"|"pessimistic_write_or_fail"|"for_no_key_update", lockVersion?: number|Date): this {
976976
this.expressionMap.lockMode = lockMode;
977977
this.expressionMap.lockVersion = lockVersion;
978978
return this;
@@ -1672,6 +1672,20 @@ export class SelectQueryBuilder<Entity> extends QueryBuilder<Entity> implements
16721672
} else {
16731673
throw new LockNotSupportedOnGivenDriverError();
16741674
}
1675+
case "pessimistic_partial_write":
1676+
if (driver instanceof PostgresDriver) {
1677+
return " FOR UPDATE SKIP LOCKED";
1678+
1679+
} else {
1680+
throw new LockNotSupportedOnGivenDriverError();
1681+
}
1682+
case "pessimistic_write_or_fail":
1683+
if (driver instanceof PostgresDriver) {
1684+
return " FOR UPDATE NOWAIT";
1685+
} else {
1686+
throw new LockNotSupportedOnGivenDriverError();
1687+
}
1688+
16751689
case "for_no_key_update":
16761690
if (driver instanceof PostgresDriver) {
16771691
return " FOR NO KEY UPDATE";
@@ -1812,7 +1826,7 @@ export class SelectQueryBuilder<Entity> extends QueryBuilder<Entity> implements
18121826
if (!this.expressionMap.mainAlias)
18131827
throw new Error(`Alias is not set. Use "from" method to set an alias.`);
18141828

1815-
if ((this.expressionMap.lockMode === "pessimistic_read" || this.expressionMap.lockMode === "pessimistic_write" || this.expressionMap.lockMode === "for_no_key_update") && !queryRunner.isTransactionActive)
1829+
if ((this.expressionMap.lockMode === "pessimistic_read" || this.expressionMap.lockMode === "pessimistic_write" || this.expressionMap.lockMode === "pessimistic_partial_write" || this.expressionMap.lockMode === "pessimistic_write_or_fail" || this.expressionMap.lockMode === "for_no_key_update") && !queryRunner.isTransactionActive)
18161830
throw new PessimisticLockTransactionRequiredError();
18171831

18181832
if (this.expressionMap.lockMode === "optimistic") {

0 commit comments

Comments
 (0)