What Happens on the MySQL Server When the Client Disconnects Abruptly?

We examine a scenario where a MySQL client sends a DML/DDL statement, the server begins executing it, and the client terminates the TCP connection without any prior notice. The article explores how the server reacts once it eventually detects the broken connection.

1. Comparing Two Scenarios

Two fictional stories illustrate the difference between a client pressing Ctrl+C to interrupt a query (the previous article) and a client simply disappearing after sending a query.

2. Client Disappears

The client sends a SQL statement, the server starts processing it, and before the query finishes the client closes the socket. During execution the server cannot sense the disconnection and continues working on the query.

Such a situation only occurs when a program connects to MySQL, finishes execution or crashes without explicitly closing the database connection.

3. What Does the Server Do?

3.1 Commit First, Then Attempt Rollback

If the server executes a DDL statement, or a DML statement with auto_commit=ON, the transaction is automatically committed when the statement finishes.

After committing, the server tries to send the result to the client via the send() system call. send() can behave in two ways:

It writes the result into the socket buffer and returns success; the server does not yet notice the client has gone.

It directly transmits the result to the client; the server immediately learns the connection is broken.

Regardless of which behavior occurs, the server eventually detects the disconnection when it attempts to read the next command, at which point it attempts to roll back the transaction. Because the transaction was already committed, the rollback has no effect, and the data changes remain permanent.

3.2 Rollback Transaction

If the server is executing a DML statement inside an explicit transaction ( BEGIN or START TRANSACTION) or with auto_commit=OFF, the transaction is not committed automatically.

After the statement finishes, the server again calls send() to return the result. The same two send() behaviors apply, and the server eventually detects the broken socket.

Upon detection, the server rolls back the transaction. Since no commit has occurred, the rollback succeeds and the data modifications are discarded, as if the DML never ran.

4. Summary

If the server runs a DDL statement, it succeeds and the changes are persisted.

If the server runs a DML statement with auto_commit=ON, it also succeeds and the changes are persisted.

If the server runs a DML statement inside an explicit transaction or with auto_commit=OFF, the transaction is rolled back and no changes are applied.

5. Open Issues

The two possible send() behaviors are determined by the operating system kernel, and the exact mechanism is not yet fully understood.