"Dennis Lee Bieber" <[email protected]> wrote in message
news:[email protected]...
> On Thu, 27 Nov 2014 12:24:39 +0200, "Frank Millman" <[email protected]>
> declaimed the following:
>
>
>>All Python database adaptors that I have used start a transaction when you
>>open a cursor. I have just re-read DB-API 2.0, and I cannot see anything
>>that specifies this behaviour, but AFAICT this is what happens.
>>
>
> Really?
>
Well, I can't prove it, no, but *something* starts a transaction, even if
you do not specify one.
Maybe the adaptor detects the first statement after opening a cursor, and
starts a transaction at that point.
Here is my empirical 'proof' -
I start up a PostgreSQL interactive session with psql, and list the current
locks - there are 3, which always seem to be there.
>From another psql session, I issue some sql commands. Here is a list of the
commands, followed by the number of current locks.
SELECT * FROM mytable - 3
BEGIN - 3
SELECT * FROM mytable - 4 (a new AccessShareLock on mytable)
COMMIT - 3
This confirms what I posted earlier - "PostgreSQL by default commits between
each statement unless you explicitly start a transaction."
Then I start a python session, set up a connection using psycopg2, and do
the same.
cur = conn.cursor() - 3
cur.execute('SELECT * FROM mytable') - 4
cur.fetchall() - 4
cur.close() - 4
conn.commit() - 3
This seems to confirm what I thought, but then I continued, and was
surprised at the result.
I can repeat these lines at will -
cur.execute('SELECT * FROM mytable') - 4
conn.commit() - 3
But if I do this -
cur.execute('SELECT * FROM mytable') - 4
cur.execute('commit') - 3
cur.execute('SELECT * FROM mytable') - 3
cur.execute('commit') - 3
There seems to be a difference between conn.commit() and
cur.execute('commit'), which leaves the connection in a different state.
However, for my purposes, this is academic.
The main lesson I have learned is that you should always issue a commit
after any logical set of SQL statements, even if they are only SELECTs,
otherwise the locks are not released.
Frank
--
https://mail.python.org/mailman/listinfo/python-list
