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

Groups > comp.lang.python > #81669 > unrolled thread

Re: Db transactions and locking

Back to article view | Back to comp.lang.python

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  Re: Db transactions and locking "Frank Millman" <frank@chagford.com> - 2014-11-28 08:57 +0200

#81669 — Re: Db transactions and locking

"Frank Millman" <frank@chagford.com> wrote in message 
news:m5924d$nbq$1@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

[toc] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web