David, your conclusion is my point. Even though the rows are gone,
the cursor is still able to get the rows.
Taking my previous example as a starting point, I broke it up into two
separate sessions. (Session 1 and Session 2 are completely different
connections to the database. Oracle sees them as two concurrent users.)
--================
-- Session 1
--================
SQL> CREATE TABLE t AS SELECT table_name FROM user_tables
2 WHERE table_name like 'DR$DOCUMENT%';
Table created.
SQL>
SQL> SELECT COUNT(*) FROM t;
COUNT(*)
----------
12
r
--================
-- Session 2
--================
SQL> variable x refcursor
SQL> begin
2 open :x for select * from t;
3 end ;
4 /
PL/SQL procedure successfully completed.
--================
-- Session 1
--================
SQL> delete from t;
12 rows deleted.
SQL> commit;
Commit complete.
SQL>
--================
-- Session 2
--================
SQL> print x
TABLE_NAME
--------------------------------
DR$DOCUMENT_BODY$I
DR$DOCUMENT_BODY$K
DR$DOCUMENT_BODY$N
DR$DOCUMENT_BODY$R
DR$DOCUMENT_SUBJECT$I
DR$DOCUMENT_SUBJECT$K
DR$DOCUMENT_SUBJECT$N
DR$DOCUMENT_SUBJECT$R
DR$DOCUMENT_URL$I
DR$DOCUMENT_URL$K
DR$DOCUMENT_URL$N
TABLE_NAME
--------------------------------
DR$DOCUMENT_URL$R
12 rows selected.
The above steps were performed in the order indicated. Databases
that offer transaction control but don't perform some kind of
multi-versioning (as Oracle does, and,
as I'm starting to think PG does), would not have allowed Session 1 to
delete the rows, as the shared locks held by Session 2 would have
prevented it. Alternatively (assuming a lower isolation level in
the same non-versioning database), Session 1 might have
been able to delete the rows, but then what would Session 2 see?
For non-versioning databases, where would the data in the final
action come from? The rows are gone (having been committed), but
non-versioning databases don't keep track of what the data looked
like at the start of the query. (That is, they don't attempt to get things
out of a rollback segment.) That's why they block in these situations
at normal isolation levels (repeatable read in particular).
None of this really affects PG in any way. I posted my first comment
to shed some light on Oracle's behavior. (Many people still work with
an understanding of transactions and concurrency that is the textbook
"shared locks block the acquisition of exclusive locks by other sessions"
model.) The original code sample I
posted is exactly the behavior expected of Oracle (and perhaps of PG,
but no one's answered that question). But it is emphatically not
the behavior one
expects out of, say, Sybase (circa System X anyway). Most transactional
databases still use non-versioning concurrency control. (PG increasingly
looks to be the exception, a prospect I find exciting. Sadly, I've not
found a PostgreSQL Concepts manual comparable to Oracle's.)
I'm looking forward to seeing how PG handles some of these samples. Don's
right in that multi-versioning is what dramatically increases concurrency.
But at this point, I'm getting confused as to what, if any issue, there
is with what I said? Sebastiano's tone is one of correction, but
the statement "So it's normal that the cursor retrieves the rows from the table
even if they've been deleted immediately after the open statement,"
doesn't apply to all databases universally. That fact is why I went on
to explain Oracle's reasoning behind using the rollback segment in
the first place.
Request notifications