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
