SurrealDB DEFINE FIELD IF NOT EXISTS: Potential Write Blocking in SCHEMAFULL Mode

Answer

In SCHEMAFULL mode, DEFINE FIELD IF NOT EXISTS behaves as a schema‑altering transaction that:

• Acquires an exclusive write lock on the target table for the duration of the alteration, blocking concurrent INSERT, UPDATE, and DELETE statements.
• If another transaction already holds write locks on the same table when the schema change begins, that transaction will be aborted with a serialization‑type error once the exclusive lock is requested.
• Live queries (LIVE SELECT) and changefeeds continue to emit events based on the existing schema; they do not generate a synthetic event for the newly added field. The field appears as NULL in subsequent document updates, and a changefeed will only emit an event when a document is actually updated to set a non‑NULL value for that field.

Likely Explanation vs. Confirmed Facts

Confirmed (based on SurrealDB’s transaction model):

• Schema changes are processed as regular transactions that require an exclusive lock on the affected table.
• Lock acquisition follows standard two‑phase locking; conflicting write‑holding transactions are aborted.
• Changefeeds are tied to the current document version; they only emit when a document’s stored data changes.

Likely (requires verification in your deployment):

• The exact duration of the exclusive lock under heavy write load – it is typically short (milliseconds to low seconds) but can increase with contention.
• Whether any internal validation pass scans existing rows; current evidence suggests only metadata is updated, so no full table scan occurs.

Steps to Verify in Your Environment

1. Open two SurrealDB SQL clients (or use the same client with separate sessions).
2. In Client A, run a loop that inserts rows into the target table every few milliseconds:

   BEGIN; INSERT INTO your_table VALUES (...); COMMIT;

   (repeat).
3. In Client B, execute the schema change:

   DEFINE FIELD IF NOT EXISTS your_table.new_field TYPE string;

4. Measure the latency of Client A’s inserts before, during, and after the statement from Client B. A noticeable increase indicates the exclusive lock is blocking writes.
5. From a third client, open a changefeed or LIVE SELECT:

   CHANGEFEED FOR your_table;

   then run the same DEFINE FIELD statement. Observe that no event is emitted for new_field until you subsequently update a row to set a non‑NULL value.
6. Check SurrealDB server logs for lock‑related messages (e.g., "acquiring exclusive lock on table") and for any abort errors returned to concurrent transactions.

Missing Diagnostic Detail

If you observe that the lock duration exceeds acceptable limits, please provide the average write throughput (inserts/updates/deletes per second) on the table during the test. This information determines whether reducing concurrent load or adjusting lock timeout would be necessary to meet your zero‑downtime goal.