On Monday, 17 June, 2019 17:50, Simon Slavin <[email protected]> wrote:
>Can someone please explain this error message to me:
>
>Simple database, journal mode set to 'delete', accessed by two
>simultaneous sessions running the SQLite command-line shell,
>SQLite version 3.28.0 2019-04-15 14:49:49
>
>Session A:
>
>PRAGMA journal_mode; <-- says 'delete'
>CREATE TABLE Test (a TEXT);
>INSERT INTO Test (a) VALUES "first";
>BEGIN;
>SELECT * FROM Test; <-- first
>
>Session B:
>
>BEGIN;
>INSERT INTO Test (a) VALUES ('second');
>SELECT * FROM Test; <-- first / second
>
>Session A:
>
>SELECT * FROM Test; <-- first
>
>Session B:
>
>END; <-- Error: database is locked
>
>Is session B complaining that session A has a lock ?
Yes. In order to commit the transaction it must be able to escalate EXCLUSIVE
(write) lock, meaning that all readers must "be out of town". However, session
A has a lock (it is hanging about in town) thus preventing the transaction from
committing. journal_mode=delete permits multiple simultaneous readers but only
ONE WRITER.
>If session A had a lock why was there no complaint for the INSERT ?
Because at the time session A needed the EXCLUSIVE lock in order to write to
the database, session B did not yet have any locks at all. When the INSERT
command was executed in AUTOCOMMIT mode, it obtained the necessary INTENT lock
immediately, escalated it to EXCLUSIVE when the implicit transaction committed
at the end of the statement execution, and released all locks on the database.
Session B was merely idling outside city limits when this occurred thus not
interfering with the activities of session A.
>If session B had a lock why didn't session A get a complaint when it did the
>SELECT * ?
At that particular instant in time both session A and session B held SHARED
locks on the database. That means that both sessions can READ from the
database. It is not until session B attempts to commit its transaction that
all other readers need to "be out of town".
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
