Comment by sgarland

2 days ago

For the love of all that’s holy, please stop doing read-after-write. In nearly all cases, it isn’t needed. The only cases I can think of are if you need a DB-generated value (so, DATETIME or UUIDv1) from MySQL, or you did a multi-row INSERT in a concurrent environment.

For MySQL, you can get the first auto-incrementing integer created from your INSERT from the cursor. If you only inserted one row, congratulations, there’s your PK. If you inserted multiple rows, you could also get the number of rows inserted and add that to get the range, but there’s no guarantee that it wasn’t interleaved with other statements. Anything else you wrote, you should already have, because you wrote it.

For MariaDB, SQLite, and Postgres, you can just use the RETURNING clause and get back the entire row with your INSERT, or specific columns.

8 comments

sgarland

Reply
mystifyingpoi  2 days ago

> please stop doing read-after-write

But that could be applied only in context of a single function. What if I save a resource and then mash F5 in the browser to see what was saved? I could hit a read replica that wasn't fast enough and the consistency promise breaks. I don't know how to solve it.

  • gunnarmorling  2 days ago

    The comment at [1] hints at a solution: in the response of the write request return the id of the transaction or its commit position in the TX log (LSN). When routing a subsequent read request to a replica, the system can either wait until the transaction is present on the replica, or it redirect to to the primary node. Discussed a similar solution a while ago in a talk [2], in the context of serving denormalized data views from a cache.

    [1] https://news.ycombinator.com/item?id=46073630 [2] https://speakerdeck.com/gunnarmorling/keep-your-cache-always...

    • sgarland  4 hours ago

      ProxySQL has a more simplified version of this, but it’s transparent to the client. Since it’s constantly monitoring the cluster, it knows what replica lag is at any given time. If you send a request to a read replica, it holds the request until enough time has passed for the replica to have caught up.

  • mrkeen  2 days ago

    Yep. Your SQL transactions are only consistent to the extent that they stay in the db.

    Mashing F5 is a perfect example of stepping outside the bounds of consistency.

    If want to update a counter, do you read the number on your frontend, add 2 then send it back to the backend? If someone else does the same, that's a lost write regardless of how "strongly consistent" your db vendor promises to be.

    But that's how the article says programmers work. Read, update, write.

    If you thought "that's dumb, just send in (+2)", congrats, that's EC thinking!