Re: SQL Puzzle - too many dimensions
On 8 Mai, 10:43, Lawrence D'Oliveiro <l...@geek-
central.gen.new_zealand> wrote:
In message <7becs6h10lufrqvb8ciii7vpafjqldv...@4ax.com>, Roedy Green wrote:
You need to combine these into one query, otherwise the max could
change just prior to the second query.
Why does that matter?
Because consistency is not guaranteed any more. If the row with the
max is removed after the first query, the second query will lack a row
for that song_id.
More formally, because TX isolation level "read committed" allows for
non repeatable reads to occur.
If you want to know more please read up on "statement level read
consistency". The whole chapter is very informative.
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/consist.htm#CNCPT88960
http://www.postgresql.org/docs/8.4/interactive/transaction-iso.html
Also it's usually faster to do it in one query.
Cheers
robert
PS: I advise anybody doing development of applications which
persistently store data in _any_ way (yes, this includes infamous
"NoSQL" storages) to make themselves familiar with transaction
handling. Doing otherwise is grossly negligent and can (and will)
cause all sorts of issues from performance degradation to data
inconsistency (which is probably worse than data loss). Actually, the
concept of "transaction" is not limited to persistent storage but also
to how an application deals with its state in memory.