Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #81623

Re: Db transactions and locking

Show key headers only | View raw

On Thu, Nov 27, 2014 at 9:24 PM, Frank Millman <frank@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

Back to comp.lang.python | Previous | Next | Find similar | Unroll thread

Thread

Re: Db transactions and locking Chris Angelico <rosuav@gmail.com> - 2014-11-27 21:34 +1100

csiph-web