SQLite3 pragma locking_mode EXCLUSIVE prevents complex cmd line searches of history.db.

What steps will reproduce the problem?

1. Need to periodically and efficiently search history.db to find valid 
services' IPs blocked by mistake.

2. Example services: NTP pools (TCP port 123), Chat programs (various TCP 
ports, differs by chat protocol), Mail programs (wide variety of options these 
days, especially for secure connections), Games (numerous TCP/UDP potrs), 
diagnostic utilities (TCP/UDP/ICMP/ARP/other protocols), local/incoming 
HTTP/HTTPS (not outgoing/external).

3. Using built-in search not feasible, would take tens of minutes possibly up 
to a half hour to perform all combinations of queries, cobble together the 
results into one text file, and research each one.

4. Custom command line PHP script can accept options to include or exclude 
services (based on category, protocol, port, source or destination) and 
dynamically construct a complex SQL query, and display all results within a 
fraction of a second.

5. Current SQLite3 pragma locking_mode EXCLUSIVE prevents this.

Additional information:

PeerBlock 1.1 and earlier sometimes suffered an SQLite I/O error related to 
SQLite3 accessing the history.db file.  The pragma locking_mode EXCLUSIVE was 
set in r619 on May 27, 2012, but not released until PeerBlock 1.2 r693 on 
January 14, 2014.

http://code.google.com/p/peerblock/source/detail?r=619

I request we take a closer look at how we set up the SQLite3 connection, and 
find a way that is less restrictive.  We may need to dig deeper to determine 
the cause of the I/O error in the first place.  We went from default NORMAL 
connection right to EXCLUSIVE, but there are many other options.  See pragma 
journal_mode options.  Maybe Write-Ahead Logging (WAL) would work better than 
the Rollback Journal, given care to optimize checkpoint frequency.  Maybe we 
can better control the use of per-connection locks, such as using PENDING, 
RESERVED, SHARED, without setting the global pragma locking_mode EXCLUSIVE.

http://www.sqlite.org/lockingv3.html
http://www.sqlite.org/wal.html

May be beneficial to develop a stress test so the I/O error can more reliably 
be triggered, to determine if changes help or hurt the situation.

Original issue reported on code.google.com by warp9p...@gmail.com on 29 Jan 2014 at 4:24

burstas / peerblock

SQLite3 pragma locking_mode EXCLUSIVE prevents complex cmd line searches of history.db. #477