I think I see a potential problem with your logic. You say "up to 2 seconds" and "up to 4 times". So it sounds like you're doing a 500ms sleep on your retry with 4 tries? What you could easiliy hit is the probability that on each time you ask for a lock it could be busy doing the inserts you mentioned earlier (I'm thinking of how this thing scales). You can actually spin the BUSY loop a lot faster adn reduce the probability of hitting another lock. And I think SQLITE_BUSY is the only one you need to spin. Any other error should be fatal and probably shouldn't be repeated and should be returned immediately. You also need to spit out when this occurs so it can be monitored and so that you know how much it occurs in case another strategy is needed. You may need to use mutexes to allow your processes to act in sequence so they each know how many are in front of them and can return an intelligent "system is busy at the moment". Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Sam Carleton Sent: Mon 5/17/2010 3:46 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] read only databases and in-memory databases On Mon, May 17, 2010 at 2:27 PM, Black, Michael (IS) <michael.bla...@ngc.com > wrote: > Not rude -- just a communications problem... > > There's a difference between read-only and exclusive. > > Read-only just means you can't do inserts and creates. It's not the write > mode of the database that matters. It's the transactions you run. > read/write with insert/create might make selects and other inserts return > BUSY. > You left out two different types of transactions: update and delete. Obviously delete is on par with an insert, so that is a no brainer, but what about update's? Can a read-only DB do an update? By my definition that would be a writing operation, but is it seen differently with SQLite? I could see how updating a row might be less invasive because it very well could mean that a few bits are being changed. On the other hand, if it is a varchar, then it is variable length, which still leads me to believe that updates are not possible with a read-only connection. > What you're thinking of is exclusive access -- where only one process can > access the database at a time and you don't need any locks. > > If you're only doing one select or join you won't gain much by exclusive > access as your only doing a couple of locks. > > If you open a databse read-only, and I open it read-write -- I'll make you > BUSY if I do an INSERT -- and you'll make me BUSY if you do a select -- I > can' INSERT data until you release your READ-LOCK. > My current strategy to handle all my read-only connections is to implement the busy handler with what can amount to a pretty long delay, I think up to 2 seconds. No transaction should take more than a few hundred milliseconds, unless I coded it wrong. All my data is pretty small, and queries pretty simple. So I am going through the Insert/Update/Delete queries and adding a retry around the whole connection. The logic I am using is: As long as the return value from a sqlite_ function is the expected, keep going, if there is any type of error, let the error bubble to the top and rollback right before closing the connection and then retry up to 4 times. So with this in mind, it is my goal to get this one process that is only writing to a :memory: db, but reading from the main DB to open the DB as read-only, if possible. Sam _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
