4 ms·

This isn't just stale data, in the sense of "a point-in-time consistent snapshot which does not reflect some recent transactions". I think what's going on here

by aphyr 1y ago

This isn't just stale data, in the sense of "a point-in-time consistent snapshot which does not reflect some recent transactions". I think what's going on here is that a read-only transaction against a secondary can observe some transaction T, but also miss transactions which must have logically executed before T.

mikesun 1y ago
"I think what's going on here is that a read-only transaction against a secondary can observe some transaction T, but also miss transactions which must have logically executed before T." i was intuitively wondering the same but i'm having trouble reasoning how the post's example with transactions 1, 2, 3, 4 exhibits this behavior. in the example, is transaction 2 the only read-only transaction and therefore the only transaction to read from the read replica? i.e. transactions 1, 3, 4 use the primary and transaction 2 uses the read replica?
- aphyr 1y ago
  Yeah, that's right. It may be that the (apparent) order of transactions differs between primary and secondary.
  - mikesun 1y ago
    ah, so something like... if the primary ordered transaction 3 < transaction 1, but transaction 2 observes only transaction 1 on the read-only secondary potentially because the secondary orders transaction 1 < transaction 3?
  - franckpachot 1y ago
    The Write-Ahead Logging (WAL) is single-threaded and maintains consistency at a specific point in time in each instance. However, there can be anomalies between two instances. This behavior is expected because the RDS Multi-AZ cluster does not wait for changes to be applied in the shared buffers. It only waits for the WAL to sync. This is similar to the behavior of PostgreSQL when synchronous_commit is set to on. Nothing unexpected.
kevincox 1y ago
To provide a simple (although contrived) example of the type of thing that can happen. Imagine that you have a table with three columns `gps_coordinate`, `postal_code` and `city`. The way these are set is that the new coordinate gets posted to the API and `gps_coordinate` is updated. This then kicks off a background task that uses the new coordinate to lookup and update `postal_code`. Then another background task uses the postal code to look up and set `city`. Since these happen sequentially, for a single update of `gps_coordinate` you would only expect to be able to observe one of: 1. Nothing updated yet, all columns have the previous value. 2. `gps_coordinate` updated, with `postal_code` and `city` still having the previous values. 3. `gps_coordinate` and `postal_code` updated with `city` still having the previous value. 4. All fields updated. But the ordering that aphyr proved is possible allows you to see "impossible" states such as 1. `postal_code` updated with `gps_coordinate` and `city` still having the previous values. 2. `city` updated with `gps_coordinate` and `postal_code` still having the previous values. Basically since these transactions happen in order and depend on one another you would expect that you can only see the "left to right" progression. But actually you can see some subset of the transactions applied even if that isn't a possible logical state of the database.

by aphyr 1y ago

mikesun 1y ago

"I think what's going on here is that a read-only transaction against a secondary can observe some transaction T, but also miss transactions which must have logically executed before T." i was intuitively wondering the same but i'm having trouble reasoning how the post's example with transactions 1, 2, 3, 4 exhibits this behavior. in the example, is transaction 2 the only read-only transaction and therefore the only transaction to read from the read replica? i.e. transactions 1, 3, 4 use the primary and transaction 2 uses the read replica?

aphyr 1y ago
Yeah, that's right. It may be that the (apparent) order of transactions differs between primary and secondary.
- mikesun 1y ago
  ah, so something like... if the primary ordered transaction 3 < transaction 1, but transaction 2 observes only transaction 1 on the read-only secondary potentially because the secondary orders transaction 1 < transaction 3?
- franckpachot 1y ago
  The Write-Ahead Logging (WAL) is single-threaded and maintains consistency at a specific point in time in each instance. However, there can be anomalies between two instances. This behavior is expected because the RDS Multi-AZ cluster does not wait for changes to be applied in the shared buffers. It only waits for the WAL to sync. This is similar to the behavior of PostgreSQL when synchronous_commit is set to on. Nothing unexpected.

kevincox 1y ago

To provide a simple (although contrived) example of the type of thing that can happen. Imagine that you have a table with three columns `gps_coordinate`, `postal_code` and `city`. The way these are set is that the new coordinate gets posted to the API and `gps_coordinate` is updated. This then kicks off a background task that uses the new coordinate to lookup and update `postal_code`. Then another background task uses the postal code to look up and set `city`. Since these happen sequentially, for a single update of `gps_coordinate` you would only expect to be able to observe one of: 1. Nothing updated yet, all columns have the previous value. 2. `gps_coordinate` updated, with `postal_code` and `city` still having the previous values. 3. `gps_coordinate` and `postal_code` updated with `city` still having the previous value. 4. All fields updated. But the ordering that aphyr proved is possible allows you to see "impossible" states such as 1. `postal_code` updated with `gps_coordinate` and `city` still having the previous values. 2. `city` updated with `gps_coordinate` and `postal_code` still having the previous values. Basically since these transactions happen in order and depend on one another you would expect that you can only see the "left to right" progression. But actually you can see some subset of the transactions applied even if that isn't a possible logical state of the database.