problem or not problem around 'begin immediate'

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

problem or not problem around 'begin immediate'

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

Re: problem or not problem around 'begin immediate'

Richard Hipp-3
On 11/25/16, Big Stone <[hidden email]> 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
[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: problem or not problem around 'begin immediate'

Richard Hipp-3
On 11/25/16, Richard Hipp <[hidden email]> 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
[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: problem or not problem around 'begin immediate'

Roger Binns
In reply to this post by Richard Hipp-3
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


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

signature.asc (169 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: problem or not problem around 'begin immediate'

Keith Medcalf
In reply to this post by big stone

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:[hidden email]]
> On Behalf Of Big Stone
> Sent: Friday, 25 November, 2016 12:27
> To: [hidden email]
> 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
> [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