Default autocommit should be None to avoid unintended transactions

[leowzz]

Describe the bug

Issue Description:

When initializing a connection in aiomysql, the default value of autocommit is explicitly set to False, which overrides the server's default configuration (typically True). This leads to unexpected behavior where transactions are implicitly started but never committed, causing long-running transactions and potential table-locking issues.

Problem Details:

1. Autocommit Mismatch:

   • If the MySQL server has autocommit=True (default), but aiomysql forces autocommit=False during connection initialization, the session enters a state where every SELECT query starts an implicit transaction.

   • Example:

     # aiomysql connection setup with autocommit=False (default)  
     conn = await aiomysql.connect(..., autocommit=False)

     This executes SET autocommit=0 on the server, overriding its default configuration.

   1. Transaction Leak:

      • After executing a query (e.g., SELECT), the transaction remains open because autocommit=False.
      • When releasing the connection back to the pool, aiomysql checks conn.server_status to determine if a transaction is active. However, due to a bug in status tracking, it incorrectly assumes no transaction is active and returns the connection to the pool without committing or rolling back.
      • The open transaction persists in the connection pool, leading to table locks (e.g., schema changes blocked by METADATA LOCK).

   2. Root Cause:

      • The autocommit parameter in aiomysql.connect() defaults to False, conflicting with the server's actual configuration.
      • The library does not respect the server's autocommit value by default, forcing unnecessary transactions.

Proposed Fix:

Set the default value of autocommit to None in aiomysql.Connection, which would:

1. Respect Server Configuration: Do not send SET autocommit=... unless explicitly specified by the user.
2. Avoid Implicit Transactions: If the server defaults to autocommit=True, no transaction is started for read-only queries.

References:

• MySQL Behavior: [autocommit Documentation](https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html)
• Related Code: aiomysql forcibly sets autocommit during initialization

---

Suggested Code Change:
Modify the autocommit default in aiomysql.Connection.__init__ from False to None:

def __init__(..., autocommit=None, ...):  
    ...

This ensures the server's autocommit configuration is respected unless explicitly overridden by the user.

---

Let me know if you need further details or testing assistance! 🙌

To Reproduce

1. mysql server default autocommit=True.
2. Initialize a connection pool without parameter autocommit.
3. Execute a SELECT query and release the connection back to the pool.
4. Observe the transaction status in MySQL (SHOW PROCESSLIST or INFORMATION_SCHEMA.INNODB_TRX), which shows an open transaction even after the connection is "closed".

Expected behavior

Respect Server Configuration: Do not send SET autocommit=... unless explicitly specified by the user.

or commit transaction when release conn to pool

Logs/tracebacks

MySQL mydb@127.0.0.1:mydb> SELECT * FROM `performance_schema`.`metadata_locks` LIMIT 0,100 \G;
***************************[ 1. row ]***************************
OBJECT_TYPE           | TABLE
OBJECT_SCHEMA         | mydb
OBJECT_NAME           | table_name
COLUMN_NAME           | <null>
OBJECT_INSTANCE_BEGIN | 140514755781136
LOCK_TYPE             | SHARED_READ
LOCK_DURATION         | TRANSACTION
LOCK_STATUS           | GRANTED
SOURCE                | sql_parse.cc:6142
OWNER_THREAD_ID       | 902740
OWNER_EVENT_ID        | 3

auto start transaction for select.

Python Version

$ python --version
Python 3.10.14

aiomysql Version

$ python -m pip show aiomysql
Name: aiomysql
Version: 0.2.0
Summary: MySQL driver for asyncio.
Home-page: https://github.com/aio-libs/aiomysql
Author: Nikolay Novik
Author-email: nickolainovik@gmail.com
License: MIT

PyMySQL Version

$ python -m pip show PyMySQL
Name: PyMySQL
Version: 1.1.1
Summary: Pure Python MySQL Driver
Home-page: 
Author: 
Author-email: Inada Naoki <songofacandy@gmail.com>, Yutaka Matsubara <yutaka.matsubara@gmail.com>
License: MIT License

SQLAlchemy Version

$ python -m pip show sqlalchemy
Name: SQLAlchemy
Version: 1.4.54
Summary: Database Abstraction Library
Home-page: https://www.sqlalchemy.org
Author: Mike Bayer
Author-email: mike_mp@zzzcomputing.com
License: MIT

OS

Linux diaochan.huoban.ai 5.15.0-125-generic #135-Ubuntu SMP Fri Sep 27 13:53:58 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux
or
Darwin Mac.local 24.4.0 Darwin Kernel Version 24.4.0: Wed Mar 19 21:16:34 PDT 2025; root:xnu-11417.101.15~1/RELEASE_ARM64_T6000 arm64

Database type and version

SELECT VERSION();
 8.0.40-0ubuntu0.22.04.1

Additional context

No response

Code of Conduct

• I agree to follow the aio-libs Code of Conduct

[liujiaqi7998]

Yes, I also encountered this problem.

[leowzz]

The issue is likely related to the validation of connections when the connection pool releases them, as the default setting for autocommit is false according to the PEP-0245
specification.

ref pymysql's issue #1205