Concurrent BEGIN–SELECT–INSERT causes instant SQLite3.7.6 "database locked"-error, overriding busy timeout?

In an sqlite3-console:

sqlite>
    CREATE TABLE items (id PRIMARY KEY);
    BEGIN;
    SELECT * FROM items;
    INSERT INTO items VALUES(78);
sqlite> _

And then in a second console:

sqlite>
    .timeout 10000;
    BEGIN;
    SELECT * FROM items;
    INSERT INTO items VALUES(78);
Error: database is locked
sqlite> _

The "database is locked"-error happends instantly, which can't be right, right?

If I omit the SELECT in the second console the busy handler waits for 10 seconds at the INSERT. I've found that using BEGIN EXCLUSIVE also makes the second 开发者_开发问答transaction wait for 10 seconds, but then at the BEGIN-statement. (Which I've resolved to as a workaround.)

My question: Is this a bug, or as it should be? If this the expected behaviour, then why?

Thanks!

(SQLite v3.7.6)

The second transaction cannot complete until the first one releases its write lock, and the first one can't complete until the second transaction releases its read lock. It thus makes sense to rollback the second one right away, since it cannot complete in any amount of time, so the first one can finish and you can try again.

BEGIN EXCLUSIVE acquires the exclusive lock right away, instead of waiting for the first query, which explains the difference you see.

You should watch out for locked database (SQLITE_BUSY) while in transactions, and rollback and try again if that happens. The timeout busy handler won't protect these.

Yes this is normal. Please read: http://www.sqlite.org/lockingv3.html