StressAsync fails

[tofutim]

I get about 400 errors in NCrunch (from sqlite being 'busy') and 17-18 in appveyor

[tofutim]

How do we get the stress test to pass? Is this a bug with sqlite3 in Windows?

[oysteinkrog]

This test has been buggy for a long time, I've tried to fix it but I have not yet been able to.
To be honest I don't really understand why it fails.

[tofutim]

When I debug in VS I keep running into 'busy' and 'database locked'. In
NCrunch I get like 400 of these errors, and the test fails because it asks
for 0 errors, and I have 400. In appveyor somehow there are only 15-40
errors, but it still stops in the same place.

In an earlier discussion on Generic and Mac, someone mentioned that their
test was failing at 499 compared to 500. This means they did not hit any
errors.

On Wed, Nov 11, 2015 at 2:31 PM, Øystein Krog notifications@github.com
wrote:

This test has been buggy for a long time, I've tried to fix it but I have
not yet been able to.
To be honest I don't really understand why it fails.

—
Reply to this email directly or view it on GitHub
#244 (comment)
.

[sjlombardo]

Since this test is running a large number of concurrent operations I would suggest two changes:

1. After opening the connection set PRAGMA busy_timeout to a reasonable value, perhaps start with 1000 and see how that works. This will allow a given connection to block for a short period of time instead of immediately returning an error if the database is locked by another operation.
2. Use WAL mode for the test database. It will drastically improve the ability of operations to run concurrently; with WAL mode readers will not block writers and writers will not block readers.

These two changes may resolve the errors you are seeing.

[tofutim]

Good suggestions, thanks!

On Thu, Nov 12, 2015 at 6:29 AM, Stephen Lombardo notifications@github.com
wrote:

Since this test is running a large number of concurrent operations I would
suggest two changes:

After opening the connection set PRAGMA busy_timeout
https://www.sqlite.org/pragma.html#pragma_busy_timeout to a
reasonable value, perhaps start with 1000 and see how that works. This will
allow a given connection to block for a short period of time instead of
immediately returning an error if the database is locked by another
operation.
2.

Use WAL mode https://www.sqlite.org/wal.html for the test database.
It will drastically improve the ability of operations to run concurrently;
with WAL mode readers will not block writers and writers will not block
readers.

These two changes may resolve the errors you are seeing.

—
Reply to this email directly or view it on GitHub
#244 (comment)
.

[tofutim]

@sjlombardo I tried your suggestion. Interestingly, journal_mode = delete (default) and journal_mode = wal have characteristically different errors. Here are the errors for delete and wal for a sample for busy timeout of 100. I changed the test to 100 items because 500 was taking too long. wal is better than delete, but increasing the timeout for wal does not seem to improve it. (Not sure why not, since it is busy).

journal_mode: wal
busy_timeout: 100
0 Busy
1 Busy
2 Busy
3 Busy
4 Busy
5 Busy
6 Busy
7 Busy
8 Busy
9 Busy
10 Busy
11 Busy
12 Busy
13 Busy
14 Busy
15 Busy
16 Busy
17 Busy
18 Busy
19 Busy
20 Busy
21 Busy
22 Busy
23 Busy
24 Busy
25 Busy
26 Busy
27 Busy
28 Busy
29 Busy
30 Busy
31 Busy
32 Busy
33 Busy
  Expected: 0
  But was:  34

and

journal_mode: delete
busy_timeout: 100
0 database is locked
1 database is locked
2 database is locked
3 database is locked
4 Failed query
5 database is locked
6 Busy
7 Failed query
8 database is locked
9 database is locked
10 database is locked
11 database is locked
12 database is locked
13 database is locked
14 Busy
15 Busy
16 database is locked
17 database is locked
18 database is locked
19 database is locked
20 database is locked
21 database is locked
22 database is locked
23 database is locked
24 database is locked
25 database is locked
26 Busy
27 database is locked
28 database is locked
29 database is locked
30 Failed query
31 database is locked
32 database is locked
33 Busy
34 database is locked
35 Busy
36 database is locked
37 database is locked
38 database is locked
39 database is locked
40 database is locked
41 database is locked
42 database is locked
43 Busy
44 database is locked
45 database is locked
46 database is locked
47 Busy
48 Failed query
49 database is locked
50 database is locked
51 Failed query
52 database is locked
53 database is locked
54 database is locked
55 Failed query
56 database is locked
57 database is locked
58 database is locked
59 database is locked
60 Busy
61 Busy
62 Busy
63 database is locked
64 Busy
  Expected: 0
  But was:  65

[tofutim]

I just discovered that the busy_timeout is reset in new connections. I probably have to set it longer for each of the threads. That is a little scary.

[tofutim]

Sadly, even setting busy_timeout for each of the runs did not change the Busy error. I changed it from 100 to 1000 with no real change in Busy errors, which is about 33%.

[tofutim]

http://beets.radbox.org/blog/sqlite-nightmare.html was interesting, i wonder if we are facing that in the test

[tofutim]

The Busy signal always comes in INSERT rather than when it is selecting from the table.

[tofutim]

I was able to make progress by adding this to PreparedSqlLiteInsertCommand

        Result r;
        lock (_locker)
        {
            r = sqlitePlatform.SQLiteApi.Step(Statement);
        }

Now there are no Busy errors, but the final count is 94 instead of 100. Locked using

    static readonly object _locker = new object();

But does this mean we need this everywhere? Isn't SQLite supposed to handle this. :P

[tofutim]

Ok, with the locker code in place, the new error is that there are only 94 / 100 when the count is asked for. However, if you Task.Delay(1000) before reading the count, everything is actually in there. This means the Task completes before the task is really completed. Not sure how that is possible, but maybe each connection has a separate cache of some sort.

      await Task.Delay(1000);
        int count = await globalConn.Table<Customer>().CountAsync();
        Assert.AreEqual(n, count, "Not enough items in table");

I experimented with some values and 100-200 usually works. Maybe we should be using the same connection instead of opening a lot of new ones? I don't know.

[tofutim]

Once I enter 'wal' I can't back out of it at the end of the test.

        await globalConn.ExecuteScalarAsync<string>("PRAGMA journal_mode = delete"); // default

at the end gives me 'database is locked' ;(

[tofutim]

Why is

    lock (_locker)
    {
        r = sqlitePlatform.SQLiteApi.Step(Statement);
    }

needed when it should already be locked by SQLiteConnectionWithLock

Actually, and this is bad because the _locker is static so it would prevent writing to different databases