On Mon, Jun 14, 2010 at 09:42:09AM -0400, Pavel Ivanov scratched on the wall:
> > This is interesting. I often have situations where I SELECT something
> > and then do manipulations (INSERT/DELETE/UPDATE) on the db as I
> > iterate through the results.
> >
> > Is this what you mean by your statement? If yes, how should such
> > situations be avoided, and why?

> If you don't issue BEGIN then your
> SELECT and UPDATE are executed in different transactions

  I'm not sure that's true...  A database connection typically has only
  one transaction state.  Locks, etc., are owned by the connection, not
  by a statement.

  The SELECT is going to create a read-only autocommit transaction.  If
  a write statements are intermixed, they will need to establish an
  autocommit write transaction.  I'm fairly sure this simply upgrades
  the existing autocommit transaction, rather than creating a different
  logical transaction.  Once the write statement finishes, I would
  assume the still-active autocommit transaction remains at the
  elevated level, since there is no way to "degrade" a transaction
  without committing it.  The initial autocommit transaction won't
  actually commit and close until the all active statements are fully
  reset or finalized.

  In short, it will be the same as putting a BEGIN/END around the
  where the SELECT is processed in the code.

  I think.  I have to admit that this gets into some gray areas on
  autocommit transactions that I don't know all that well.  
  I do know is that intermixing modifications while walking through
  a SELECT has always worked exactly the way I expected.


Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
sqlite-users mailing list

Reply via email to