Db transactions and locking
Frank Millman
frank at chagford.com
Fri Nov 28 01:57:07 EST 2014
"Frank Millman" <frank at chagford.com> wrote in message
news:m5924d$nbq$1 at ger.gmane.org...
>
>
> 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.
>
On reflection, this makes sense, and also 'proves' that my initial theory of
the adaptor starting a transaction on opening a cursor must be wrong.
My guess now is that the connection is in one of two states - a transaction
is active, or it is not. If a command is issued, and a transaction is not
active, then a transaction is started. If a conn.commit() or a
conn.rollback() is issued, the command is passed up to the database, and the
connection state is reset to not active.
cur.execute('commit') tells the database to commit the transaction, but the
adaptor is not aware of this, so does not reset. Therefore the next command
does not trigger starting a new transaction.
I have now learned another lesson - never use cur.execute('commit'), always
use conn.commit()
Frank
More information about the Python-list
mailing list