This post is about a real problem I faced in my project. I merged two blog post into one so this is a bit longer post, please read till the end. Thank you!
I hope after reading this post, you will be able to avoid the same mistake we made in our project 😅. This small mistake didn’t cost us a billion dollars. But, It did cost us a few thousand, and the Prometheus alerts saved us.
But it could cost you a Billion if you are not careful 💸. I learn my lessons through mistakes, this is something that I will never forget in future.
The scenario is a simple database transaction. We all have used database transactions at least once in our programming life. If you don’t know how transactions work you could read the docs here for Postgres.
Our service handles the management of some Subscriptions in the database. All the changes to the rows happen within a transaction. The service is in Golang and the flow is like this::
- Start the transaction
- Fetch the record by ID
- Confirm if the operation for the change is possible
- If Yes, then update the record
- Commit the transaction
- For any error, revert the transaction
To fetch a record and acquire a lock on the record so that other flows would wait to update, we use SELECT ... FOR UPDATE query to fetch the record from Postgres and lock it.
Note: We use sqlx lib for database access.
The repository code is like this:
func (r *fetcher) GetSubscription(tx *sqlx.Tx, id uuid.UUID) (*model.Subscription, error) {
var subscription model.Subscription
err := tx.Get(&subscription, `
SELECT * FROM subscriptions
WHERE id = $1
FOR UPDATE
`, id)
if err != nil {
return nil, err
}
return &subscription, nil
}The service code is like this:
func (s *service) CancelSubscription(ctx context.Context, id uuid.UUID) (*model.Subscription, err error) {
tx, err := s.db.BeginTxx(ctx, nil)
if err != nil {
return nil, err
}
defer func() {
if err != nil {
tx.Rollback()
}
}()…