Re: [Fwd: Re: Python client + select = locked resources???] - Mailing list pgsql-general

Hi!

2009.07.01. 9:43 keltezéssel, Craig Ringer írta:

On Wed, 2009-07-01 at 09:23 +0200, durumdara wrote:

 

Firebird have repeatable read, but PG is not have it. Hmmm... Then that is meaning that every statement is in new
transaction context which can makes inconsistency in the views...
For example (pseudo):
select item_head, count(items)
select items

Possible: count(items) <> len(fetchall(items)) if someone committed a
new record into "items" table...

Am I thinking good?   

You are. However, this is ALSO true in a transaction by default. 

Sh*t... (* = [o | u ])... :-(

MySQL doc:

REPEATABLE READ

This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 13.2.8.2,

PostgreSQL defaults to the READ COMMITTED isolation level, which means
that statements may see data that was committed by another transaction
after the start of the transaction in which the statement is run, but
before the start of the statement. 

Ahhhh... this was I want to avoid.

In the Firebird world I simply used Repeatable Read without concurrency or other error.

If some updates had conflicts in nonwaiting mode, the FB sent an error message me to show, we had a problem.
But normally, if someone simply read the tables, or inserted new records to it, the FB handle this case without errors.

I got errormessage only if two transactions want to do something in same record in same time...

This is conflict:
tr1: update a set a.code = a.code
tr2: update a set a.name = "al" where a.code = 1

This is not:
tr1: select count(*) a1 from a
tr2: update a set a.name = "al" where a.code = 1
tr2: insert into a ...
tr1: select count(*) a2 from a (a1 = a2)
tr1: select * from a (a1 = a2 = fetched(records))

If you want to avoid that, you may use the SERIALIZABLE isolation level.
That has its own complications and costs, though, including the need to
be prepared to retry any transaction after a serialization failure.

(Of course, your app should be prepared to retry a transaction ANYWAY
unless you're incredibly sure your code is perfectly free from lock
conflicts etc). 

Sometimes we need consistent data, this [select count(*) from a <> fetchall(select * from a)] not good result.

But I'm not sure in SERIALIZABLE mode because I don't know, if I change the records, or add new records to table a, I can get some errors in any of the clients, or PG handle this without problems - as Firebird do it, or I got many errors in the clients.

Data integrity is very important sometimes  - for count(*) = len(fetched(*)), and for querys, sums, subqueries are let equal.

See:

http://www.postgresql.org/docs/8.3/static/transaction-iso.html

Once again, I VERY strongly recommend reading the whole PostgreSQL
manual. It'll teach you a lot about SQL and relational databases in
general as well as PostgreSQL in particular, and is very well written. 

Ok, I understand it, I read it, but experience is more and more than the read.

For example:
I read the apache/fastcgi documentation, but never I think that it is not working in Windows (as working in Linux)... :-(

Sometimes the people need to release her/his ideas from the idealist world, because hard to realize.

Possible if I change my default transactions to "serial", it is not working; and it is better to working with read committed - and with some little mistakes that client's won't see...

Thanks:
    dd