Thanks for your helpful explanations. Reading subsection 13.2.1 makes
sense now. I am not sure if and how the documentation could be improved,
but I propose below some suggestions that you may find relevant. My
confusion may have stem from my lack of knowledge in PostgreSQL locking
mechanism.
Le 29/10/2012 22:42, Kevin Grittner a écrit :
louis-claude.ca...@univ-fcomte.fr wrote:
In the first paragraph of Section 13.2.1, it is first stated that
"[with] this isolation level, a SELECT query [...] never sees [...]
changes committed during query execution by concurrent
transactions." It is my understanding that this is untrue (as the
rest of the paragraph seems to imply).
What are you seeing which you feels contradicts that? (I don't see
anything which appears to contradict it, but maybe there's more than
one way for someone to read it, and I'm missing something.)
My mistake, I interpreted "during query execution" as "during the
current transaction execution". It could be more precise to replace "it
never sees either uncommitted data or changes committed during query
execution by concurrent transactions" by one of the following :
- "it never sees either uncommitted data or changes committed during
_this_ query execution by concurrent transactions"
- "_this query_ never sees either uncommitted data or changes committed
during _its_ execution by concurrent transactions"
Then, in the same subsection, an example is given in which a DELETE
statement is affected by an uncommitted UPDATE.
No, the DELETE statement is blocked by the UPDATE to see whether it
commits or rolls back. If the transaction with the UPDATE rolls back,
the results of the DELETE will not be affected by the UPDATE. If the
UPDATE commits, the committed transaction will affect the DELETE.
It is unclear to me if this still corresponds to the standard
"Committed Read" isolation level.
Yes, it's still in section 13.2.1, which is all about READ COMMITTED
transactions. And I believe that it still complies with the
requirements of the standard for that level.
It could be clearer with more precision: when is committed the
DELETE statement?
I guess it could be more clear that the DELETE will block pending the
completion of the transaction containing the previously run UPDATE.
The commit of the DELETE will be some time after the commit of the
transaction containing the UPDATE.
What is the snapshot that it sees (when does the transaction
start)? Or maybe this standard isolation level only specified that
SELECT statements must not be affected by uncommitted changes?
No transaction is affected (other than blocking or possibly rolling
back with a serialization failure), by an *uncommitted* transaction.
In this example the DELETE is affected by a *committed* transaction.
The snapshot for the DELETE is taken after the query is parsed and
before it starts scanning for rows to delete. When there is a write
conflict in READ COMMITTED it follows the update chain from the
visible row to find the latest committed version of the row, blocking
for a change in progress; so technically an UPDATE or DELETE at the
READ COMMITTED transaction isolation level sometimes strays from a
single snapshot for the statement. The alternative would be to
restart the statement after a write conflict where the other
transaction commits, which could perform much worse when a statement
affects a large number of rows.
A writing query may therefore find rows by reading some committed
values, wait for a writing lock and then retest if the write should be
performed based on values from a subsequent commit. This is not
intuitive that data accessed from a single query may correspond to
several commits. The second paragraph could be started by something like
"Although, any single query will see only committed values, a writing
query may see an inconsistent state of the database" for a better
introducing this inconsistent idea. Also, as locking is described later
(section 13.3), the UPDATE/DELETE example explanation (the sentence
"This occurs because [...] the criteria.") may be too synthetic. I
propose to extend it by stating which snapshot are considered and how
operations are sequentially performed. For example: "The DELETE query
selects from the committed database the row in which hits=10. As this
row is locked by the UPDATE (see ROW EXCLUSIVE lock in Section 13.3),
the DELETE is blocked and wait for the COMMIT to occurs, which will free
the lock. At this point, the DELETE proceeds by retesting if its
selected row must be deleted. This is no longer the case as the DELETE
snapshot of the row results from the last COMMIT that incremented hits
to 11."
As I am not an expert, I cannot guarantee that there is indeed any
issue.
Well, based on what you said there appears to be at lest some room to
clarify or elaborate, so people
