Db transactions and locking

[Frank Millman]

"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