Path: csiph.com!usenet.pasdenom.info!dedibox.gegeweb.org!gegeweb.eu!nntpfeed.proxad.net!feeder1-2.proxad.net!proxad.net!feeder1-1.proxad.net!hq-usenetpeers.eweka.nl!hq-usenetpeers.eweka.nl!bcyclone03.am1.xlned.com!bcyclone03.am1.xlned.com!newsfeed.xs4all.nl!newsfeed1a.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: "Frank Millman" <frank@chagford.com>
Subject: Re: Db transactions and locking
Date: Fri, 28 Nov 2014 07:44:45 +0200
References: <m56u4s$baa$1@ger.gmane.org> <4loe7at2ls7tfq0oe041ru9svvsm8aksik@4ax.com>
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.16393.1417153499.18130.python-list@python.org>
Lines: 75
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:81666


"Dennis Lee Bieber" <wlfraed@ix.netcom.com> wrote in message 
news:4loe7at2ls7tfq0oe041ru9svvsm8aksik@4ax.com...
> On Thu, 27 Nov 2014 12:24:39 +0200, "Frank Millman" <frank@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