为何DDL命令前后执行的DML命令会自动提交？实操场景问询

Great question! This behavior boils down to two key database mechanisms: implicit commits triggered by DDL and (often) the default autocommit mode in your database client. Let’s break down exactly what happened in your workflow:

1. Implicit Commit Before/After DDL Execution #

Nearly all relational databases (MySQL, Oracle, PostgreSQL, etc.) treat DDL statements (like CREATE TABLE) as hard transactional boundaries. Here’s what went down when you ran CREATE TABLE table2:

• The database first checks for any uncommitted transactions in your session (your first INSERT into table1). If there’s one pending, it automatically commits that transaction before executing the DDL.
• The DDL operation itself runs next, and since DDL changes can’t be rolled back in most databases, it gets auto-committed immediately after completion.

So your first INSERT wasn’t left hanging—it got committed silently the moment you executed the CREATE TABLE command.

2. Auto-Commit Mode for Subsequent DML #

Most database clients (like the MySQL command line, psql, or SQL Developer) have autocommit enabled by default. This means that unless you explicitly start a transaction with START TRANSACTION or BEGIN, every single DML statement (like your second INSERT into table1) is treated as its own standalone transaction and gets auto-committed right after execution.

Even if you didn’t run COMMIT manually, that second INSERT was already finalized the moment it finished running. When you logged out, there was no uncommitted data left to lose.

Quick Recap of Your Exact Workflow #

Let’s map this step-by-step:

• Step 1: INSERT INTO table1 ... → If autocommit was enabled (the default), this commits instantly. If you’d disabled autocommit, it stays uncommitted until...
• Step 2: CREATE TABLE table2 → Triggers an implicit commit, locking in the first INSERT (and committing the DDL itself).
• Step 3: INSERT INTO table1 ... → Auto-commits immediately (thanks to default autocommit) or would get implicitly committed when you log out (if you’d turned autocommit off).
• Log out: All data is already committed, so it persists in the database.

If you want to avoid this automatic behavior, explicitly start a transaction with START TRANSACTION before running your DML statements, then only commit when you’re ready. Just note that running any DDL inside an active transaction will still trigger an implicit commit of everything before it.

内容的提问来源于stack exchange，提问作者Can'tCode