In PostgreSQL you can lock records while selecting them using FOR UPDATE. When using a LEFT JOIN you can run into an error if your selection includes NULL records for the joined table, to prevent this you should specify for which table(s) the records should be locked like so:
SELECT * FROM notes LEFT JOIN authors ON notes.author_id = authors.id FOR UPDATE OF notes;If you also want to lock the author records in the example above you would have to use a regular join and lock the records without an author in a second query.
In some cases you may only want to select records that have not been locked yet (in cases where any match to the selection, for example in a queue like table), you can use the SKIP LOCKED modifier to do this like so:
SELECT * FROM notes LEFT JOIN authors ON notes.author_id = authors.id FOR UPDATE OF notes SKIP LOCKED;You cannot use FOR UPDATE with aggregate functions such as COUNT, if you are using an ORM of some sorts you may have to retrieve your records and count them afterwards in code.
For more information take a look at the official PostgreSQL documentation for this feature.