This month Amazon launched a new serverless databased called Amazon Aurora DSQL. Its a fully managed PostgreSQL-compatible database engine that is designed to scale to any workflow and still be strongly consistent. Heres a great talk on the internals that I recommend: Deep dive into Amazon Aurora DSQL and its architecture
Whats really interesting to me is that relational databases are not known to be infinitely scalable. They are a great choice to model a relationalship between entities but its this relationship that make it difficult to scale.
A key tenant of making a system scale is to avoid coordination between components. The relationship between tables/rows in most SQL databases often requires different forms of locking to ensure you get a consistent result for your query. A table lock on an update will block a concurrent read on that data in most cases.
One of the ways the DSQL team avoid this problem is by making all queries use snapshot isolation with multi-version concurrency control (MVVC). This isn’t a new thing but there are some twists. They tag all queries with a highly accurate timestamp (amazing on its own!) and only look at the data from that exact point in time.
This allows them to read/update concurrently from any different users without having to do much coordination. If you watch the talk, you will see that there is coordination on write but its only on commit. It will check concurrent transactions to decide if it can commit.
The one concurrency bug that we still need to worry about is write skew.
Imagine we are looking at the URC rugby table.
- Hollywoodbets Sharks - 10 Points
- Leinster Rugby - 9 Points
- Glasgow Warriors - 5 Points
I did say imagine!
There are three database tables.
Team
| Id |
Name |
| 10 |
Hollywoodbets Sharks |
| 20 |
Leinster Rugby |
| 30 |
Glasgow Warriors |
Ladder
| TeamId |
Points |
| 10 |
10 |
| 20 |
9 |
| 30 |
5 |
Match
| MatchId |
HomeTeamId |
AwayTeamId |
HomeScore |
AwayScore |
| 100 |
10 |
20 |
0 |
0 |
There is a feature where the website will update based on the live score. It shows who would lead if the game ended.
There currently is a match between the Sharks and Leinster in Durban. Each team are responsible for updating their own score and the Ladder if they are now in the lead.
at t1: Leinster scores a try, putting them in the lead.
The away team runs:
IF EXISTS (SELECT * FROM Match where MatchId = 100 AND AwayScore > HomeScore)
THEN
UPDATE Ladder SET Points=13 WHERE TeamId = 20;
END IF;
The ladder is sadly correct.
at t2: The Leinster try gets reversed (cheating of course) and the Sharks get a penalty to hit the lead.
At the same time Leinster reverse their 5 points and Sharks add three. They both do this off the version of the table at T1.
With snapshot isolation, Leinster and sharks see the table in their transactions as
| MatchId |
HomeTeamId |
AwayTeamId |
HomeScore |
AwayScore |
| 100 |
10 |
20 |
0 |
5 |
This makes it possible to get an unexpected result when the Sharks runs:
IF EXISTS (SELECT * FROM Match where MatchId = 100 AND AwayScore < HomeScore)
THEN
UPDATE Ladder SET Points=14 WHERE TeamId = 10;
END IF;
We could end up with the ladder being factually and morally wrong even though the Sharks are leading the game.
- Leinster Rugby - 13 Points
- Hollywoodbets Sharks - 10 Points
- Glasgow Warriors - 5 Points
This is called write skew and its caused by two concurrent processes reading versioned data to make a decision.
Its one of the tradeoffs that were made by using snapshot isolation instead of full serializable isolation.
There are ways to reduce the likelihood of write skew (eg:SELECT FOR UPDATE) but its not guaranteed to be executed in a serial order with snapshot isolation.
DSQL is a great option for the many of use cases but we should be careful if this would be a dealbreaker for your application.
Edit 19 December 2024: Marc just added a post on his blog about the choice of Snapshot isolation. You can read it here: Snapshot Isolation vs Serializability