-> --- Titus Brown wrote:
->
-> > You do need the rollback in any situation involving
-> > true transactions;
-> > otherwise multiple processes will not see the same
-> > committed data until
-> > after they do a commit.
->
-> I'm not sure I understand your point.
->
-> If you do a rollback, to "see" the same data, you
-> loose the current update, correct ?
->
-> Are you sure that's what one expect ?
->
-> By reading the psycopg doc, they assure consistency
-> for "heavily" multi-threaded applications.
->
-> Do you think that having (let's imagine) 8 SCGI
-> threads , we can put psycopg in default ?
psycopg is just another adapter; it's optimized for multi-threaded apps
in various ways.
Here's a demonstration of the transaction issue. Note that db
connections *start out* in a transaction.
Assume val = 1.
time db conn 1 db conn 2 db conn 3
---- --------- --------- ---------
(begin transaction) (begin transaction)
1 val = 5
2 select val
3 commit
4 select val (begin trans)
5 rollback/commit select val
6 select val
At both t = 2 and t = 4, db conn 2 will see val=1. This is because
transactions are isolated, so the commit by db conn 1 does not affect
db conn 2 at all.
db conn 2 will only see val = 5 at t = 6, after exiting and then
resuming a transaction.
db conn 3 will see val = 5 at t = 5, because it is entering a new
transaction after db conn 1 commits.
My solution has always been to do a rollback prior to each select, so
that at e.g. t=4, db conn 2 would do a rollback and pick up on the
commit done by db conn 1. Another solution would be to create a new
connection for each select.
You can pretty easily test all this out for yourself with multiple
windows. Note that with PostgreSQL, INSERTs seem to show up across
transactions but UPDATEs do not; not sure I'd expect that.
cheers,
--titus