Read-only and query cancellation have to reach the database server

OwlAI's SQL execution tool runs SQL written by the model against a customer's production database. Some limits set on the client side look like they are in effect but never reach the database server. Read-only set through a connection option disappears behind a connection pooler; in our test, CREATE TABLE ran anyway. A driver's socket timeout drops the connection and leaves the server's query running. And when the user stops an answer but only the agent loop learns about it, a query already sent to the database keeps running until its 600-second time budget runs out.

So all three limits, blocking writes, capping time and cancelling, are set through mechanisms the database server enforces itself. Read-only goes in as the first statement of every transaction, the time cap is also set on the server, and cancellation is sent as a cancel call each engine's server understands. This post covers what we set where for each engine, and the path a stopped answer takes to reach the running query.

#Connection options and session settings never reach the database connection behind a pooler

Blocking words such as INSERT and DELETE in the SQL string doesn't hold up for long. Functions that write differ by engine, so the list never ends. We leave rejecting writes to the database server.

The easy way to make the server read-only is to pass it as a connection option or send a session-level SET once. If the client connects to the database directly, that is enough. A connection pooler such as PgBouncer, Supavisor or ProxySQL in between changes things. A transaction-mode pooler hands a client one server connection only for the length of a transaction and returns it to the pool when the transaction ends. The outcome depends on where the setting goes.

A driver's read-only flag is no guarantee either. We marked the Tibero JDBC driver read-only and ran a write, and the write succeeded. Whether the server enforces the driver's flag is up to the engine.

#Read-only is the first statement of every transaction

The SQL execution tool opens a new transaction for each SQL execution and makes read-only its first statement. The model's SQL runs inside that transaction, and the database server rejects any write with an error.

On MariaDB, MySQL and Oracle, if read-only cannot be set, nothing runs on that connection. Running without it would leave account privileges as the only defense.

A transaction-scoped setting holds only if a single SQL statement runs inside that transaction. PostgreSQL has a simple query protocol that runs several statements joined by ; in one go, so the execution tool sends SQL only over the extended protocol, which takes one statement at a time.

Oracle has one more way to write outside a read-only transaction. A function declared with PRAGMA AUTONOMOUS_TRANSACTION runs in an autonomous transaction separate from its caller, so a write inside a function called from a SELECT gets committed. That is why the Oracle executor first sets ALTER SESSION DISABLE COMMIT IN PROCEDURE on every connection. Commits inside functions and procedures are then rejected with ORA-00034, and an autonomous transaction cannot commit. If this setting cannot be applied, nothing runs on that connection either.

What a read-only transaction blocks varies by engine, and some engines, like Tibero, do not accept one at all. So the last boundary is the connection account. The Before you connect area of the database connection dialog shows, per engine, the SQL for creating a read-only account and tells you to connect with an account that has query (SELECT) privileges only.

#The time cap is also set on the server, and socket timeouts are not trusted with it

Each SQL execution has a time budget. The value is the installation setting timeouts.dbQuerySeconds (600 seconds by default), and the model cannot change it through tool arguments. A watcher thread that starts alongside the SQL enforces the budget. Every 0.1 seconds it checks the elapsed time and the cancellation signal, and when the budget runs out or the signal is set, it calls the engine's cancel function.

In case the watcher fails to cancel, a second time cap is set on the database server. The server-side cap is set 5 seconds past the budget, so in normal operation the watcher stops the statement first.

Each cap covers the other's gaps. MySQL's max_execution_time applies only to read-only SELECT statements, not to statements inside stored functions; those are stopped by the KILL QUERY the watcher sends. Conversely, if the watcher's cancel never reaches the server, the server-side cap cuts the statement off.

The driver's socket read timeout is not used as a server-side cap. In our test with the MariaDB driver, the socket timeout only dropped the client's connection while the server's query kept running.

Why a statement stopped is decided by the flag the watcher sets, not by error messages that differ from driver to driver. Even if a statement finishes without an error, its result is discarded when the watcher sent a cancel in the meantime. Some cancellations end without an error, and keeping that result would pass a partially read set of rows to the model as if it were complete. The model receives a result saying the query was cancelled or timed out, and on a timeout it decides whether to rewrite the SQL.

#Cancellation goes to each engine in a form its server understands

The cancel call the watcher makes differs by engine.

By default the Oracle driver sends a break as TCP urgent data, that is, out-of-band. NATs and proxies often drop that data. With Oracle 18c XE behind a port mapping, a cancel on default settings had not stopped the query after 60 seconds, and a call timeout set to 20 seconds took 30 to 40 seconds to cut it off. So we send the break in-band. In the same environment, an in-band break stopped the query in 1 to 3 seconds.

#The stop signal travels all the way to the running query

The agent loop checks for cancellation when it starts a round (one model call plus running the tool calls in its response) and when it skips tool calls that haven't started yet. If only the loop checks, SQL already sent to the database never learns it was cancelled. When we stopped a subagent in an isolated environment, one round kept going for 14 minutes until two queries already in flight (102 seconds and 615 seconds) finished.

So the cancellation signal goes all the way to the tool handler. While it runs, the loop puts its cancellation signal in a Python context variable (contextvars). Context variables follow execution into threads started with asyncio.to_thread, so the SQL execution tool can read the current loop's signal on whatever thread it runs. Passing the signal as a function argument would make every function between the loop and the tool accept it and pass it on.

The SQL execution tool watches two signals. One comes from its caller (a chat turn's cancellation, or a schema analysis job's cancellation), and the other is the current loop's signal. The tool creates a single thread event that is set as soon as either signal is set, hands it to the watcher, and checks both signals every 0.25 seconds. If the coroutine waiting for the SQL result is cancelled, the same event is set.

The loop ends on cancellation too. If cancellation arrives while tools are running, the remaining tool calls are closed with cancelled results, so every call still has exactly one result in the history. The loop then ends on the spot. If cancelled calls counted as consecutive failures, the loop would send one more model call asking for an answer from what it had gathered, and a cancelled turn has no answer to write.

Because this path lives in the loop, stopping chat, a playbook, schema analysis or a deep research researcher also stops queries already sent (“One agent loop for every feature, with the differences in hooks”).

#Design choices

We left write blocking to the engine's read-only transaction and the account's privileges, so how strong the defense is varies by engine. On Tibero the account's privileges are the main boundary, and if the customer gives the account write privileges, that much is open. A blocklist works without knowing the database type, which is its advantage, but the list differs by engine and never ends. That is why the connection dialog shows each engine's read-only account SQL alongside.

Every engine opens a new connection for each SQL execution and discards it afterward. Even on MariaDB and MySQL, where the time cap is a session value, that value never carries over to the next call or to another client, and read-only and the time cap always apply only to that call's transaction. The cost is connection time on every execution.

Cancellation is passed along by checking signals at short intervals. After a stop, the watcher notices within 0.35 seconds at most, and on the engines we tested, the query took another 1 to 3 seconds to stop. A person pressing stop does not notice that delay, so we chose the simple design of merging both signals into one event.

If you send AI-written SQL to a database behind a connection pooler, or to a production database where one query can run for minutes, start by checking which server connection each setting actually lands on. Set read-only and the time cap at transaction scope, send cancellation through the engine's own cancel call, and use socket timeouts only to clean up dropped connections.