Db transactions and locking

[Frank Millman]

"Dennis Lee Bieber" <wlfraed at ix.netcom.com> wrote in message 
news:4loe7at2ls7tfq0oe041ru9svvsm8aksik at 4ax.com...
> On Thu, 27 Nov 2014 12:24:39 +0200, "Frank Millman" <frank at chagford.com>
> 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