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. 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
