# Secret store and database write ordering: what a failure leaves behind, and how a retry resumes > Without a shared transaction, write order affects the state left after failure. SQLite examples cover secret overwrites, pending rows and retries. Source: https://sheng.page/en/posts/secret-store-and-database-write-ordering/ · Published: 2026-11-02 · Author: Sheng Consider a tenant-creation operation that writes a database row and a signing secret to a separate store. If each store commits independently, one write can succeed while the other fails. The order affects which data is left in an incomplete or incorrect state, but recognising that state and resuming the operation still need their own design. This post stays on the two stores and how they fit together. Which fields the signature covers and how keys rotate are covered in [What to sign in HMAC requests](/en/posts/what-to-sign-in-hmac-requests/) and are not repeated here. ## What each order leaves behind The example receives an API key, inserts a row with that key under a unique constraint, and writes the matching secret to the store. The database rejects duplicate keys. For this example, the secret store overwrites an existing value without rejecting the write; that behaviour needs verification for any other store. Order one: write the secret, then insert the row. If the API key already belongs to another tenant, the INSERT hits the unique constraint, but the secret store was written a step earlier, and the existing tenant's secret has been replaced. The example below uses the sqlite3 module from the Python standard library as the database and a plain dict as the secret store. The dict is deliberate: the example provides no rollback for the dict, modelling a store whose writes cannot be undone by the database transaction. ```python import sqlite3 db = sqlite3.connect(":memory:") db.execute("CREATE TABLE tenants (api_key TEXT PRIMARY KEY, name TEXT)") secrets = {} # stand-in for a file-based secret store: no transaction, a write is final def create_tenant_secret_first(api_key, name, secret): secrets[api_key] = secret # step 1: write the secret db.execute("INSERT INTO tenants VALUES (?, ?)", (api_key, name)) db.commit() # step 2: insert the row create_tenant_secret_first("key-A", "tenant-1", "s3cret-of-tenant-1") print("before:", secrets["key-A"]) try: create_tenant_secret_first("key-A", "tenant-2", "s3cret-of-tenant-2") except sqlite3.IntegrityError as e: print("insert failed:", e) print("after: ", secrets["key-A"]) print("rows: ", db.execute("SELECT * FROM tenants").fetchall()) ``` ```text before: s3cret-of-tenant-1 insert failed: UNIQUE constraint failed: tenants.api_key after: s3cret-of-tenant-2 rows: [('key-A', 'tenant-1')] ``` The row still belongs to tenant-1, but its secret has been replaced with tenant-2's value. If the API maps the unique-constraint error to a 409, the caller sees a failed create request without necessarily learning that tenant-1's key has changed. When tenant-1 starts failing verification depends on when the verifier reads the new value. A cache or an old-key grace period could delay the failure and make it harder to trace back to the create request. Order two: insert the row, commit, and only then write the secret. A duplicate key is rejected at the first step and the store is never touched. ```python import sqlite3 db = sqlite3.connect(":memory:") db.execute("CREATE TABLE tenants (api_key TEXT PRIMARY KEY, name TEXT)") secrets = {} def create_tenant_commit_first(api_key, name, secret): db.execute("INSERT INTO tenants VALUES (?, ?)", (api_key, name)) db.commit() # the row exists only once commit returns secrets[api_key] = secret # write the secret afterwards create_tenant_commit_first("key-A", "tenant-1", "s3cret-of-tenant-1") print("before:", secrets["key-A"]) try: create_tenant_commit_first("key-A", "tenant-2", "s3cret-of-tenant-2") except sqlite3.IntegrityError as e: print("insert failed:", e) print("after: ", secrets["key-A"]) print("rows: ", db.execute("SELECT * FROM tenants").fetchall()) ``` ```text before: s3cret-of-tenant-1 insert failed: UNIQUE constraint failed: tenants.api_key after: s3cret-of-tenant-1 rows: [('key-A', 'tenant-1')] ``` In this example, a duplicate key no longer overwrites an existing tenant's secret. A failure can still occur after the row commits, leaving a tenant without a secret. If the system can identify and handle that incomplete row, the effect is easier to contain. Changing the write order alone does not finish the design. ## Settle the commit boundary before the order "Insert first" needs a precise meaning of "the insert succeeded". In autocommit mode, confirm that INSERT has completed and committed successfully. Inside a transaction, INSERT returning only means that statement was accepted; the COMMIT that follows can still fail. The secret should be written only after COMMIT is confirmed successful. Writing it after INSERT and before COMMIT puts you right back in order one. SQLite in rollback journal mode can demonstrate "INSERT succeeded, COMMIT failed". A second connection is part-way through reading the same file and holds its lock. The writer's INSERT goes through, and COMMIT fails after timing out while trying to obtain an exclusive lock. This demonstration depends on that locking mode and cannot be applied directly to WAL mode. ```python import sqlite3, tempfile, os path = os.path.join(tempfile.mkdtemp(), "tenants.db") setup = sqlite3.connect(path) setup.execute("CREATE TABLE tenants (api_key TEXT PRIMARY KEY, name TEXT)") setup.executemany("INSERT INTO tenants VALUES (?, 'seed')", [(f"key-{i}",) for i in range(100)]) setup.commit() reader = sqlite3.connect(path, isolation_level=None) cursor = reader.execute("SELECT * FROM tenants") # mid-read, holds a SHARED lock cursor.fetchone() # deliberately left unfinished writer = sqlite3.connect(path, timeout=0.2) writer.execute("INSERT INTO tenants VALUES ('key-A', 'tenant-1')") print("INSERT inside transaction: ok") try: writer.commit() print("commit: ok") except sqlite3.OperationalError as e: print("commit failed:", e) writer.rollback() cursor.close() print("rows with key-A:", sqlite3.connect(path).execute( "SELECT count(*) FROM tenants WHERE api_key = 'key-A'").fetchone()[0]) ``` ```text INSERT inside transaction: ok commit failed: database is locked rows with key-A: 0 ``` PostgreSQL can also fail to commit because of deferred constraints or serialisation failures. A connection lost during COMMIT may leave the outcome unknown; it needs verification. In each case, a successful INSERT alone is insufficient evidence that the row was committed. List the side effects of each step and whether they can still be rolled back. In this example the database write can be rolled back before COMMIT, but the secret store does not participate in that transaction, and restoring an old value would be another fallible write. Confirming the database commit before writing the secret avoids the overwrite shown above. Other stores and operations need their own assessment of rollback and compensation. ## Recognising the failed state and resuming from it Order two leaves a new problem: the row exists, the secret was never written. To handle it, the row has to be able to say which stage it reached. Give the tenant a creation status: pending when inserted, active once the secret is in place, and have the signature verification path accept only active tenants. A tenant without a secret would fail verification anyway; the status means a pending tenant gets an explicit "not yet activated", which is much easier to diagnose than a rejection that looks like a bad signature. Status helps a retry resume once the request has been identified as belonging to the same creation attempt. When the same create request arrives again, look up the row first. No row: insert it. Row present and pending: skip the insert and do the missing step. Row present and active: leave it alone, that tenant is complete, whether a previous retry finished it or an unrelated request created it. The example only demonstrates sequential retries of the same request. It implements neither ownership checks nor coordination of concurrent retries, so it is not a safe tenant-creation implementation. It simulates the first secret write failing, then a resend. ```python import sqlite3 db = sqlite3.connect(":memory:") db.execute("CREATE TABLE tenants (api_key TEXT PRIMARY KEY, name TEXT, status TEXT)") secrets = {} fail_next_secret_write = True def put_secret(api_key, secret): global fail_next_secret_write if fail_next_secret_write: fail_next_secret_write = False raise OSError("secret store unavailable") secrets[api_key] = secret def create_tenant(api_key, name, secret): row = db.execute("SELECT status FROM tenants WHERE api_key = ?", (api_key,)).fetchone() if row is None: db.execute("INSERT INTO tenants VALUES (?, ?, 'pending')", (api_key, name)) db.commit() elif row[0] == "active": return "already active" # finished already, leave it alone put_secret(api_key, secret) # resume at the missing step db.execute("UPDATE tenants SET status = 'active' WHERE api_key = ?", (api_key,)) db.commit() return "active" def dump(label): print(label, db.execute("SELECT api_key, status FROM tenants").fetchall(), secrets) try: create_tenant("key-A", "tenant-1", "s3cret-of-tenant-1") except OSError as e: print("first attempt failed:", e) dump("after failure:") print("retry:", create_tenant("key-A", "tenant-1", "s3cret-of-tenant-1")) dump("after retry: ") print("again:", create_tenant("key-A", "tenant-1", "other")) dump("after again: ") ``` ```text first attempt failed: secret store unavailable after failure: [('key-A', 'pending')] {} retry: active after retry: [('key-A', 'active')] {'key-A': 's3cret-of-tenant-1'} again: already active after again: [('key-A', 'active')] {'key-A': 's3cret-of-tenant-1'} ``` After the first failure there is a pending row and an empty store. The retry writes only the secret and the status; it does not insert again. The third call carries a different secret, and because the row is already active the operation returns without touching the store. The example reduces "same request" to "same API key", which is insufficient to establish ownership of the creation attempt. Even with a caller-supplied idempotency key, request ownership and concurrent retries need verification. The general principles are in [Idempotency and retries](/en/posts/idempotency-and-retries/). Two things are deliberately left out of the example. First, SELECT followed by INSERT races: two requests creating the same key at once will have one INSERT hit the unique constraint, and that error needs handling and a fresh read of the status. An existing row alone does not establish that this request may continue writing. Second, pending rows are never cleaned up here. If you do clean them up, the compensating delete has to check that the row belongs to this create attempt and is still pending, or it will remove a tenant that another request just finished. Whether to compensate inline or leave it to a periodic background job depends on whether a lingering pending row does any harm. Rejecting pending tenants during verification is only part of that assessment; the effect on other workflows needs verification. ## Where two-phase commit falls down Two stores that need to commit together is the textbook case for two-phase commit. The protocol has two phases: the coordinator asks each participant whether it can commit, each participant writes what it needs to commit or undo to its log and votes, and only if every vote is yes does the coordinator order a commit, otherwise an abort. Wikipedia is blunt about the main drawback: it is a blocking protocol. A participant that has voted yes can only wait for the decision, and if the coordinator fails permanently and its decision cannot be recovered, some participants may remain blocked. Before that drawback even matters, there is a precondition. 2PC requires every participant to persist a prepared state and accept a commit or abort instruction later. PostgreSQL can do this: after `PREPARE TRANSACTION` the transaction's state is fully stored on disk, and `COMMIT PREPARED` or `ROLLBACK PREPARED` can be issued from any session. The same documentation says the command exists for an external transaction manager to run atomic global transactions across resources, that applications and interactive sessions should not be using it, and that transactions left prepared for a long time interfere with VACUUM and in extreme cases can force the database to shut down to prevent transaction ID wraparound. The file-based secret store assumed here has no prepared state. A write takes effect immediately, so it cannot take part in the protocol, and support for 2PC on the database side alone cannot guarantee atomicity across these two stores. Within this example's constraints, ordering plus compensation is an option with weaker guarantees than 2PC: the intermediate state is visible, a pending row exists for a while, and the compensation can itself fail. In return you need neither a secret store that speaks the protocol nor a plan for prepared transactions left in the database. Whether that trade is acceptable depends on whether a pending state harms anything else in the system. If verification rejects pending tenants and listings do not present them as live, retaining the row may be acceptable, but its effect on other workflows still needs verification. ## Aside: temp files and rename in a file-based store A common way to write a file-based secret store is to write a temporary file and rename it over the target, so that, where atomic replacement is guaranteed, a reader sees either the old complete content or the new complete content. There is a trap in the setup itself: if both the main service and a background worker write this file, and both use the same fixed temporary name, starting them together makes them overwrite or delete each other's temp file. The fix is a unique temporary name. Go's `os.CreateTemp` generates a filename containing a random string from the pattern, and the documentation guarantees that multiple programs or goroutines calling it at the same time will not choose the same file. ```go package main import ( "fmt" "os" "path/filepath" "sync" ) // Each writer gets a unique temp name from os.CreateTemp, then renames it over the target. func writeAtomically(dir, target, content string) (string, error) { tmp, err := os.CreateTemp(dir, ".secrets-*.tmp") if err != nil { return "", err } if _, err := tmp.WriteString(content); err != nil { return "", err } if err := tmp.Close(); err != nil { return "", err } return tmp.Name(), os.Rename(tmp.Name(), filepath.Join(dir, target)) } func main() { dir, _ := os.MkdirTemp("", "store") var wg sync.WaitGroup for _, who := range []string{"main-service", "background-worker"} { wg.Add(1) go func(who string) { defer wg.Done() name, err := writeAtomically(dir, "secrets.json", `{"writer":"`+who+`"}`) fmt.Printf("%-17s tmp=%s err=%v\n", who, filepath.Base(name), err) }(who) } wg.Wait() data, _ := os.ReadFile(filepath.Join(dir, "secrets.json")) left, _ := filepath.Glob(filepath.Join(dir, ".secrets-*.tmp")) fmt.Printf("final content: %s\nleftover temp files: %d\n", data, len(left)) } ``` ```text background-worker tmp=.secrets-2624387774.tmp err= main-service tmp=.secrets-2077610865.tmp err= final content: {"writer":"main-service"} leftover temp files: 0 ``` The two goroutines get different temporary names, both renames succeed, the target holds one writer's complete content, and no temp file is left behind. Which writer wins, and what the random suffix is, may change between runs. The scope of this needs stating. The `os.Rename` documentation says an existing target is replaced if it is not a directory, and also that on non-Unix platforms the rename is not guaranteed to be atomic even within the same directory. Atomic replacement does not guarantee survival after a power cut. The required scope of fsync and the platform conditions need verification; the example implements neither durability nor complete cleanup on failure. Atomic replacement also does not mean read-modify-write is locked: two writers that each read the old file, modify it and rename will have the later rename discard the earlier one's changes, although the output above only shows replacement of complete files, not a read-modify-write sequence. A unique temporary name solves path collisions and nothing else. Lost updates between multiple writers need a lock or a single writer, for instance a rule that only the main service generates this file. ## Notes - With the overwriting store in this example, writing the secret first can damage an existing tenant; committing the row first can leave an incomplete new tenant. - Write the secret only after the database COMMIT is confirmed successful. INSERT succeeding inside a transaction does not count, and the rollback journal example uses a SQLite reader holding its lock to make COMMIT time out. - Here, complete the rollback-capable database operation before writing to the separate store; other operations need their own assessment. - Give the row a creation status such as pending, and have verification accept only active. A retry checks request ownership and status before resuming, and leaves active rows alone. - 2PC needs both sides to persist a prepared state. The file-based store in this example does not provide that ability, and the PostgreSQL documentation reserves `PREPARE TRANSACTION` for external transaction managers. - Use `os.CreateTemp` for unique temp names in a file-based store, but that only fixes path collisions, not lost updates between writers, and `os.Rename` does not guarantee atomicity on non-Unix platforms. ## Further reading - Wikipedia, [Two-phase commit protocol](https://en.wikipedia.org/wiki/Two-phase_commit_protocol): the voting and commit phases and the blocking behaviour; the 2PC background in this post comes from here. - PostgreSQL 17 documentation, [Two-Phase Transactions](https://www.postgresql.org/docs/17/two-phase.html): the database side of 2PC, the three commands and where prepared state is stored; cited only, not run. - PostgreSQL 17 documentation, [PREPARE TRANSACTION](https://www.postgresql.org/docs/17/sql-prepare-transaction.html): the command reference, including the statement that it is meant for external transaction managers and the warning about long-lived prepared transactions. - Go documentation, [os.Rename](https://pkg.go.dev/os#Rename): replacement semantics and the non-Unix atomicity caveat; `os.CreateTemp` on the same page documents the unique-name guarantee. > Ideas and technical judgement by Sheng; drafted with Claude · topic chosen by Sheng; text, case notes and examples written by Claude, run on Linux with Python 3.13.18 and Go 1.24.7.