>As was being discussed yesterday, I have four processes accessing the >same database file. When they perform an sqlite action, I wish them to >block if the DB is not available. SQLite does not block if it finds the >db busy or locked, it returns an error code.
You can have SQLite do all this by itself, I mean without any extra code. I've found this is the easiest way to handle the situation. 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. >Any feedback on this (especially with reference to how long I should >sleep for) would be much appreciated. Also, I know this is pretty >horrible - any suggestions for a better approach would also be great. How long should the timeout be? I'd say as long as possible and here's why. After DB connections call sqlite3_busy_timeout() with a nonzero timeout, when an immediate transaction A can't start due to another transaction B locking the DB, SQLite will first retry a few times by itself, then put A to sleep internally for some time after which it will retry until either completion of the transaction or timeout reached. You might say "so I need a timeout a bit larger than the longest transaction". No, things are not that simple. If another transaction C is launched by another process and get blocked like A, it will enter its own cycle of retries but asynchronously with A. When C terminates, you have no clue as to which of A or C will be next to eventually lock the DB. Even if A was first in the "queue", C could very well get active before A, just because SQLite has no queue. That's why your timeout needs to be much longer than the longest possible sequence of the longest transactions. Sounds like A could very well never execute if B, C, D, C, B, .. transactions take precedence every time. Just like in quantum physics, it's possible! In practice you should have a good idea of how long it is reasonable to wait. In most of my (business) applications, I now set 600000 (10 minutes). Thus even in the worst case I'm sure that all my trasactions will eventually find their way. That or I'm terribly unlucky! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
