On Thu, Dec 25, 2014 at 7:32 AM, Rick Kelly <rpke...@gci.net> wrote: > I'm using SQLite3 for the first time and am looking to see if those more > experienced can look at my deployment and offer suggestions. > > I have a client/server application running on MS Windows communicating over > TCP/IP supporting a few dozen clients. Basically, clients send a request to > the server and the SQL is built, submitted and results returned. >
Do I understand correctly: SQLite is running inside of the single server process you have built? In other words, you are not trying to access SQLite database files directly from the clients across a network? > > The database file is located in the same folder as the server. The server > is > multi-threaded. I'm using library version 3.8.7.4 > > The database file is opened as follows: > > 1. sqlite3_enable_shared_cache(%SQLITE_OPEN_SHAREDCACHE) > 2. Database is opened with %SQLITE_OPEN_READWRITE > 3. PRAGMA's are set > > PRAGMA JOURNAL_MODE=WAL; > PRAGMA WAL_AUTOCHECKPOINT=500; > PRAGMA SECURE_DELETE; > PRAGMA SYNCHRONOUS=FULL; > PRAGMA THREADS=0; > > All SELECT type requests are wrapped with BEGIN TRANSACTION/COMMIT and > UPDATE/INSERT/DELETE transactions are wrapped with BEGIN IMMEDIATE > TRANSACTION/COMMIT. The server uses a connection pool and threads request a > handle from the pool and return it to the pool immediately afterwards. The > largest size of the connection pool and the number of open handles is > limited to 5. In my stress testing, I've thrown thousands of requests at > the > server and the pool usually gets to two and rarely 3. > > If a request encounters %SQLITE_BUSY or %SQLITE_LOCKED, I'm retrying up to > 5 > times with a 100ms sleep in between. > > Will this approach minimize %SQLITE_BUSY and %SQLITE_LOCKED situations and > provide decent concurrency and long term stability? > > I haven't yet tried to serialize update requests so that only one at a time > is active. > > Rick Kelly > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Client-Server-Best-Practices-tp79728.html > Sent from the SQLite mailing list archive at Nabble.com. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
