Python DB API - commit() v. execute("commit transaction")?

[Frank Millman]

"Skip Montanaro"  wrote in message 
news:CANc-5Uz2ruXRWnAX8pJEVQZtQbndC0aOJz3gGeb04k1ZfFfQgQ at 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