On Monday, 17 June, 2019 18:46, Simon Slavin <slav...@bigfraud.org> wrote:
>I think I see my error. I thought that the lock was promoted from >read to read/write when the INSERT command was processed. At this >point, SQLite knows that it is going to need to write. >Instead, although SQLite knows that it is going to have to write, it >does not try to promote the lock until COMMIT. >In some ways I can appreciate this: it allows other threads to finish >their work. But in others it seems wrong, as if SQLite is ignoring a >potential problem, in a way that may make it an even bigger problem >in the future. See https://sqlite.org/lockingv3.html for how transactions and locks work in journal_mode=delete|persist|truncate (ie, not WAL). There is a link on that page to how transactions work when WAL is in effect. Note that the default BEGIN [DEFERRED] [TRANSACTION] obtains a SHARED or RESERVED lock only when the next statement is executed on that connection. This means that an update (UPDATE/INSERT/DELETE) statement (on that connection) may fail because it might not be able to acquire a lock (either SHARED or RESERVED) that it requires. BEGIN IMMEDIATE [TRANSACTION] causes a RESERVED lock to be obtained immediately on that connection. Subsequent statements executed on that connection cannot fail to obtain the requisite RESERVED lock. However, it is theoretically possible for a COMMIT on that connection to fail if there are outstanding SHARED locks from other connections. Other connections will not be able to obtain a RESERVED lock and therefore cannot update the database while the RESERVED lock is held. BEGIN EXCLUSIVE [TRANSACTION] causes an EXCLUSIVE lock to be obtained immediately. Subsequent statements on the same connection cannot fail to obtain locks and the COMMIT will succeed without error or delay. No other connection may obtain any kind of lock on the database and are therefore unable to read or write the database. --- 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
