sqlite3_get_autocommit() changes value when sqlite3_step() returns SQLITE_BUSY

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

sqlite3_get_autocommit() changes value when sqlite3_step() returns SQLITE_BUSY

Nic Ramage
Hi,

I think I have found a regression/bug in Sqlite with respect to
the sqlite3_get_autocommit() function.

Sometimes, when multiple connections are used in multiple threads,
sqlite3_get_autocommit()  reports that the connection has a transaction
open (i.e. not in auto commit mode), even though sqlite3_step() returns
SQLITE_BUSY.  I have verified that sqlite3_get_autocommit()  reports that
there is no transaction open before sqlite3_step() is called.  The problem
seems to be specific to the WAL journal mode, as I haven't been able to
reproduce it for

Initially, the problem only showed up under Linux, but I have now been able
to reproduce the problem on Windows too.  For me it shows up quicker under
linux, but that may just be because of the difference in environments.  I
have attached a single-file C++11 application that consistently reproduces
the problem for me.

I was also able to bisect the introduction of the problem to sqlite
v3.17.0.  v3.16.2 does not have the problem.  The latest 3.24.0 release
does.  I am not an expert on the sqlite source code, but during my
debugging, I was able to determine that the auto commit flag was being
cleared in "case OP_AutoCommit", which returns without error.  Obviously,
something else must be causing the  SQLITE_BUSY afterwards, but is not
resetting the auto commit flag.

In my code, I have worked around the problem by issuing a "rollback"
whenever I detect the problem described.

I hope this is enough information to be able to solve the bug.  If,
however, this behaviour turns out not to be a bug, but something that is
expected to happen occasionally, then the "rollback" provides a reasonable
way to mitigate the issue.

Regards
Nic

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

test.cpp (6K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: sqlite3_get_autocommit() changes value when sqlite3_step() returns SQLITE_BUSY

Dan Kennedy-4
On 09/12/2018 08:56 PM, Nic Ramage wrote:
> Hi,
>
> I think I have found a regression/bug in Sqlite with respect to
> the sqlite3_get_autocommit() function.



Hi,

Thanks for putting in the work to create the bug demo. Bisecting the
latest SQLite commits using it shows that this bug was fixed here:

   https://www.sqlite.org/src/info/e6108047cb136119

Fix will appear in 3.25.0 (currently undergoing release testing).

Dan.





>
> Sometimes, when multiple connections are used in multiple threads,
> sqlite3_get_autocommit()  reports that the connection has a transaction
> open (i.e. not in auto commit mode), even though sqlite3_step() returns
> SQLITE_BUSY.  I have verified that sqlite3_get_autocommit()  reports that
> there is no transaction open before sqlite3_step() is called.  The problem
> seems to be specific to the WAL journal mode, as I haven't been able to
> reproduce it for
>
> Initially, the problem only showed up under Linux, but I have now been able
> to reproduce the problem on Windows too.  For me it shows up quicker under
> linux, but that may just be because of the difference in environments.  I
> have attached a single-file C++11 application that consistently reproduces
> the problem for me.
>
> I was also able to bisect the introduction of the problem to sqlite
> v3.17.0.  v3.16.2 does not have the problem.  The latest 3.24.0 release
> does.  I am not an expert on the sqlite source code, but during my
> debugging, I was able to determine that the auto commit flag was being
> cleared in "case OP_AutoCommit", which returns without error.  Obviously,
> something else must be causing the  SQLITE_BUSY afterwards, but is not
> resetting the auto commit flag.
>
> In my code, I have worked around the problem by issuing a "rollback"
> whenever I detect the problem described.
>
> I hope this is enough information to be able to solve the bug.  If,
> however, this behaviour turns out not to be a bug, but something that is
> expected to happen occasionally, then the "rollback" provides a reasonable
> way to mitigate the issue.
>
> Regards
> Nic
>
>
>
> _______________________________________________
> 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: sqlite3_get_autocommit() changes value when sqlite3_step() returns SQLITE_BUSY

Detlef Golze
This also fixes the problem I mentioned here:
https://www.mail-archive.com/sqlite-users@.../msg108340.html

(BEGIN IMMEDIATE fails with "cannot start a transaction within a transaction (1)" without nested transsactions)

Thanks,
Detlef.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users <[hidden email]> Im Auftrag von Dan Kennedy
Gesendet: Donnerstag, 13. September 2018 17:30
An: [hidden email]
Betreff: Re: [sqlite] sqlite3_get_autocommit() changes value when sqlite3_step() returns SQLITE_BUSY

On 09/12/2018 08:56 PM, Nic Ramage wrote:
> Hi,
>
> I think I have found a regression/bug in Sqlite with respect to
> the sqlite3_get_autocommit() function.



Hi,

Thanks for putting in the work to create the bug demo. Bisecting the
latest SQLite commits using it shows that this bug was fixed here:

   https://www.sqlite.org/src/info/e6108047cb136119

Fix will appear in 3.25.0 (currently undergoing release testing).

Dan.





>
> Sometimes, when multiple connections are used in multiple threads,
> sqlite3_get_autocommit()  reports that the connection has a transaction
> open (i.e. not in auto commit mode), even though sqlite3_step() returns
> SQLITE_BUSY.  I have verified that sqlite3_get_autocommit()  reports that
> there is no transaction open before sqlite3_step() is called.  The problem
> seems to be specific to the WAL journal mode, as I haven't been able to
> reproduce it for
>
> Initially, the problem only showed up under Linux, but I have now been able
> to reproduce the problem on Windows too.  For me it shows up quicker under
> linux, but that may just be because of the difference in environments.  I
> have attached a single-file C++11 application that consistently reproduces
> the problem for me.
>
> I was also able to bisect the introduction of the problem to sqlite
> v3.17.0.  v3.16.2 does not have the problem.  The latest 3.24.0 release
> does.  I am not an expert on the sqlite source code, but during my
> debugging, I was able to determine that the auto commit flag was being
> cleared in "case OP_AutoCommit", which returns without error.  Obviously,
> something else must be causing the  SQLITE_BUSY afterwards, but is not
> resetting the auto commit flag.
>
> In my code, I have worked around the problem by issuing a "rollback"
> whenever I detect the problem described.
>
> I hope this is enough information to be able to solve the bug.  If,
> however, this behaviour turns out not to be a bug, but something that is
> expected to happen occasionally, then the "rollback" provides a reasonable
> way to mitigate the issue.
>
> Regards
> Nic
>
>
>
> _______________________________________________
> 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users