How to avoid race conditions in GORM

Question

I am developing a system to enable patient registration with incremental queue number. I am using Go, GORM, and MySQL.

An issue happens when more than one patients are registering at the same time, they tend to get the same queue number which it should not happen.

I attempted using transactions and hooks to achieve that but I still got duplicate queue number. I have not found any resource about how to lock the database when a transaction is happening.

func (r repository) CreatePatient(pat *model.Patient) error {
    tx := r.db.Begin()
    defer func() {
        if r := recover(); r != nil {
            tx.Rollback()
        }
    }()

    err := tx.Error
    if err != nil {
        return err
    }

    

    // 1. get latest queue number and assign it to patient object
    var queueNum int64
    err = tx.Model(&model.Patient{}).Where("registration_id", pat.RegistrationID).Select("queue_number").Order("created_at desc").First(&queueNum).Error

    if err != nil && err != gorm.ErrRecordNotFound {
        tx.Rollback()
        return err
    }
    pat.QueueNumber = queueNum + 1

    // 2. write patient data into the db
    err = tx.Create(pat).Error
    if err != nil {
        tx.Rollback()
        return err
    }

    return tx.Commit().Error
}

You cannot safely look up the highest id number in a database and then increment it by one, because of race conditions. Transactions don't help. When you insert a row into a database, you get back its id number. Take a look at this. https://stackoverflow.com/questions/28773963/how-can-i-retrieve-instance-of-last-added-item — O. Jones, Feb 22 '21 at 13:06
Nice input Jones. But the thing is we are using UUID for the primary key. Even if we used incremental primary key, we can not rely on it to assign the queue number, because in one table there are patients from different registrations. We wouldn't want a patient to get a queue number according to the last queue number from different registration. — Hardyin Alexander Hutapea, Feb 22 '21 at 13:38

Ezequiel Muns · Accepted Answer · 2021-02-22T15:05:26.163

As stated by @O. Jones, transactions don't save you here because you're extracting the largest value of a column, incrementing it outside the db and then saving that new value. From the database's point of view the updated value has no dependence on the queried value.

You could try doing the update in a single query, which would make the dependence obvious:

UPDATE patient AS p
JOIN (
  SELECT max(queue_number) AS queue_number FROM patient WHERE registration_id = ?
) maxp
SET p.queue_number = maxp.queue_number + 1 
WHERE id = ?

In gorm you can't run a complex update like this, so you'll need to make use of Exec.

I'm not 100% certain the above will work because I'm less familiar with MySQL transaction isolation guarantees.

A cleaner way

Overall, it'd be cleaner to keep a table of queues (by reference_id) with a counter that you update atomically:

Start a transaction, then

SELECT queue_number FROM queues WHERE registration_id = ? FOR UPDATE;

Increment the queue number in your app code, then

UPDATE queues SET queue_number = ? WHERE registration_id = ?;

Now you can use the incremented queue number in your patient creation/update before transaction commit.

thantks Ezquiel, I haven't thought about it before. I tried it, but somehow it return error `Error 1093: You can't specify target table 'patients' for update in FROM clause` — Hardyin Alexander Hutapea, Feb 22 '21 at 14:20
Yeah, mysql doesn't like subqueries that query the table that's being updated, have updated the sql to use joins instead, and provided what I consider a better overall approach. — Ezequiel Muns, Feb 22 '21 at 14:46
oh my God, thank you so much! it works like a charm! I tried using the "dirtier" way and it is working as expected. — Hardyin Alexander Hutapea, Feb 22 '21 at 14:53

How to avoid race conditions in GORM

1 Answers1

A cleaner way