SQLITE_ERROR instead of SQLITE_BUSY or clarification of busy state errors

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

SQLITE_ERROR instead of SQLITE_BUSY or clarification of busy state errors

mailing lists
Hi,

I face the following issue:

1) SQLite has been compiled with SQLITE_THREADSAFE=1 and SQLITE_DEFAULT_SYNCHRONOUS=3
2) I am opening in a thread a new database (standard journal mode) and creating some tables, indices etc. (explicit transaction)
3) while creating the database a new database connection (read only) is established in another thread that tries to get some data by a prepared statement.

In this second thread (step (3)) I get an SQLITE_ERROR in sqlite3_prepare. I actually expected an SQLITE_BUSY error. Remark: as step (2) is a transaction no tables exist when step (3) starts execution.

Is my understanding correct that I only get an SQLITE_BUSY error when actually trying to run a query? In all other cases I should get different error codes, or?

Regards,
Hardy
_______________________________________________
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_ERROR instead of SQLITE_BUSY or clarification of busy state errors

Simon Slavin-3
On 30 Oct 2019, at 10:33pm, mailing lists <[hidden email]> wrote:

> In this second thread (step (3)) I get an SQLITE_ERROR in sqlite3_prepare. I actually expected an SQLITE_BUSY error. Remark: as step (2) is a transaction no tables exist when step (3) starts execution.
>
> Is my understanding correct that I only get an SQLITE_BUSY error when actually trying to run a query? In all other cases I should get different error codes, or?

I cannot immediately solve your problem, but here are some things you didn't mention which might help.

Did you test it to see that if the other program (the one which writes) isn't running you don't get the error ?

SQLite has two locking errors: SQLITE_BUSY and SQLITE_LOCKED.

In order to prepare a statement, SQLite has to read the database.  It does this because preparing a statement requires it to know the layout of the table and which indexes are available.  If another connection has the database locked, SQLite cannot do this.

When you tell SQLite to open a database it does not do it.  It reads in the schema (structures of tables and indexes) only when you do the first thing that needs them.  So if you're writing a small test program, it might not be doing things when you expect it to.

If you are doing this as a test, that's fine.  If you are writing software for production use, then both programs should be setting a timeout of at least a few seconds:

<https://sqlite.org/c3ref/busy_timeout.html>
_______________________________________________
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_ERROR instead of SQLITE_BUSY or clarification of busy state errors

Keith Medcalf
In reply to this post by mailing lists

On Wednesday, 30 October, 2019 16:33, mailing lists <[hidden email]> wrote:

>I face the following issue:

>1) SQLite has been compiled with SQLITE_THREADSAFE=1 and
>SQLITE_DEFAULT_SYNCHRONOUS=3
>2) I am opening in a thread a new database (standard journal mode) and
>creating some tables, indices etc. (explicit transaction)
>3) while creating the database a new database connection (read only) is
>established in another thread that tries to get some data by a prepared
>statement.

>In this second thread (step (3)) I get an SQLITE_ERROR in
>sqlite3_prepare. I actually expected an SQLITE_BUSY error. Remark: as
>step (2) is a transaction no tables exist when step (3) starts execution.

That is because your SQL statement has an error.  By the sounds of it the table(s) you are trying to query do not exist because you have not yet committed the transaction which is creating them.  

Key hints "new database" (contains nothing), creating tables etc in an explicit transaction, and while creating the database ... preparing a statement that reads the database fails with an error.

>Is my understanding correct that I only get an SQLITE_BUSY error when
>actually trying to run a query? In all other cases I should get different
>error codes, or?

Yes.

--
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_ERROR instead of SQLITE_BUSY or clarification of busy state errors

mailing lists
Hi,

what I do not really understand is why the statement in step (3) is allowed to read the database at all and not aborts by a SQLITE_BUSY error. The transaction in step (2) is modifying the database, in my case the statement is creating the database. But assume that step (2) is modifying it only, so step (3) - that tries to read the database during modification - is potentially incorrect anyway.

Nevertheless, it is probably just a misinterpretation of the error messages from my side.

Regards,
Hardy

> Am 2019-10-31 um 01:53 schrieb Keith Medcalf <[hidden email]>:
>
>
> On Wednesday, 30 October, 2019 16:33, mailing lists <[hidden email]> wrote:
>
>> I face the following issue:
>
>> 1) SQLite has been compiled with SQLITE_THREADSAFE=1 and
>> SQLITE_DEFAULT_SYNCHRONOUS=3
>> 2) I am opening in a thread a new database (standard journal mode) and
>> creating some tables, indices etc. (explicit transaction)
>> 3) while creating the database a new database connection (read only) is
>> established in another thread that tries to get some data by a prepared
>> statement.
>
>> In this second thread (step (3)) I get an SQLITE_ERROR in
>> sqlite3_prepare. I actually expected an SQLITE_BUSY error. Remark: as
>> step (2) is a transaction no tables exist when step (3) starts execution.
>
> That is because your SQL statement has an error.  By the sounds of it the table(s) you are trying to query do not exist because you have not yet committed the transaction which is creating them.  
>
> Key hints "new database" (contains nothing), creating tables etc in an explicit transaction, and while creating the database ... preparing a statement that reads the database fails with an error.
>
>> Is my understanding correct that I only get an SQLITE_BUSY error when
>> actually trying to run a query? In all other cases I should get different
>> error codes, or?
>
> Yes.
>
> --
> 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
> [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