Trying Aurora DSQL made me write retry logic for the first time
I used Aurora DSQL for a small shared to-do list to learn about distributed SQL. The part that taught me the most was handling commit conflicts under optimistic concurrency.
We covered distributed databases in my systems class this semester, mostly in theory. When I saw that Aurora DSQL is serverless and PostgreSQL-compatible, I wanted to try it on something small enough to finish in a weekend: a shared to-do list that my roommates and I could all edit.
Connecting was the first thing that felt different from RDS. There's no database password. DSQL uses IAM authentication, so the app generates a short-lived auth token with the AWS SDK and passes it as the password to a normal Postgres driver. It felt weird for about an hour and then I really liked not having a password in a config file.
The real lesson was concurrency. DSQL uses optimistic concurrency control. Transactions don't hold locks while they run. Conflicts are checked at commit, and if someone else changed the same rows first, your commit fails with a serialization error (SQLSTATE
40001).I tested it with two scripts updating the same to-do item in a loop. My first version of the code just crashed whenever it lost the race, because I'd never had to handle that error before. Every database I've used in class quietly made one transaction wait for the other. The fix was a small retry loop:
```python
for attempt in range(5):
try:
with conn.transaction():
update_item(conn, item_id, new_title)
break
except SerializationFailure:
time.sleep(0.05 * (2 ** attempt))
```
After that, both scripts finished and the final state was consistent.
Some other things I ran into:
- It's PostgreSQL-compatible, not identical. Some features aren't supported, so read the list before trying to port an existing schema. I had to change a couple of things in mine.
- There are limits on how much one transaction can modify. That pushed me toward small transactions, which is probably a good habit anyway.
- For a project this size, I stayed inside the free tier the whole time.
I'd read about optimistic concurrency in lecture slides. Having my own script crash on a failed commit made it real in a way the slides didn't.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article