Add an autocommit property to sqlite3.Connection with a PEP 249 compliant manual commit mode and migrate

[maggyero]

BPO	39457
Nosy	@malemburg, @bitdancer, @maggyero, @erlend-aasland

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = None
created_at = <Date 2020-01-26.19:17:09.219>
labels = ['type-feature', 'library', '3.9']
title = 'Add an autocommit property to sqlite3.Connection with a PEP 249 compliant manual commit mode and migrate'
updated_at = <Date 2021-01-06.13:21:56.616>
user = 'https://github.com/maggyero'

bugs.python.org fields:

activity = <Date 2021-01-06.13:21:56.616>
actor = 'zzzeek'
assignee = 'none'
closed = False
closed_date = None
closer = None
components = ['Library (Lib)']
creation = <Date 2020-01-26.19:17:09.219>
creator = 'maggyero'
dependencies = []
files = []
hgrepos = []
issue_num = 39457
keywords = []
message_count = 12.0
messages = ['360732', '360734', '360735', '384383', '384397', '384414', '384417', '384421', '384425', '384485', '384499', '384501']
nosy_count = 7.0
nosy_names = ['lemburg', 'ghaering', 'r.david.murray', 'zzzeek', 'maggyero', 'erlendaasland', 'james.oldfield']
pr_nums = []
priority = 'normal'
resolution = None
stage = None
status = 'open'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue39457'
versions = ['Python 3.9']

• PR: gh-83638: Add sqlite3.Connection.autocommit for PEP 249 compliant behaviour #93823

[maggyero]

In non-autocommit mode (manual commit mode), the sqlite3 database driver implicitly issues a BEGIN statement before each DML statement (INSERT, UPDATE, DELETE, REPLACE) not already in a database transaction, BUT NOT before DDL statements (CREATE, DROP) nor before DQL statements (SELECT) (cf. https://github.com/python/cpython/blob/master/Modules/_sqlite/cursor.c#L480):

    /* We start a transaction implicitly before a DML statement.
       SELECT is the only exception. See python/cpython#54133. */
    if (self->connection->begin_statement && self->statement->is_dml) {
        if (sqlite3_get_autocommit(self->connection->db)) {
            result = _pysqlite_connection_begin(self->connection);
            if (!result) {
                goto error;
            }
            Py_DECREF(result);
        }
    }

Like Mike Bayer explained in issue bpo-9924, this is not what other database drivers do, and this is not PEP-249 compliant (Python Database API Specification v2.0), as its author Marc-André Lemburg explained (cf. https://mail.python.org/pipermail/db-sig/2010-September/005645.html):

Randall Nortman wrote:
PEP-249 says that transactions end on commit() or rollback(), but it
doesn't explicitly state when transactions should begin, and there is
no begin() method.

Transactions start implicitly after you connect and after you call
.commit() or .rollback(). They are not started for each statement.

I think the implication is that transactions begin
on the first execute(), but that's not explicitly stated. At least
one driver, pysqlite2/sqlite3, does not start a transaction for a
SELECT statement. It waits for a DML statement (INSERT, UPDATE,
DELETE) before opening a transaction. Other drivers open transactions
on any statement, including SELECT.

My question for the DB-SIG is: Can I call it a bug in pysqlite2 that
it does not open transactions on SELECT? Should the spec be amended
to make this explicit? Or are both behaviors acceptable, in which
case perhaps a begin() method needs to be added for when the user
wants control over opening transactions?

I should probably add a note to PEP-249 about this.

Aymeric Augustin said in issue bpo-10740:

While you're there, it would be cool to provide "connection.autocommit = True" as an API to enable autocommit, because "connection.isolation_level = None" isn't a good API at all -- it's very obscure and has nothing to do with isolation level whatsoever.

So I suggest that we introduce a new autocommit property and use it to enable a truly PEP-249 compliant manual commit mode (that is to say with transactions starting implicitly after connect(), commit() and rollback() calls, allowing transactional DDL and DQL):

autocommit = True  # enable the autocommit mode
autocommit = False  # disable the autocommit mode (enable the new PEP 249 manual commit mode)
autocommit = None  # fallback to the commit mode set by isolation_level

I also suggest that we use this new PEP-249 manual commit mode (with transactional DDL and DQL) by default and drop the old manual commit mode (without transactional DDL and DQL). We could use the following migration strategy:

1. During the deprecation period:

• Add the new autocommit property with the value None by default, so that the old manual commit mode is still the default.
• Add a deprecation warning for the value None of the autocommit property, in favor of the other values True and False. It will prompt users who enabled the autocommit mode with isolation_level = None to use autocommit = True instead, and users who disabled the autocommit mode (that is to say users who enabled the old manual commit mode) with isolation_level = DEFERRED/IMMEDIATE/EXCLUSIVE to use autocommit = False instead AND add to their code the potentially missing commit() calls required by the new PEP-249 manual commit mode.

2. After the deprecation period:

• Set the value of the autocommit property to False by default, so that the new PEP-249 manual commit mode becomes the new default.
• Remove the value None of the autocommit property and its deprecation warning.
• Remove the value None of the isolation_level property, so that the old manual commit mode disappears.

[maggyero]

• Remove the value None of the isolation_level property, so that the old manual commit mode disappears.

Correction:

• Remove the value None of the isolation_level property.

[maggyero]

• Remove the value None of the autocommit property and its deprecation warning.

Correction:

• Remove the value None of the autocommit property and its deprecation warning, so that the old manual commit mode disappears.

[jamesoldfield]

If this ever gets implemented, "autocommit" would be a terrible name for it. That word has a very specific meaning in SQLite, which is essentially the same as "not in a transaction started with BEGIN ...". At the moment, if you want to explicitly control when transactions start (a good idea considering how confusing the current behaviour is) then you would set isolation_mode to None and manually start a transaction with execute("BEGIN") - at which point you are NOT in autocommit mode, until you commit or rollback. According to this proposal, if I want manual control over transactions, I would set conn.autocommit = True, even though I don't want autocommit mode (according to SQLite's definition)!

[maggyero]

If this ever gets implemented, "autocommit" would be a terrible name for it. That word has a very specific meaning in SQLite, which is essentially the same as "not in a transaction started with BEGIN ...".

Yes if you are talking about SQLite, the database ENGINE: the SQL statements inside BEGIN and COMMIT are said to be in manual commit mode, while SQL statements outside are said to be in autocommit mode. So the autocommit mode is the default mode for database ENGINES.

But here I am talking about SQLite3, the Python database DRIVER. You do not issue BEGIN statements with database DRIVERS, they are issued implicitly, so that the manual mode is the default mode for database DRIVERS.

Cf. this Stack Overflow answer for more details: https://stackoverflow.com/a/48391535/2326961

At the moment, if you want to explicitly control when transactions start (a good idea considering how confusing the current behaviour is)

No, you do not want that at the database DRIVER level. Because like Mike Bayer explained in issue bpo-9924, this is not what other database DRIVERS do, and this is not PEP-249 compliant (Python Database API Specification v2.0), as its author Marc-André Lemburg explained (cf. https://mail.python.org/pipermail/db-sig/2010-September/005645.html):

Randall Nortman wrote:

PEP-249 says that transactions end on commit() or rollback(), but it

doesn't explicitly state when transactions should begin, and there is

no begin() method.

Transactions start implicitly after you connect and after you call .commit() or .rollback(). They are not started for each statement.

[jamesoldfield]

Yes if you are talking about SQLite, the database ENGINE

I sure was! In this comment I will stick to saying either "SQLite engine" or "sqlite3 driver" as appropriate, hopefully that will be clearer.

But here I am talking about SQLite3, the Python database DRIVER

Yep, I was aware of that. I was trying to say, please don't use the word "autocommit" in the sqlite3 driver when that word has a related but different meaning in the SQLite engine.

You do not issue BEGIN statements with database DRIVERS, they are issued implicitly, so that the manual mode is the default mode for database DRIVERS.

This sentence isn't literally true for several reasons (you say "you do not" but I certainly do, you use of "with database drivers" is dubious, and you seem to have causality in the wrong direction). I think there might be a bit of a language barrier here, so I hope you don't mind if I leave this to one side.

Cf. this Stack Overflow answer for more details: https://stackoverflow.com/a/48391535/2326961

I am fully, and painfully, aware of when the sqlite3 driver code will automatically issue BEGIN statements to the engine. I have no need to read StackOverflow answers about it, I have read the C source code to sqlite3 (and pysqlite) directly. I spent more time than I care to admit recently doing that! In fact that happened as a result of reading several confusing StackOverflow answers about transactions (maybe I'll write my own and add to the confusion...)

What that answer doesn't mention is that, even with even with isolation_mode=None, it's perfectly possible to start a transaction, which takes the SQLite engine out of autocommit mode. This is fully and intentionally supported by the sqlite3 driver, and the original author has said so and even recommended. For example, let's look at this code:

    conn = sqlite3.connect(path, isolation_mode=None)
    conn.execute("INSERT INTO test (i) VALUES (?)", (1,))  # stmt 1
    foo = conn.execute("SELECT * FROM test").fetchall()    # stmt 2
    conn.execute("BEGIN")                                  # stmt 3
    conn.execute("INSERT INTO test (i) VALUES (?)", (4,))  # stmt 4
    bar = conn.execute("SELECT * FROM test").fetchall()    # stmt 5
    conn.execute("COMMIT")                                 # stmt 6

Statement 1 and statement 2 execute using the SQLite engine's autocommit mode. Statements 3 through to 5 execute in a single transaction and do *not* use the SQLite engine's autocommit mode. (Technically statement 6 actually does use autocommit because COMMIT uses the autocommit mechanism under the hood ... but let's forget about that!)

Under your proposal, the first line would be changed to say "autocommit=True", even though not all the code below is in autocommit mode (according to the SQLite engine's definition). What's more, I could insert this line of code between statements 3 and 6:

    print("Autocommit mode?", conn.autocommit)

And it would print True even though autocommit mode is off!

Now, maybe your reaction is that "autocommit mode *in the driver*" can have a different meaning from "autocommit mode *in the engine*". Yes, it can, but that doesn't mean it should! Please, just pick a different name! For example, say "manual mode" (instead of autocommit=True) or "auto-start-transaction mode" (instead of autocommit=False).

No, you do not want that at the database DRIVER level. Because like Mike Bayer explained in issue bpo-9924, this is not what other database DRIVERS do, and this is not PEP-249 compliant

The "that" you are referring to here was when I said that I prefer to set isolation_level = None, like the above code snippet. Do not tell me that it is not what I want; it certainly IS what I want! I do not want the sqlite3 driver getting in the way between me and the SQLite engine. Many future users of the sqlite3 driver are likely to feel the same way, and the API should allow that to happen clearly.

[malemburg]

I think there's a bit of a misunderstanding here. When relying on
a DB-API driver's transaction API, you are not allowed to issue
separate transaction commands to the DB backend via the .execute()
methods. You have to use conn.commit() and conn.rollback().

The DB-API wants drivers to have connections default to transactional
behavior and implicitly start transactions when opening the connection
as well as when ending one (via .commit() or .rollback()) - provided the
backend does support transactions.

It also suggests that a method may be used to set the transactional
behavior.

Now, historically, many drivers have not always used methods for
this, but instead (or in addition) provide a property
connection.autocommit to allow setting or querying the current
state. Over time, this has become more or less a standard.

Aside: This is a bit unfortunate, since users would not expect
exceptions from such properties (e.g. network errors), but this where
things have moved and it's hard to change.

I guess the SQLite driver does not start a new transaction for
SELECTs, since these are usually read-only, but don't know whether
this still holds today (e.g. think of UDFs running INSERTs or UPDATEs).

For the same reason, removing the SELECT "optimization" may cause
a backwards incompatible change, which can be tricky to identify
and cause corruption of data (in this case, data not written to
the database, where it previously was written).

[maggyero]

@james.oldfield

What that answer doesn't mention is that, even with even with isolation_mode=None, it's perfectly possible to start a transaction, which takes the SQLite engine out of autocommit mode.

Exactly, so since autocommit=True is equivalent to isolation_mode=None I do not see why you the name ‘autocommit’ would be a problem. As you said, when you issue BEGIN, you leave autocommit mode.

Under your proposal, the first line would be changed to say "autocommit=True", even though not all the code below is in autocommit mode (according to the SQLite engine's definition).

What is the difference with isolation_mode=None which also means autocommit mode?

What's more, I could insert this line of code between statements 3 and 6:
print("Autocommit mode?", conn.autocommit)
And it would print True even though autocommit mode is off!

No, because the autocommit property would be automatically updated to False at conn.execute("BEGIN"), which is the standard behaviour as @lemburg explained.

@lemburg

I guess the SQLite driver does not start a new transaction for SELECTs, since these are usually read-only

Nor for DDL statements (CREATE, DROP).

For the same reason, removing the SELECT "optimization" may cause
a backwards incompatible change, which can be tricky to identify
and cause corruption of data (in this case, data not written to
the database, where it previously was written).

Since DQL statements (SELECT) are read-only, maybe we could keep the optimization and start transactions implicitly only for DDL statements (CREATE, DROP)?

[zzzeek]

Under your proposal, the first line would be changed to say "autocommit=True", even though not all the code below is in autocommit mode (according to the SQLite engine's definition). What's more, I could insert this line of code between statements 3 and 6:

> print("Autocommit mode?", conn.autocommit)

As Marc-Andre indicated, this is in fact how "autocommit" behaves on other drivers, including:

psycopg2 - uses either connection.autocommit, or extensions.ISOLATION_LEVEL_AUTOCOMMIT
pymysql - uses conn.autocommit(True|False)
mysqldb - uses conn.autocommit(True|False)
cx_Oracle - uses conn.autocommit
pyodbc - uses conn.autocommit

With all of the above drivers, one can emit "BEGIN" and "COMMIT" using
connection.execute(), and within the scope of that BEGIN/COMMIT pair, the
database "engine" itself is in a transaction. The "driver" however remains in
"autocommit" mode. This mode specifically means the driver is not getting
involved in starting and stopping transactions.

As Marc mentions, we're not supposed to be emitting "BEGIN" and "COMMIT" on
the driver, but none of them get in the way of us doing so, and additionally
most databases support additional options for the "BEGIN/START TRANSACTION" phase
that aren't available in the DBAPIs so sometimes we don't have much choice at least for the "BEGIN" command.

Here's an example using psycopg2, where the timestamp now() will freeze
when we're in a transaction started using manual "BEGIN"/ "COMMIT", while .autocommit stays True, and otherwise match statement execution time if we're not:

    >>> import psycopg2
    >>> conn = psycopg2.connect(user="scott", password="tiger", host="localhost", database="test")
    >>> conn.autocommit = True
    >>> cursor = conn.cursor()
    >>> cursor.execute("SELECT 1")
    >>> cursor.execute("select now() = statement_timestamp()")
    >>> cursor.fetchall()
    [(True,)]
    >>> cursor.execute("BEGIN")
    >>> cursor.execute("select now() = statement_timestamp();")
    >>> cursor.fetchall()
    [(False,)]  # we're in a transaction
    >>> conn.autocommit   # still in driver-level autocommit
    True
    >>> cursor.execute("COMMIT")
    >>> cursor.execute("select now() = statement_timestamp();")
    >>> cursor.fetchall()
    [(True,)]

For SQLAlchemy we already support pysqlite's "isolation_level=None" to implement "autocommit" so this issue does not affect us much, but the meaning of the term "autocommit" at the driver level shouldn't be controversial at this point as there's a lot of precedent. "connection.autocommit" does not refer to the current transactional state of the database, just the current preference set upon the driver itself.