For Workes & Pages, what is the name of the domain?
n/a
What is the issue or error you’re encountering
D1 transactions are leaving the database in an inconsistent state
What are the steps to reproduce the issue?
I have a Pages Function which runs a batch of UPDATE statements on D1. The queries increment counter fields in two different tables. The counters should always have consistent values. But I find they do not.
What is going on here? My understanding was that (a) incrementing a field in SQLite is atomic, and (b) D1 batched queries are SQLite transactions which should be internally consistent.
Pages Function code (SvelteKit):
await platform.env.DB.batch([
platform.env.DB.prepare("UPDATE scores SET players = players + 1 WHERE day = ?").bind(day),
platform.env.DB.prepare("UPDATE articles SET played = played + 1 WHERE id = ?").bind(articleId),
])
Relevant parts of DB schema:
CREATE TABLE scores (day INTEGER PRIMARY KEY NOT NULL, players INTEGER DEFAULT 0 NOT NULL) STRICT;
CREATE TABLE articles (id INTEGER PRIMARY KEY NOT NULL, played INTEGER DEFAULT 0 NOT NULL) STRICT;
The Function runs 300-400 times a day. I find most days, scores.players differs from articles.played by 1 or 2.