How one connection kills a database

Think you could land a DBA job at GitHub?

You've just been paged. Every query is failing to execute. Find out why.

Years ago at GitHub, this was a question Sam Lambert would ask in interviews.

The solution involved a downed database that started with a MySQL schema change needing an exclusive lock on a table, but it couldn't get one. Another session had already touched that table and never committed. That open transaction blocked the schema change.

Once the schema change was waiting, new queries on the same table queued up behind it. Nothing was deadlocked. Nothing would time out on its own. The whole pile-up sat there until someone found the connection at the top of the chain and killed it.

(By the way, you'd avoid this MySQL lock on PlanetScale Vitess since ALTER would have been an online schema change, copying in the background and only taking a brief lock at cutover.)

This isn't rare, and it isn't exclusive to MySQL. You can reproduce the same pile-up on Postgres just as easily.

Want to see a stuck database?

You won't need elevated permissions. A normal application connection is enough.

All it takes is an unhandled exception and a poorly timed migration.

Say you have a Postgres database with a hot table named orders.

On one connection, a transaction begins a SELECT query, but the app throws an exception.

-- Connection A: Ordinary application user
BEGIN;

SELECT * FROM orders WHERE id = 123;

-- ...then the app throws an exception 💀

The SELECT query finished, but Postgres keeps holding the connection, waiting for the transaction to end with COMMIT or ROLLBACK, which it never does because of the app's thrown exception.

While Postgres waits on that connection, another connection attempts to run a migration.

-- Connection B: Migration role waiting on lock
ALTER TABLE orders ADD COLUMN foo integer;

The SELECT query only needed a read lock (ACCESS SHARE). The migration needs a lock that blocks all other access (ACCESS EXCLUSIVE), so it waits.

Postgres will not let later queries jump the queue. Every new query on orders waits behind the already-stuck migration, which is itself stuck behind the initial SELECT connection that never closed.

-- Connections C, D, E, etc: Waiting on the lock
SELECT *
FROM orders
WHERE customer_id = ?;

If the app doesn't handle errors gracefully and depends on quick responses from orders, you've got downtime.

Not because of a hack, but because one unhandled exception prevented a COMMIT, kept a connection open, blocked a migration, and that migration blocked everything else.

By the way, this is preventable. It happens because the default for idle_in_transaction_session_timeout is disabled. Give it a value and Postgres times out transactions that haven't closed.

Freeing a stuck database

Debugging the state of connections in MySQL and Postgres can be clunky.

As covered in See what your database is doing right now with Connections it is possible to run a query on a loop in your terminal to see active connections. But it's not an ideal interface.

It also depends on your database having spare connection slots. The worst-case scenario is your database is overwhelmed with connections that haven't been closed, meaning even you can't connect to debug and kill them.

Because this experience is so terrible, we've added the ability to view and kill connections in Postgres and MySQL databases on PlanetScale.

Connections in the dashboard

PlanetScale's connections tooling uses a reserved administrative connection so it can still show you a list of active connections and processes even when connections are exhausted and your application can't connect.

In the PlanetScale dashboard, you can now click the Connections tab to view a live list of connections. You'll see a bunch of useful columns like Process ID, State, and Duration, but most importantly, you will see a column for Blocked Queries.

From here, it's easy to see which connections are currently blocking others from proceeding. You can click into any connection to see more details, such as the query currently being performed by that connection.

Kill process gives you three options, from least to most disruptive:

• Cancel query stops the running statement and leaves the connection open, so the app can send another query
• Terminate transaction rolls back the open transaction and closes the connection, but only if that same transaction is still running
• Terminate connection drops the backend entirely, whether it is idle or mid-query

Thinking back to the downtime example, the idle SELECT had already finished, so canceling the query will not release the lock. In that scenario you would need to terminate the transaction or the connection.

Over on Vitess, each process has two options: cancel the query or terminate the connection. In a sharded database, you can view the process list per keyspace and shard.

Connections via CLI

If you'd rather work with a terminal UI, launch:

pscale branch connections top <database> <branch>

This opens an interactive live view that refreshes about once every second. The most important connections are sorted toward the top. You can navigate through the view using keyboard shortcuts and open any connection to see more details.

If your agents prefer the current state of connections in JSON, use this one-shot command instead of the live view:

pscale branch connections show <database> <branch> --format json

That output includes the IDs needed to cancel a query or terminate a connection, which you can still require confirmation for. See the CLI reference for the kill commands that go with it.

Migrate today, stress less later

The correct answer to land your dream job today is "Use PlanetScale."

Next time you're paged because everything is erroring, I hope you've already migrated over. The interview answer hasn't changed, but connection management on PlanetScale has made it easier than ever before.