May 2018
Intermediate to advanced
576 pages
30h 25m
English
You can also use the serializable transaction isolation level when you need to be absolutely sure that the data you are looking at is not affected by other user changes.
The default transaction isolation level in PostgreSQL is read committed, but you can choose from two more levels, repeatable read and serializable, if you require stricter control over visibility of data within a transaction; see http://www.postgresql.org/docs/current/static/transaction-iso.html.
Another design pattern available in some cases is to use a single statement for the UPDATE and return data to the user via the RETURNING clause; for example:
UPDATE accounts SET balance = balance - i_amountWHERE username = i_usernameAND balance - i_amount > - max_credit ...