"Skip Montanaro" wrote in message
news:canc-5uz2ruxrwnax8pjevqztqbndc0aojz3ggeb04k1zfff...@mail.gmail.com...
Assuming the underlying database supports transactions, is there any
difference between calling the commit() method on the connection and
calling the execute method on the cursor with the "commit transaction"
statement? It seems a bit asymmetric to me to start a transaction with
cur.execute("begin transaction")
but end it with
conn.commit()
Yes there is a difference, at least as far as the combination of PostgreSQL
and psycopg2 is concerned. I will use 'PSQL' in the following, to save me
some typing.
A while ago I had to delve into PSQL locking, as I had a problem with locks
not being cleared. I learned that, for a simple SELECT statement, PSQL
checks to see if it is in a transaction. If not, it does not set any locks,
but if it is, it creates a lock which is cleared on the next
COMMIT/ROLLBACK.
By default, psycopg2 uses 'autocommit', which means that even a SELECT is
preceded by a 'BEGIN' statement internally. I never changed the default, so
all of the following assumes that autocommit is on.
I had many SELECT's, but I was not issuing any form of commit, so the locks
built up. I solved my problem by always committing. However in my
experimenting I found something curious.
I had one window open on a python session, where I could execute commands,
and another on a psql session, where I could monitor the 'lock' table.
I found that, if I issued a SELECT, a lock was created, if I called
conn.commit(), the lock was cleared. I could repeat this sequence and the
pattern was consistent.
However, if I issued a SELECT and called cur.execute('commit'), the lock was
cleared, but the next SELECT did *not* create a lock.
I worked out a possible reason for this, which I have not proved it by
examining the source code of psycopg2, but is internally consistent. The
theory goes like this -
psycopg2 is in one of two states - a transaction is active, or it is not
active. If you execute any command, and a transaction is not active, it
starts a transaction first. If you call conn.commit() or conn.rollback(), it
sends the command to the database and resets its state. However, (and this
is the theory,) if you call cur.execute('commit'), it sends the command to
the database, but does not reset its state. So when you execute the next
command, it thinks the transaction is still active, so it does not start a
new transaction. PSQL, on the other hand, knows that the previous
transaction has been committed, so if the next command is a SELECT, it does
not create a lock.
As I said, I cannot prove this, but the theory fits the observed behaviour
perfectly, so I have proceeded on the assumption that it is true. Therefore
I now always run every SQL command or block of commands within a context
manager, which always calls conn.commit() or conn.rollback() on exit, and I
have not had any more problems. I use exactly the same code for sqlite3 and
for Sql Server/pyodbc, and it has not caused any problems there either.
Frank Millman
--
https://mail.python.org/mailman/listinfo/python-list
