Jan Snelders <jan(at)snelders(dot)net> wrote:
> I wrote small java program which performs some selects in one
> transaction (and thread) and one delete in another transaction and
> thread on the same table holding one record initially.
> The DELETE transaction commits.
> The program now selects and
> prints the records from the SELECT transaction. (0 records
> printed, we expected one record since we are still within the
> SELECT transaction which started while this record was still
> available)
You are probably running at the READ COMMITTED transaction isolation
level. The behavior you describe is allowed at that isolation
level, both by the standard and by PostgreSQL. You seem to be
expecting that the default transaction isolation level will be
SERIALIZABLE (which is not altogether unreasonable, since that is
what the standard requires); but PostgreSQL has a default
configuration of defaulting to the READ COMMITTED level. You can
edit your postgresql.conf file to specify
default_transaction_isolation = 'serializable' to get standard
conforming behavior.
Note that there are still some corner cases where you don't get full
ACID behavior at the SERIALIZABLE level in PostgreSQL version 9.0;
this has been enhanced to fully compliant behavior in the upcoming
9.1 release.
For 9.0 behavior, see:
http://www.postgresql.org/docs/9.0/static/transaction-iso.html
For behavior in the soon-to-be-release 9.1 version, see:
http://www.postgresql.org/docs/9.1/static/transaction-iso.html
-Kevin