Re: Not commit

On Fri, Jan 30, 2009 at 3:52 AM, Antonio Prado <antonio-m7WKv7+duuT/[email protected]> wrote:
> In Python 2.4
>
> The cursor B does not view / updated insert in A.
>
> Why ??
>
>
> import psycopg
>
> A = psycopg.connect("host=%s dbname=%s user=%s" %(host, dbname,
> user))
> B = psycopg.connect("host=%s dbname=%s user=%s" %(host, dbname,
> user))
>
> A.cursor.execute("INSERT abc INTO tabela")
> A.commit()
>
> A.cursor.execute("SELECT abc FROM tabela")
> result = B.cursor.fetchall()
>
> result [abc]
>
> B.cursor.execute("SELECT abc FROM tabela")
> result = B.cursor.fetchall()
>
> result []
> Register not found.

I assume there is a bit more to this example than you've included
here, since the above is likely to work.

The first time you execute a statement on a connection (or first time
after commit/rollback), psycopg starts a database transaction.  If
there had been some activity on B prior to A.commit(), then B would
still be in a transaction from before the row was added to tablea so
won't see the new row.

If you do a commit or rollback on B, you'd start a new transaction and
should see all the changes made by other parallel connections.

As a general rule, if you don't know when the transaction will be
committed or rolled back when executing a statement, your program will
likely run into these sort of problems.  Long running transactions can
also cause performance problems or deadlocks for other simultaneous
connections to the database.

James.