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 transaction. 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 restart. 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. -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
