On Mon, Jun 14, 2010 at 02:44:02PM +0200, Robert Latest scratched on the wall:
> On Fri, Jun 11, 2010 at 5:26 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
> > 1. Ensure that you have no transactions started with SELECT and
> > continued with INSERT/DELETE/UPDATE
> 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?

  Not so much... the issue is that if you start a transaction, do a
  bunch of selects (e.g. read-only) and then modify the database in
  some way, the transaction needs to acquire the write lock in order to
  proceed.  If there are going to be locking issues, this is where they
  will manifest themselves.  This is also the specific situation that
  can lead to a deadlock, so you're only real choice is to rollback the

  On the practical side, this shouldn't be a big deal... The transaction
  should be read-only at that point, so rolling back the transaction
  doesn't actually change the database or undo any of the "work" done
  by the application.  As an extension of that, the program logic
  should be fairly easy to deal with, since no actual changes have
  been made.

  But that assumes the program flow and control is designed to handle
  that.  If the application design expects to do a "unit of work" and
  that unit just happens to have a bunch of read stuff before it gets
  to the write stuff, you're still breaking that unit of work in the
  middle.  Depending on the code design, that can be a tricky error
  condition to handle.

  Possible solutions include opening the transaction with a pre-acquired
  reserved lock (or exclusive lock).  That avoids the mid-way
  transition.  There can still be issues, but they'll happen at the
  very beginning of the transaction, when it is usually easier to

  Another possibility is to break the work up into two steps-- do all
  the read work, then open the transaction and do all the write work. 
  That can be risky if the two parts are related, however.  Read-only
  transactions are still useful, in that they keep the database from
  changing while you're looking up keys or something critical.

  But your general approach of looping over a select and doing other
  things is completely valid.  You just need to be ready to handle a
  busy condition at the first modification.


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