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 #9924. */
    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 #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 #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.