SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Kira Backes
Dear sqlite mailing list,

I had to spend 2 days debugging a SQLITE_BUSY_SNAPSHOT, and reading
the documentation did not help me. Our code base does not use
transactions at all (we have a segmented code base protected by
mutexes for a whole section, so reads/writes do not conflict ever). We
neither had a crashing sqlite connection nor a corrupted database
file. Even reading everything in isolation did not help me, since
we're supposed to be able to share a connection between threads as
long as we do not read/write into the same table at the same time,
which we were able to ensure due to the section mutexes.

After thinking a very long time about this I found the reason: You
absolutely can not share a WAL connection between threads or risk
SQLITE_BUSY events. Yes, you heard right. If connection A runs a
SELECT query in table A, then connection B inserts something into
table B, and then you try in a concurrent thread to INSERT into table
C using connection A you will get SQLITE_BUSY_SNAPSHOT errors until
all SELECT queries are finished on connection A (in my case this took
about a minute because I was reading some cache tables with several
threads, so there was never a second where all queries were
finished...). So I had SQLITE_BUSY_SNAPSHOT errors for a full minutes
even though I never used transactions anywhere... Just because I had
some long running SELECT statements in unrelated tables it made the
connection completely unusable.

This is not a theoretical case, this can happen *VERY* easily and as
far as I can tell this is not documented anywhere (and believe me,
I've ready nearly every single page of the sqlite3 documentation). So
you should really really document this very easy to trigger case.

And if you don't believe me: since I'm a nice girl I've written a unit
test (C++, catch2) for this which reliably reproduces this behavior:


TEST_CASE("Test SQLITE_BUSY_SNAPSHOT", "[sqlite3]")
{


  std::string db_name{"test_sqlite3_busy_snapshot.sqlite3"};
  if (std::filesystem::exists(db_name)) {
    REQUIRE(std::filesystem::remove(db_name));
  }



  std::mt19937_64 engine{std::random_device{}()};
  std::uniform_int_distribution<int64_t> u(INT64_MIN, INT64_MAX);

  auto open_db_fn = [&]{
    sqlite3* handle;

    REQUIRE(sqlite3_open_v2(db_name.c_str(), &handle,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_exec(handle, "PRAGMA journal_mode=WAL; PRAGMA
synchronous=NORMAL; PRAGMA secure_delete=FAST;", nullptr, nullptr,
nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_busy_timeout(handle, 600'000) == SQLITE_OK);

    return handle;
  };



  std::string insert_into_FIRST = "INSERT INTO `FIRST_table` VALUES (?)"s;
  std::string insert_into_SECOND = "INSERT INTO `SECOND_table` VALUES (?)"s;
  std::string insert_into_THIRD = "INSERT INTO `THIRD_table` VALUES (?)"s;



  // initialize db, insert 4 rows into FIRST table
  {
    sqlite3* init_handle{open_db_fn()};

    REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `FIRST_table` (`a`
LONG)", nullptr, nullptr, nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `SECOND_table`
(`a` LONG)", nullptr, nullptr, nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `THIRD_table` (`a`
LONG)", nullptr, nullptr, nullptr) == SQLITE_OK);


    sqlite3_stmt* stmt{nullptr};
    REQUIRE(sqlite3_prepare_v3(init_handle, insert_into_FIRST.c_str(),
-1, 0, &stmt, nullptr) == SQLITE_OK);

    for (int i{0}; i != 4; ++i) {
      REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK);
      REQUIRE(sqlite3_step(stmt) == SQLITE_DONE);
      REQUIRE(sqlite3_reset(stmt) == SQLITE_OK);
    }

    REQUIRE(sqlite3_finalize(stmt) == SQLITE_OK);

    REQUIRE(sqlite3_close(init_handle) == SQLITE_OK);
  }

  // init handle is closed, from here on we have a clean state





  sqlite3* FIRST_handle{open_db_fn()};
  sqlite3* SECOND_handle{open_db_fn()};



  std::atomic_bool shall_continue_read_from_FIRST = true;


  // Continuously read from FIRST table using FIRST handle
  std::thread thread_read_from_FIRST{[&] {
    sqlite3_stmt* stmt;
    REQUIRE(sqlite3_prepare_v3(FIRST_handle, "SELECT * FROM
`FIRST_table`", -1, SQLITE_PREPARE_PERSISTENT, &stmt, nullptr) ==
SQLITE_OK);

    while (shall_continue_read_from_FIRST) {
      while (sqlite3_step(stmt) == SQLITE_ROW) {
        sqlite3_column_int(stmt, 0);
        std::this_thread::sleep_for(std::chrono::milliseconds{5});
      }
      sqlite3_reset(stmt);
    }
    sqlite3_finalize(stmt);
  }};




  // Insert once into SECOND table (untouched before) using SECOND
handle (unused before)
  {
    sqlite3_stmt* stmt;
    REQUIRE(sqlite3_prepare_v3(SECOND_handle,
insert_into_SECOND.c_str(), -1, SQLITE_PREPARE_PERSISTENT, &stmt,
nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK);
    REQUIRE(sqlite3_step(stmt) == SQLITE_DONE);
    REQUIRE(sqlite3_reset(stmt) == SQLITE_OK);
    sqlite3_finalize(stmt);
  }




  // Insert once into THIRD table (untouched before) using FIRST
handle (which is still doing a select in another thread somewhere)
  {
    sqlite3_stmt* stmt;
    REQUIRE(sqlite3_prepare_v3(FIRST_handle,
insert_into_THIRD.c_str(), -1, 0, &stmt, nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK);
    REQUIRE(sqlite3_step(stmt) == SQLITE_BUSY);
    REQUIRE(sqlite3_extended_errcode(FIRST_handle) == SQLITE_BUSY_SNAPSHOT);
    REQUIRE(sqlite3_reset(stmt) == SQLITE_BUSY);
    REQUIRE(sqlite3_extended_errcode(FIRST_handle) == SQLITE_BUSY_SNAPSHOT);
    sqlite3_finalize(stmt);
  }



  // Stop select on FIRST handle and FIRST table in other thread
  shall_continue_read_from_FIRST = false;
  thread_read_from_FIRST.join();



  // Retry insert into THIRD table using FIRST handle
  {
    sqlite3_stmt* stmt;
    REQUIRE(sqlite3_prepare_v3(FIRST_handle,
insert_into_THIRD.c_str(), -1, 0, &stmt, nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK);
    REQUIRE(sqlite3_step(stmt) == SQLITE_DONE);
    REQUIRE(sqlite3_reset(stmt) == SQLITE_OK);
    sqlite3_finalize(stmt);
  }



  std::filesystem::remove(db_name);

}




kind regards,
Kira Backes
_______________________________________________
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: SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Clemens Ladisch
Kira Backes wrote:
> Our code base does not use transactions at all

When you do not use explicit transactions, SQLite will automatically
create implicit transactions.

<https://www.sqlite.org/lang_transaction.html> says:
| An implicit transaction (a transaction that is started automatically,
| not a transaction started by BEGIN) is committed automatically when
| the last active statement finishes. A statement finishes when its
| prepared statement is reset or finalized.

> we're supposed to be able to share a connection between threads as
> long as we do not read/write into the same table at the same time

One connection implies one transaction.  So if two statements happen
to be active at the same time in two threads, they will share
a transaction, and might keep the transaction active longer than the
other thread expects.

See <https://www.sqlite.org/isolation.html>.

> After thinking a very long time about this I found the reason: You
> absolutely can not share a WAL connection between threads or risk
> SQLITE_BUSY events.

This is not really related to threads; the same can happen when
a single thread tries to write in a formerly read-only transaction.

> This is not a theoretical case, this can happen *VERY* easily and as
> far as I can tell this is not documented anywhere

<https://www.sqlite.org/rescode.html#busy_snapshot>


It is strongly recommended to use a separate connection per thread.


Regards,
Clemens
_______________________________________________
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: [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Hick Gunter
In reply to this post by Kira Backes
Works as intended.

"our code base does not use transactions at all" does NOT mean that there are no transactions, just that SQLite uses *implicit* transactions, i.e. every statement is in it's own transaction.

"we can share a connection between threads as long as we don't read/write into the same table at the same time" is NOT a correct assumption. The assertion you need to prove for SQLite running in multi-threaded mode is "we can share a connection between threads as long as the connection is not used simultaneously in two or more threads".

What you program is doing is guaranteed to provoke SQLITE_BUSY_SNAPSHOT errors.

Your first thread is creating an implicit transaction on connection FIRST by reading from table FIRST.
Your second thread is creating an implicit transaction on connection SECOND by writing into table SECOND. The implied COMMIT "invalidates" the snapshot on connection FIRST.
Your third thread now attempts to insert into table THIRD on connection FIRST. But connection FIRST is within an READ transaction started by your first thread, so it has to escalate it's transaction to a WRITE transaction. But because it is in an "invalid" snapshot, it cannot do so.

This is well documented behaviour.

https://sqlite.org/isolation.html

"Another example: X starts a read transaction using BEGIN and SELECT, then Y makes a changes to the database using UPDATE. Then X tries to make a change to the database using UPDATE. The attempt by X to escalate its transaction from a read transaction to a write transaction fails with an SQLITE_BUSY_SNAPSHOT error because the snapshot of the database being viewed by X is no longer the latest version of the database. If X were allowed to write, it would fork the history of the database file, which is something SQLite does not support. In order for X to write to the database, it must first release its snapshot (using ROLLBACK for example) then start a new transaction with a subsequent BEGIN. "

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Kira Backes
Gesendet: Montag, 12. August 2019 09:33
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Dear sqlite mailing list,

I had to spend 2 days debugging a SQLITE_BUSY_SNAPSHOT, and reading the documentation did not help me. Our code base does not use transactions at all (we have a segmented code base protected by mutexes for a whole section, so reads/writes do not conflict ever). We neither had a crashing sqlite connection nor a corrupted database file. Even reading everything in isolation did not help me, since we're supposed to be able to share a connection between threads as long as we do not read/write into the same table at the same time, which we were able to ensure due to the section mutexes.

After thinking a very long time about this I found the reason: You absolutely can not share a WAL connection between threads or risk SQLITE_BUSY events. Yes, you heard right. If connection A runs a SELECT query in table A, then connection B inserts something into table B, and then you try in a concurrent thread to INSERT into table C using connection A you will get SQLITE_BUSY_SNAPSHOT errors until all SELECT queries are finished on connection A (in my case this took about a minute because I was reading some cache tables with several threads, so there was never a second where all queries were finished...). So I had SQLITE_BUSY_SNAPSHOT errors for a full minutes even though I never used transactions anywhere... Just because I had some long running SELECT statements in unrelated tables it made the connection completely unusable.

This is not a theoretical case, this can happen *VERY* easily and as far as I can tell this is not documented anywhere (and believe me, I've ready nearly every single page of the sqlite3 documentation). So you should really really document this very easy to trigger case.

And if you don't believe me: since I'm a nice girl I've written a unit test (C++, catch2) for this which reliably reproduces this behavior:


TEST_CASE("Test SQLITE_BUSY_SNAPSHOT", "[sqlite3]") {


  std::string db_name{"test_sqlite3_busy_snapshot.sqlite3"};
  if (std::filesystem::exists(db_name)) {
    REQUIRE(std::filesystem::remove(db_name));
  }



  std::mt19937_64 engine{std::random_device{}()};
  std::uniform_int_distribution<int64_t> u(INT64_MIN, INT64_MAX);

  auto open_db_fn = [&]{
    sqlite3* handle;

    REQUIRE(sqlite3_open_v2(db_name.c_str(), &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_exec(handle, "PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA secure_delete=FAST;", nullptr, nullptr,
nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_busy_timeout(handle, 600'000) == SQLITE_OK);

    return handle;
  };



  std::string insert_into_FIRST = "INSERT INTO `FIRST_table` VALUES (?)"s;
  std::string insert_into_SECOND = "INSERT INTO `SECOND_table` VALUES (?)"s;
  std::string insert_into_THIRD = "INSERT INTO `THIRD_table` VALUES (?)"s;



  // initialize db, insert 4 rows into FIRST table
  {
    sqlite3* init_handle{open_db_fn()};

    REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `FIRST_table` (`a` LONG)", nullptr, nullptr, nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `SECOND_table` (`a` LONG)", nullptr, nullptr, nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_exec(init_handle, "CREATE TABLE `THIRD_table` (`a` LONG)", nullptr, nullptr, nullptr) == SQLITE_OK);


    sqlite3_stmt* stmt{nullptr};
    REQUIRE(sqlite3_prepare_v3(init_handle, insert_into_FIRST.c_str(), -1, 0, &stmt, nullptr) == SQLITE_OK);

    for (int i{0}; i != 4; ++i) {
      REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK);
      REQUIRE(sqlite3_step(stmt) == SQLITE_DONE);
      REQUIRE(sqlite3_reset(stmt) == SQLITE_OK);
    }

    REQUIRE(sqlite3_finalize(stmt) == SQLITE_OK);

    REQUIRE(sqlite3_close(init_handle) == SQLITE_OK);
  }

  // init handle is closed, from here on we have a clean state





  sqlite3* FIRST_handle{open_db_fn()};
  sqlite3* SECOND_handle{open_db_fn()};



  std::atomic_bool shall_continue_read_from_FIRST = true;


  // Continuously read from FIRST table using FIRST handle
  std::thread thread_read_from_FIRST{[&] {
    sqlite3_stmt* stmt;
    REQUIRE(sqlite3_prepare_v3(FIRST_handle, "SELECT * FROM `FIRST_table`", -1, SQLITE_PREPARE_PERSISTENT, &stmt, nullptr) == SQLITE_OK);

    while (shall_continue_read_from_FIRST) {
      while (sqlite3_step(stmt) == SQLITE_ROW) {
        sqlite3_column_int(stmt, 0);
        std::this_thread::sleep_for(std::chrono::milliseconds{5});
      }
      sqlite3_reset(stmt);
    }
    sqlite3_finalize(stmt);
  }};




  // Insert once into SECOND table (untouched before) using SECOND handle (unused before)
  {
    sqlite3_stmt* stmt;
    REQUIRE(sqlite3_prepare_v3(SECOND_handle,
insert_into_SECOND.c_str(), -1, SQLITE_PREPARE_PERSISTENT, &stmt,
nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK);
    REQUIRE(sqlite3_step(stmt) == SQLITE_DONE);
    REQUIRE(sqlite3_reset(stmt) == SQLITE_OK);
    sqlite3_finalize(stmt);
  }




  // Insert once into THIRD table (untouched before) using FIRST handle (which is still doing a select in another thread somewhere)
  {
    sqlite3_stmt* stmt;
    REQUIRE(sqlite3_prepare_v3(FIRST_handle,
insert_into_THIRD.c_str(), -1, 0, &stmt, nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK);
    REQUIRE(sqlite3_step(stmt) == SQLITE_BUSY);
    REQUIRE(sqlite3_extended_errcode(FIRST_handle) == SQLITE_BUSY_SNAPSHOT);
    REQUIRE(sqlite3_reset(stmt) == SQLITE_BUSY);
    REQUIRE(sqlite3_extended_errcode(FIRST_handle) == SQLITE_BUSY_SNAPSHOT);
    sqlite3_finalize(stmt);
  }



  // Stop select on FIRST handle and FIRST table in other thread
  shall_continue_read_from_FIRST = false;
  thread_read_from_FIRST.join();



  // Retry insert into THIRD table using FIRST handle
  {
    sqlite3_stmt* stmt;
    REQUIRE(sqlite3_prepare_v3(FIRST_handle,
insert_into_THIRD.c_str(), -1, 0, &stmt, nullptr) == SQLITE_OK);
    REQUIRE(sqlite3_bind_int64(stmt, 1, u(engine)) == SQLITE_OK);
    REQUIRE(sqlite3_step(stmt) == SQLITE_DONE);
    REQUIRE(sqlite3_reset(stmt) == SQLITE_OK);
    sqlite3_finalize(stmt);
  }



  std::filesystem::remove(db_name);

}




kind regards,
Kira Backes
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Kira Backes
In reply to this post by Clemens Ladisch
> When you do not use explicit transactions, SQLite will automatically create implicit transactions.

But the documentation only says that an implicit transaction is
created for data-changing queries like INSERT:

https://www.sqlite.org/lang_transaction.html

> Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect

it more or less explicitly says that a SELECT does not need a
transaction. So it should be documented that a SELECT is also an
implicit transaction or that a SELECT statement is promoted to a READ
transaction when a concurrent INSERT statement happens.

mit freundlichen Grüßen,
Kira Backes
_______________________________________________
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: [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Kira Backes
In reply to this post by Hick Gunter
> Your first thread is creating an implicit transaction on connection FIRST by reading from table FIRST.

But what you're saying is different to the documentation:

https://www.sqlite.org/lang_transaction.html

> Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect

I don't understand why you are so defensive against improving the documentation?

mit freundlichen Grüßen,
Kira Backes
_______________________________________________
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: [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Kira Backes
In reply to this post by Hick Gunter
PS:

> Another example: X starts a read transaction using BEGIN and SELECT, then Y makes a changes to the database using UPDATE.


Same goes for this example! We did not start a read transaction with
BEGIN. This part says that you need to start a read transaction with
BEGIN, which we did not do, and the other part says that SELECTs do
*NOT* start an automatic transaction.

So is this maybe even a bug in sqlite? Or the documentation is incorrect?

kind regards,
Kira Backes
_______________________________________________
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: SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Rowan Worth-2
In reply to this post by Kira Backes
On Mon, 12 Aug 2019 at 16:55, Kira Backes <[hidden email]> wrote:

> > When you do not use explicit transactions, SQLite will automatically
> create implicit transactions.
>
> But the documentation only says that an implicit transaction is
> created for data-changing queries like INSERT:
>
> https://www.sqlite.org/lang_transaction.html
>
> > Any command that changes the database (basically, any SQL command other
> than SELECT) will automatically start a transaction if one is not already
> in effect
>

Yeah I see what you mean... That sentence should not be taken in isolation,
but I agree it's misleading! It's clarified a few paragraphs down (after
noting that a "deferred" transaction is the default mode of operation):

Thus with a deferred transaction, the BEGIN statement itself does nothing
> to the filesystem. Locks are not acquired until the first read or write
> operation. The first read operation against a database creates a SHARED
> <https://www.sqlite.org/lockingv3.html#shared_lock> lock and the first
> write operation creates a RESERVED
> <https://www.sqlite.org/lockingv3.html#reserved_lock> lock.
>


I think the initial statement should read:

"Any command that changes or reads the database will automatically start a
transaction if one is not already in effect"

Because the actual SQL command is irrelevant -- you can still run
INSERT/CREATE or other queries which represent write operations on a
database which is EXCLUSIVELY locked by another process, as long as the
query only involves temporary tables (which is kind of a cop-out because
such queries don't have to touch the database, but it just further
highlights the fact that DB access/modification is the crucial component
and not the SQL command).

Btw your original comment said "as far as I can tell this is not documented
anywhere," but the behaviour is unsurprising after understanding sqlite's
locking model, which is documented here:

https://www.sqlite.org/lockingv3.html

-Rowan
_______________________________________________
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: SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Kira Backes
Thank you Rowan,

in the context of *locks* it is kinda documented, but not in the
context of transactions :(

Just have a look at the error description:

https://www.sqlite.org/rescode.html#busy_snapshot

> The SQLITE_BUSY_SNAPSHOT error code is an extended error code for SQLITE_BUSY that occurs on WAL mode databases when a database connection tries to promote a read transaction into a write transaction but finds that another database connection has already written to the database and thus invalidated prior reads.
>
> The following scenario illustrates how an SQLITE_BUSY_SNAPSHOT error might arise:
>
> Process A starts a read transaction on the database and does one or more SELECT statement. Process A keeps the transaction open.
> Process B updates the database, changing values previous read by process A.
> Process A now tries to write to the database. But process A's view of the database content is now obsolete because process B has modified the database file after process A read from it. Hence process A gets an SQLITE_BUSY_SNAPSHOT error.


in my opinion and from what I've read I've never started a READ
transaction. And if I open the transaction documentation it explicitly
says that SELECT statements to not start a read transaction. This is a
different concept from a SHARED lock. If it isn't than it should be
clearly explained and documented that every SHARED lock = READ
transaction.



mit freundlichen Grüßen,
Kira Backes
_______________________________________________
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: [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Hick Gunter
In reply to this post by Kira Backes
So how do you propose to have consistency and isolation if SELECT does not create an automatic transaction if no explicit transaction exists?

Consider:

SELECT <some stuff> FROM <somewhere>;
BEGIN;
UPDATE <somewhere else> SET field = <some stuff> +1;
COMMIT;

If the SELECT and UPDATE statements are not part of the same transaction, there is no guarantee that <some stuff> is still valid by the time you use it in the UPDATE.

Or even better:

SELECT <value1> FROM <somewhere>;
SELECT <value2> FROM <wherever>;

How do you know that the expression <value1> + <value2> ever had that specific value (either one, or both, could have been changed between the two read operations)?

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Kira Backes
Gesendet: Montag, 12. August 2019 11:01
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

PS:

> Another example: X starts a read transaction using BEGIN and SELECT, then Y makes a changes to the database using UPDATE.


Same goes for this example! We did not start a read transaction with BEGIN. This part says that you need to start a read transaction with BEGIN, which we did not do, and the other part says that SELECTs do
*NOT* start an automatic transaction.

So is this maybe even a bug in sqlite? Or the documentation is incorrect?

kind regards,
Kira Backes
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
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: [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Kira Backes
> So how do you propose to have consistency and isolation if SELECT does not create an automatic transaction if no explicit transaction exists?

I think this is a misunderstanding which might explain your first
email reply. I do not propose any change for sqlite, but I wasted 2
days debugging and reading every page in sqlite documentation (locks,
transactions, isolation, error description) did not help me. So I just
want the documentation to be improved. I would change the error
description for SQLITE_BUSY_SNAPSHOT so that it no longer says "read
transaction" but instead says "read transaction or ongoing select
statement". And the first sentence in "transactions" should be changed
to indicated that select statement also create implicit read
transactions.
_______________________________________________
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: [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

test user
You can also use “BEGIN EXCLUSIVE” before any writes; this gets you a write
transaction immediately. If this returns OK all following read/writes will
not return BUSY.

A transaction can upgrade from a read only to a write (when you do a select
followed by an insert).

I think you get the snapshot error when the previous reads in the
transaction no longer reference the HEAD database version because another
connection has written to it.



On Mon, 12 Aug 2019 at 11:03, Kira Backes <[hidden email]> wrote:

> > So how do you propose to have consistency and isolation if SELECT does
> not create an automatic transaction if no explicit transaction exists?
>
> I think this is a misunderstanding which might explain your first
> email reply. I do not propose any change for sqlite, but I wasted 2
> days debugging and reading every page in sqlite documentation (locks,
> transactions, isolation, error description) did not help me. So I just
> want the documentation to be improved. I would change the error
> description for SQLITE_BUSY_SNAPSHOT so that it no longer says "read
> transaction" but instead says "read transaction or ongoing select
> statement". And the first sentence in "transactions" should be changed
> to indicated that select statement also create implicit read
> transactions.
> _______________________________________________
> 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: SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Richard Hipp-3
In reply to this post by Kira Backes
The documentation on transactions at
https://www.sqlite.org/lang_transaction.html was written long, long
ago, apparently long before WAL mode was available, and is in serious
need of updating and improvement.  I'm working on that now

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Kira Backes
Thank you very much, I hope my example which probably many users tap
into unknowingly might help :-)

mit freundlichen Grüßen,
Kira Backes

On Mon, 12 Aug 2019 at 12:58, Richard Hipp <[hidden email]> wrote:

>
> The documentation on transactions at
> https://www.sqlite.org/lang_transaction.html was written long, long
> ago, apparently long before WAL mode was available, and is in serious
> need of updating and improvement.  I'm working on that now
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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: SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

Jens Alfke-2
In reply to this post by Kira Backes

> On Aug 12, 2019, at 12:34 AM, Kira Backes <[hidden email]> wrote:
>
> Our code base does not use
> transactions at all (we have a segmented code base protected by
> mutexes for a whole section, so reads/writes do not conflict ever).

This will really hurt performance of multiple writes, since each write statement will be followed by an implicit commit, which requires some expensive disk I/O and file system flushes.

You’re also losing opportunities for parallelism by using a single connection, as SQLite allows reads on one connection to run concurrently with both reads and writes on another connection.

—Jens
_______________________________________________
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: [EXTERNAL] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

James K. Lowden
In reply to this post by Kira Backes
On Mon, 12 Aug 2019 12:02:33 +0200
Kira Backes <[hidden email]> wrote:

> I would change the error description for SQLITE_BUSY_SNAPSHOT so that
> it no longer says "read transaction" but instead says "read
> transaction or ongoing select statement".

While I agree the documentation could be clarified, that wouldn't be a
particularly good change.  A "read transaction" is, exactly, an
"ongoing select statement".  More precisely, the *execution* of a
SELECT statement is a read transaction, whether or not preceded by
BEGIN TRANSACTION. The person reading the documentation is expected to
know that.  It's defined by SQL, and has nothing to do with the SQLite
implementation.  

I think you actually got burned by multithreading.  Let's just say
you're not the first.  ;-)  

To the extent the documentation could be made clearer, I would suggest
it focus on the interface and not the implementation.  Knowledge of
locks, while interesting, should not be required to use transactions
correctly or to understand SQLITE_BUSY_SNAPSHOT.  

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