Support NOWAIT option in SELECT FOR UPDATE queries

[kresho]

Not all databases support this so it would be a dialect specific option. Postgres and Oracle do support it directly.

Mssql has a tablehint which could be covered with the standardised NOWAIT option.

---

Indicate your interest in the addition of this feature by adding the 👍 reaction. Comments such as "+1" will be removed.

[nitish166]

As @kresho suggested that not all databases support this queries. I just want to add here details about "NOWAIT" options.

NOWAIT: To avoid waiting for other transactions to release row locks, maybe we can use NOWAIT with SELECT ....... FOR UPDATE or SELECT ........ FOR SHARE locking read statement.

A locking read that uses NOWAIT never waits to acquire a row lock. The query executes immediately, failing with an error if a requested row is locked.

[github-actions]

This issue has been automatically marked as stale because it has been open for 7 days without activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment or remove the "stale" label. 🙂

[abel-matsec]

Just poking on this thread that we have a use case where this functionality would be very useful.

[abel-matsec]

This seems reasonably straightforward to support. There are already examples of postgres only lockmodes in sequelize today:

(i may poke at a diff to see how hard this actually is...)

[abel-matsec]

Seems like there are two ways to go about this:

1. Add new lock modes with NOWAIT appended.
2. Expose a new "lock option" that is a separate param specified on a query. In SELECT statements these are technically two separate things, so it may may sense to separate them in the sequelize query options.

For what it's worth, NO WAIT and SKIP LOCKED are also available in mysql 8.0, so this is no longer just a postgres thing.

[abel-matsec]

Ah, and just discovered in master that skipLocked is already exposed. I'll get a diff that uses a similar pattern for nowait.

[abel-matsec]

Ok, PR submitted. If there is support for it, I'll go ahead and get the website docs PR together. But will hold off on that until I confirm it's worth the time.