How to avoid DataIntegrityViolationException in multithread environment for inserts?-CodePudding

Suppose we have a "devices" table that has a "device_uuid" column on which we have a unique constraint.

The pseudocode for the part of the application looks like

Optional<Device> device = deviceRepository.findByDeviceUUID(deviceUUID)

if (device.isPresent())
  device.heartBeat()
else 
 createNewDevice(deviceUUID)

On high load, when more than one thread tries to add a new device I end up with DataIntegrityViolationException because of the unique constraint violation, which is nice because data is consistent, however, it makes quite a mess in the logs of my application. Is it possible to lock it somehow on the database level?

I have tried already making it in Serializable isolation level, but it is not a solution to this type of problem.

CodePudding user response：

The easiest way is to select the Device with the FOR UPDATE SQL clause. In this case, another transaction won't be able to obtain the same row until the previous one is still holding it.

If you're using JPA, then here is the way.

Device device = (Device) entityManager.createQuery("from Device where deviceId = :id")
  .setParameter("id", uuid)
  .setLockMode(LockModeType.PESSIMISTIC_WRITE)
  .getSingleResult();

CodePudding user response：

for a multi-threaded-environment its always better to generate UUID on centralized-node here Database can be a good option

We can create a function at DB side and get uuid from there