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. -j -- 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
