title: Kotlin JPA One-To-Many @ElementCollection attempts to save duplicates resulting in constraint violations
body: <p>I have the following entity defined (simplified)...</p>
  
  <pre><code>@Entity(name = "metrics")
  data class MetricsEntity(
      val name: String,
      // ... other properties omitted for clarity
      @ElementCollection(fetch = FetchType.EAGER)
      @MapKeyColumn(name = "event_name")
      @Column(name = "event_count")
      @CollectionTable(name = "metric_event", joinColumns = [JoinColumn(name = "metrics_id")])
      val events: MutableMap&lt;String, Int&gt;,
  )
  </code></pre>
  
  <p>The idea here is that for every entry in the metrics table we record counts of events and we end up with a table containing entries like this...</p>
  
  <pre><code> metrics_id |            event_name             | event_count 
  ------------+-----------------------------------+-------------
     15647624 | Launched                          |           1
     15647624 | Registration_successful           |          10
     15647624 | Registration_failed               |           2
     15647624 | History_viewed                    |           1
  </code></pre>
  
  <p>In the code we load the metrics entities using something like this...</p>
  
  <pre><code>val metrics = metricsRepository.findByProperties(properties)
  </code></pre>
  
  <p>...to get a single metric. The selection criteria here have been simplified but suffice to say we get one metric instance from this query. the repository here is defined as...</p>
  
  <pre><code>interface MetricsRepository : CrudRepository&lt;MetricsEntity, Long&gt; {
     ...
  }
  </code></pre>
  
  <p>Now we either update the events map to add a new count of one of increment an existing count using the following code...</p>
  
  <pre><code>metrics.events[eventName] = (it.events[eventName] ?: 0) + 1
  metricsRepository.save(it)
  </code></pre>
  
  <p>This works the vast majority of the time but every now and again the call to save throws a constraint violation called metric_event_constraint on the above table which is defined as...</p>
  
  <pre><code>ALTER TABLE metric_event ADD CONSTRAINT metric_event_constraint UNIQUE (metrics_id, name);
  </code></pre>
  
  <p>This seems to suggest that the save operation is saving a new row when a row already exists. Looking at the log is suggests that I am getting collisions between multiple threads trying to modify the same count...</p>
  
  <pre><code>08:58:18.466  INFO 3 --- [TaskExecutor-11] incr count for event Launched, count 1
  08:58:18.487  INFO 3 --- [cTaskExecutor-4] incr count for event Launched, count 1
  08:58:18.618 ERROR 3 --- [cTaskExecutor-4] incr count failed for request
  08:58:24.623  INFO 3 --- [TaskExecutor-94] incr count for event Launched, count 2
  08:59:14.951  INFO 3 --- [askExecutor-126] incr count for event Launched, count 3
  </code></pre>
  
  <p>...here the first event works and increments the count, the second one fails and doesn't (constraint violation caught) and the 3rd and 4th work fine. Total count is 3 when we wanted 4. It looks to me like the 1st and 2nd event collided.</p>
  
  <p>So the question is firstly do you think that this summary is correct? Secondly, how do I make it work ;)? My assumption is that I need to lock the metric entity so how would I go about that using the framework of crud repositories and entity classes used?</p>
  
  <p>The database behind this is Postgres.</p>
  
  <p>Regards,</p>
  
  <p>Mark</p>