WAL mode permits 'reading' by multiple connections while 1 connection is writing. Never ever is more than a single writer permitted. It does this by creating "cursor stability" when a read transaction is commenced (that is, changes to the database made on a DIFFERENT CONNECTION will not be visible).
The issue with the test you have designed is that the first process that runs is within a transaction, and that transaction does not end until the "select" is finalized. When the "select" is processed, the connection acquires a "read" lock on the database for the ENTIRE DURATION of the processing of the select statement (that is, until "no more rows" is returned or the statement otherwise reset). When you process the "update" statement on THIS VERY SELF-SAME CONNECTION, you have upgraded the existing lock from a READ lock to a WRITE lock. Only ONE connection may have a write lock at any given time. Since the lock is being held until the "select" statement is completed, NO OTHER CONNECTION can acquire a "write" lock on the database. If you wish to test concurrency of updates then you must put the update in a DIFFERENT CONNECTION than the one that contains the select. And specify a busy timeout since it is probable that your two processes will still attempt to acquire write locks at the same time. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Jim Dossey >Sent: Monday, 20 November, 2017 12:37 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] WAL mode with readers and writers > >Thanks to feedback from Simon Slavin, I now understand how >data_version >works and have it working in my code. But in my testing, I tried >another situation to see what would happen with locking in WAL >mode. I >have a process that does the following pseudo-code with a table: > >sqlite3_prepare("SELECT * FROM table;"); >while (sqlite3_step() == SQLITE_ROW) { > x = current_rowid(); > sqlite3_exec("UPDATE table SET value=1 WHERE rowid=x;"); > sleep(1); >} > >Basically it does a SELECT, then for each row found it does an UPDATE >on >that row to set some value. I know there are better ways to do this >in >SQL - this is just a test of locking in WAL mode. But it is a real >situation that could happen in my application. > >If I run this process twice, the first instance will start generating >SQLITE_BUSY errors on the UPDATE when the second instance starts >up. I >thought in WAL mode you could intermix readers and writers. Is this >not >the case? The 2 read loops seem to work just fine. It's just that >the >first process can no longer do UPDATE's when the second process >starts. >I tried compiling with -DSQLITE_THREADSAFE=1 but that did not >help. I >suspect that in WAL mode you can have multiple readers but only 1 >writer. > >Broadcasters General Store, Inc. Disclaimer - This message contains >confidential information and is intended only for the individual(s) >named. If you are not the named addressee you should not >disseminate, distribute or copy this e-mail. Please notify the >sender immediately by e-mail if you have received this e-mail by >mistake and delete this e-mail from your system. If you are not the >intended recipient you are notified that disclosing, copying, >distributing or taking any action in reliance on the contents of this >information is strictly prohibited. > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
