I understand that this is an example so I try to build some context around those queries.
The issue here is that you (apparently) want to guarantee that whoever sees the result of the select (probably a customer) can safely buy the last available book with the update.
This can't be solved in a simple way because minutes can pass between that select and that update. This means that you can't put those queries in a transaction. If you could you should use select for update
I used code like update table set counter = counter + 1 / - 1 many times without transactions when all I have to do is to update a counter and nothing else in the system really cares about the value of the counter. If this is not the case, the queries accessing the counter should use some form of locking. A transaction is not enough.
The issue here is that you (apparently) want to guarantee that whoever sees the result of the select (probably a customer) can safely buy the last available book with the update.
This can't be solved in a simple way because minutes can pass between that select and that update. This means that you can't put those queries in a transaction. If you could you should use select for update
https://www.postgresql.org/docs/current/sql-select.html
I used code like update table set counter = counter + 1 / - 1 many times without transactions when all I have to do is to update a counter and nothing else in the system really cares about the value of the counter. If this is not the case, the queries accessing the counter should use some form of locking. A transaction is not enough.
This is the first good guide I found googling for examples https://vladmihalcea.com/a-beginners-guide-to-database-locki...