Re: [sqlite] problem or not problem around 'begin immediate'
This is a bug in Python, or more correctly it is a luser-error (error code 1D
10T).
If you wish to control transactions explicitly, you need to open the connection
with isolation_level=None.
Otherwise, the sqlite3 (pysqlite) library issues "automagically created"
"begin" statements for you (oftentimes incorrectly). For example, the 1D 10T
talk of not opening transactions for "read only" operations, which is not
correct. Even read only operations are contained within a transaction, it may
just be a transaction that you do not explicitly start or commit. So, if you
issue an explicit "BEGIN" statement when operating in automagical mode (that
is, when isolation_level is any value OTHER THAN None), you may experience
error AHBL.
This has nothing to do with SQLite itself, it is a 1D 10T error in the
interface module design.
(the error thrown in correct -- you cannot start a transaction within a
transaction)
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Big Stone
> Sent: Friday, 25 November, 2016 12:27
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] problem or not problem around 'begin immediate'
>
> there is some discussion about an issue-or-not-an-issue on bugs.python.org
>
> https://bugs.python.org/issue28518
>
> #
>
> conn = sqlite3.connect(':memory:', isolation_level='IMMEDIATE')
> conn.execute('begin immediate')
>
> Throws:
>
> sqlite3.OperationalError: cannot start a transaction within a transaction
>
> This didn't happen in previous versions
>
> #
> Maybe a sqlite developer comment would help settle the case ?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem or not problem around 'begin immediate'
On 25/11/16 12:02, Richard Hipp wrote: > Rather, I presume > that Python has recently started using the sqlite3_stmt_readonly() > interface in a new way. The bigger picture may be helpful. There is a third party module developed under the name "pysqlite" which has a long and storied history. At some point a copy was folded into Python as a module named "sqlite3". There are periodic copies of code changes between the two. Python has a database API specification named DB-API (PEP 249). This mandates common behaviour no matter what the underlying database. Transactions are expected to be started automatically, committed automatically (under some circumstances I think), and commit / rollback are methods on a cursor object. This presumably matches how Postgres, Oracle etc function. Since SQLite doesn't work that way, the pysqlite authors did it manually. The execution code would manually parse each statement, determine what kind of statement it was (makes changes means silently start a transaction) and behave appropriately. Needless to say, parsing statements had various bugs. Eventually they decided to use sqlite3_stmt_readonly() instead of parsing, which is how the current situation arose. ie the API is used to try and simulate the behaviour of other databases. pysqlite does have an option (off by default) to avoid all this silent transaction stuff. Roger signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem or not problem around 'begin immediate'
On 11/25/16, Richard Hipp wrote: > Clarification will be checked in shortly and will appear in the 3.16.0 > release. FWIW, the documentation clarification change is here: https://www.sqlite.org/src/timeline?c=a4205a83 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem or not problem around 'begin immediate'
On 11/25/16, Big Stone wrote:
> there is some discussion about an issue-or-not-an-issue on bugs.python.org
>
> https://bugs.python.org/issue28518
>
> #
>
> conn = sqlite3.connect(':memory:', isolation_level='IMMEDIATE')
> conn.execute('begin immediate')
>
> Throws:
>
> sqlite3.OperationalError: cannot start a transaction within a transaction
>
> This didn't happen in previous versions
>
> #
> Maybe a sqlite developer comment would help settle the case ?
I don't have a login for the python bug tracker so I cannot comment
there. But I think I see the problem. This is as Aviv Polivoda
remarks at https://bugs.python.org/issue28518#msg279808
I think this is a error in sqlite as the documentation says:
"ransaction control statements such as BEGIN, COMMIT, ROLLBACK,
SAVEPOINT, and RELEASE cause sqlite3_stmt_readonly() to return true,"
Except it is not a bug in SQLite, but rather an ambiguity in the
documentation. In his quote, Aviv omitted the second clause from the
documentation: "since the statements themselves do not actually
modify the database but rather they control the timing of when other
statements modify the database." (Full text here:
https://www.sqlite.org/c3ref/stmt_readonly.html)
For a plain BEGIN statement, there are no changes to the database
file, so sqlite3_stmt_readonly() does indeed return TRUE. But for a
BEGIN IMMEDIATE statement, there are database changes, because the
extra IMMEDIATE keyword causes the statement to go ahead and start
transaction "immediately".
So technically, the documentation is correct, though I can certainly
understand that it is misleading and ambiguous as currently worded.
Clarification will be checked in shortly and will appear in the 3.16.0
release.
Note also that behavior of sqlite3_stmt_readonly() has not changed
since that interface was first added for the 3.7.5 release on
2011-02-01. So this is not an SQLite regression. Rather, I presume
that Python has recently started using the sqlite3_stmt_readonly()
interface in a new way.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] problem or not problem around 'begin immediate'
there is some discussion about an issue-or-not-an-issue on bugs.python.org
https://bugs.python.org/issue28518
#
conn = sqlite3.connect(':memory:', isolation_level='IMMEDIATE')
conn.execute('begin immediate')
Throws:
sqlite3.OperationalError: cannot start a transaction within a transaction
This didn't happen in previous versions
#
Maybe a sqlite developer comment would help settle the case ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
