WAL mode with readers and writers

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

WAL mode with readers and writers

Jim Dossey
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL mode with readers and writers

Keith Medcalf

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-
>[hidden email]] On Behalf Of Jim Dossey
>Sent: Monday, 20 November, 2017 12:37
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL mode with readers and writers

Simon Slavin-3
In reply to this post by Jim Dossey


On 20 Nov 2017, at 7:37pm, Jim Dossey <[hidden email]> wrote:

> 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);
> }

In SQLite, as in other SQL engines, all access to a database must be done inside a  transaction.  If SQLite was being stuffy and uncooperative if you tried

        SELECT * FROM table

without declaring a transaction, you would get an error back telling you you did it wrong.  What you would be expected to do would be

        BEGIN;
        SELECT * FROM table;
        COMMIT;

Operations on the database by commands like SELECT and INSERT are minimal.  It’s the COMMIT which does all the hard stuff.  Locks start off as a READ lock, and are promoted to a WRITE lock the first time they try to do any writing.

However, SQLite is polite and convenient and if it sees you’ve forgotten to open a transaction, it opens one for you, and closes it as soon as possible, to let other connections get a lock as soon as possible.  Within your code above this would be

sqlite3_exec("BEGIN");
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);
}
sqlite3_exec("COMMIT");

The COMMIT cannot go earlier because until you have received your SQLITE_DONE from the loop you are still executing your SELECT statement.

Reviewing your proposed procedure knowing the above you can see that you are still maintaining a write lock on the database even between UPDATEs, because the transaction which contains them all is still running.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users