On Mon, Jun 13, 2011 at 06:21:10PM +0200, Jean-Christophe Deschamps scratched on the wall: > Hi Jay, > > >> Invoke sqlite3_busy_timeout() with a safe timeout for every connection > >> to the DB. > >> Use IMMEDIATE transactions everywhere. > >> > >> That's all you have to do. > > > > Well, yes and no. You still have to deal with the case of > > SQLITE_BUSY being returned due to deadlocks. Setting a timeout and > > using BEGIN IMMEDIATE will help significantly, but not completely > > eliminate this issue. It also means that if an SQLITE_BUSY does slip > > through, you have no choice but to rollback the transaction and start > > all over. > > > > For more specifics, see: > > > > http://sqlite.org/lockingv3.html > > http://sqlite.org/c3ref/busy_handler.html <= deadlock info > > http://sqlite.org/c3ref/busy_timeout.html > > I'm not sure you can get deadlocks with immediate transactions. As > I understand it, it would defeat the purpose of immediate > transactions. Do you have a sample test pseudo-code to show how it > can happen?
Looking at this again, it would seem you are correct. The BEGIN IMMEDIATE can, of course, fail, but once that's passed you should be good to go. Without the busy handler you can still get _BUSY return codes from any command (including the final COMMIT), but you should be able to wait them out-- assuming all read interactions with the database finish and reset/finalize their statements in a reasonable amount of time. That doesn't apply to other connections from other processes, of course. The BEGIN IMMEDIATE only "protects" the database connection that issues it. If another database connection attempts to modify the DB under a DEFERRED transaction (including an auto-commit transaction) a deadlock can still occur. However, in that case, it would normally be the responsibility of the DEFERRED connection to give up and release all the locks. Of course, many, many applications out there don't deal with this correctly, so you would need to be careful with a general-access database. -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
