Db transactions and locking

[Chris Angelico]

On Thu, Nov 27, 2014 at 9:24 PM, Frank Millman <frank at chagford.com> wrote:
> "PostgreSQL by default commits between each statement unless you explicitly
> start a transaction."
>
> 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.

The default is probably for backward compatibility, but automatically
opening a transaction is the better way. (When I used DB2, back in the
90s, that was the only way anything was ever done. There was no such
thing as autocommit.) Assume that everything you do is inside a
transaction; that's how it's going to be on the back-end anyway. For
example, PostgreSQL's MVCC is controlled by transaction numbers; any
row with a transaction number greater than the viewing transaction's
is invisible. That means that even a SELECT statement must, by
definition, be in a transaction.

One easy way to use psycopg2 - though I don't know if this is the best way - is:

with conn, conn.cursor() as cur:
    cur.execute(...)

Exit the with block and you close off the transaction and release
cursor resources.

ChrisA