Grails 2 Gorm Hibernate 4: 2 round robin servers causing jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '493' for key 'PRIMARY'

Question

I am working on a Grails version 2.5 project. I am facing a problem with Gorm / Hibernate. The project is deployed on 2 servers, each would be getting a call in a round robin fashion. When we hit one of our APIs, its trying to create a new entity and persist in it the DB.

The problem is, when we call this API, almost every other request is failing with error message like:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '493' for key 'PRIMARY'

I tried setting the id value, but its not taking that value, and its trying its own value. I also tried mapping id generator 'assigned' in the child class (can't touch base class which has generator set to identity instead as there are other child classes)

My question is, how can we purge what hibernate thinks would be the next value of the primary key? I have tried disabling second level cache and query cache, but that doesn't work. Do we have a way to programmatically purge hibernate cache, so that it goes to the DB to see what max id value is there? Not just that, if you are aware of any other way to solve it, then please post them too.

EDIT:

Here are the mappings of the base class and child class:

Base class:

abstract class BaseEntity implements Serializable {
    static mapping = {
        tablePerHierarchy false  // avoid creating the base_domain table
        tablePerConcreteClass true
        id generator: 'identity'
        createdDate index: true  // index this column in each subclass table
        inUse index: true  // index this column in each subclass table
    }
}

Child class:

class ActualEntity1 extends BaseEntity {

    static mapping = {
        id generator: 'assigned'
    }
}

These are gorm domain classes. I removed the other fields of the classes intentionally.

Answer 1

There might be a race condition if that same API is getting called. So, for now, the first thing I wanted to have is not have this problem as frequently as it is coming now.
Later, if it at all happens, we can think of using a common key in Redis that we can keep on incrementing.

I am trying all the solutions which don't involve changing the DB schema

It is true that using multiple servers in a round-robin fashion could lead to race conditions for the next value of the primary key. When you are using the identity generator, Hibernate relies on the underlying database to generate IDs, typically via auto-increment columns.

The most straightforward way to handle this situation in a multiserver environment would be to use a sequence generator, the database will always generate a unique identifier, ensuring there are no duplicate keys.
That, however, involves modifying the database schema.

You can start and try using UUIDs (Universally Unique Identifiers) as primary keys. UUIDs are generated in such a way that they are virtually guaranteed to be unique.
That will not require changes in your DB schema, but you will need to modify your domain objects to support UUIDs as primary keys.

class ActualEntity1 extends BaseEntity {
    UUID id

    static mapping = {
        id generator: 'uuid'
    }
}

It does have the advantage of virtually eliminating the possibility of collisions.
But: UUIDs are larger than regular integer IDs and can be harder to manage in some situations.

---

Since you have tried using the assigned generator in the ActualEntity1, you can programmatically determine the next ID by querying the database for the maximum current ID and then adding one to it.
That, however, has a potential risk of race conditions. You would have to implement some locking mechanism to ensure the ID is not duplicated.

---

You can also rely on external services:

• Build a separate microservice responsible solely for generating unique IDs. Every time a new entity needs to be persisted, this service is called first to retrieve a new ID. The main advantage of this approach is that the ID generation logic is abstracted away and centralized. But this introduces another point of failure and additional latency.

• Use a distributed system tool like ZooKeeper or etcd for maintaining a global unique ID generator. These systems can maintain distributed locks, ensuring that you always get unique IDs, even across multiple servers. However, introducing a new system component like this can be complex.
  See for illustration "Generating Distributed UUID's using Zookeeper", from Phani Kumar Yadavilli, May 2019.

---

the problem is, changing this key in DB to UUID isn't just a change for that particular table, but the other tables keeping FKs. *

So, I was thinking of avoiding a lot data migration which would come. Also, I tried generator assigned, but only if that worked as documented, I wouldn't have pasted this question.

I would really appreciate if there could be a way within grails, without setting up zookeeper / redis / DB change.

OK. Given the constraints, another approach could be to use a combination of programmatic assignment and synchronization.

That would involve using a dedicated database table as a distributed lock mechanism. It would help making sure only one server can assign IDs at a given time, effectively eliminating the race condition.

You would need to:

• Create a table called DistributedLock with a single row.
• When a server needs to assign an ID, it will try to obtain a lock on that row. In SQL terms, this would be a SELECT ... FOR UPDATE statement, which tries to lock the row for the duration of the transaction.
• If the server gets the lock, it then fetches the maximum ID from the target table, increments it, assigns it to the new entity, and releases the lock (commits the transaction).
• If another server tries to get the lock while it is already locked by the first server, it will wait until the first server releases the lock.

That approach does add a bit of overhead for the locking, but ensures that the race condition is handled without changing primary key types or introducing external systems.

A pseudo-code could look like:

// Try to obtain a lock. If another server already has the lock, this will wait.
def lock = DistributedLock.executeQuery("SELECT d FROM DistributedLock d WHERE ... FOR UPDATE")

// Now that we have the lock, fetch the max ID
def maxId = ActualEntity1.executeQuery("SELECT max(id) FROM ActualEntity1")

// Assign the next ID
newEntity.id = maxId + 1

// Save the entity
newEntity.save()

// Commit the transaction, which releases the lock.

However, if the system will be under very heavy load with frequent entity creations, this might not be the most scalable solution and you may need to explore other avenues.