Improve clarity of sqlite3 transaction handling docs #94017

Documentation

The sqlite3 module docs has a section called Controlling Transactions.

There are some improvement possibilities:

• The current docs start out by describing the SQLite autocommit mode. This can be confusing to new users, especially since the DB API (PEP-249) describes its own autocommit behaviour. Suggesting to tone down the SQLite autocommit mode and instead focus on what the sqlite3 module does.
• The docs say that implicit BEGIN statements are issues before DML statements. This is partly true. Implicit BEGIN statements are issued before INSERT, UPDATE, DELETE, and REPLACE statements when executed by execute or executemany, but not by executescript. Other DML statements do not receive such treatment.
• We should update the docs of the execute, executemany, and executescript cursor methods to briefly mention their transaction control peculiarities.
• Since SQLite autocommit mode is mentioned, we should note that it is in fact possible to query this (low-level) mode using the in_transaction connection attribute. Such information can help developers debug transaction control problems.
• Possibly augment the docs of the isolation_level attribute. Currently, the isolation_level property of connection refer to the docs of the isolation_level attribute, which again refer to the Controlling Transactions section. The isolation_level property should have a concise description (reference), and link to the isolation_level attribute. The isolation_level attribute should provide a reference for what it does, and briefly mention expected behaviour. The Controlling Transactions section should provide more background (explanation) to what is happening, and it should briefly mention PEP 249, and the SQLite autocommit mode.
• Revisit any transaction control examples and possibly turn them into clearer how-to's, or guides.