Combining your transactions would probably help a fair bit. And I think you're sleeping WAY too long. Besides upgrading your sqlite use sqlite3_busy_timeout() -- it will handle the sleeps for you and do it much faster than what you're doing (besides doing it automaticalliy for you whenever it needs to). I take it that you're not seeing a lot of "locked" errors for a single user transaction??? With a bunch of users you're quite likely get one collision but I'd be surprised if it was more than one at the size you're talking about. You didn't say if you were exhausting your 50 times or how many sleeps you ended up doing for any one person. If you use sqlite3_busy_timeout() and give it 25000 milliseconds (25 seconds) I'll bet you don't see any "locked" messages any more plus you'll get faster user response times. You might also find it useful to put a tic/toc function in your code and show the time to execute all the transactions in your audit log. Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of raf Sent: Sun 7/11/2010 11:37 PM To: sqlite-users@sqlite.org Subject: EXTERNAL:[sqlite] database is locked(5) too often - what am i doing wrong? Greetings, I've been using sqlite-3.4.2 for a customer-only website for a few years now and the number of users has recently grown to the point where I'm now seeing too many "database is locked(5)" (i.e. busy) errors. When my code gets a "database is locked(5)" error, it sleeps for a random period between 0.25-0.5 seconds and tries again. It repeats this up to 50 times before giving up and I only see an error if all of the repeated attempts failed as well. I'm now seeing this error a lot. My use of sqlite seems unable to cope with more than about 30-50 concurrent updaters. I'm not sure of the exact number. Is that typical? Bear in mind that every use of this website is recorded in the database for auditing purposes so all users are updaters. Each page hit results in several database writes each of which is currently in a separate transaction. Is combining these into a single transaction likely to help or make matters worse? Or is this just an inappropriate use of sqlite? I expect I'll have to migrate it to postgres but I thought I'd ask for opinions here first. Cheers, raf _______________________________________________ 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
