Spring / Hibernate - StaleStateException thrown when deleting entity

Viewed 154

One of my entities is throwing a StaleStateException when trying to delete it. Code in question:

@RestController
@RequestMapping("/cycles")
public class DeleteCycleController {
  @DeleteMapping("{cycleId}")
  @ResponseStatus(HttpStatus.NO_CONTENT)
  public void deleteCycleById(@PathVariable("cycleId") UUID cycleId, Principal principal) {
// cycleRepository extends CrudRepository
    cycleRepository.deleteById(new CycleId(cycleId));
//    deleter.deleteCycle(new CycleId(cycleId));
  }
}

Throws the following:

org.springframework.orm.ObjectOptimisticLockingFailureException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: HikariProxyPreparedStatement@716092925 wrapping delete from cycle where id='c4c1428e-c296-4199-85f6-8ef16e6999c9'::uuid; nested exception is org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1; statement executed: HikariProxyPreparedStatement@716092925 wrapping delete from cycle where id='c4c1428e-c296-4199-85f6-8ef16e6999c9'::uuid

Here are the hibernate logs just before the failure:

2021-03-11 20:34:11.532 DEBUG 55483 --- [nio-8080-exec-7] org.hibernate.SQL                        : requestId=b53dd96089f748cf942fc46867d32fb5, requestMethod=DELETE, correlationId=b53dd96089f748cf942fc46867d32fb5, requestURI=/cycles/c4c1428e-c296-4199-85f6-8ef16e6999c9, userId=5ae11e82-f8df-4fad-ae10-1aa57423ba31, orgId=c7389774-9afa-452b-aa1c-74e7b18bc04d : select cycle0_.id as id1_0_0_, cycle0_.attributes as attribut2_0_0_, cycle0_.created_at as created_3_0_0_, cycle0_.cycle_type_id as cycle_ty4_0_0_, cycle0_.description as descript5_0_0_, cycle0_.end_time as end_time6_0_0_, cycle0_.start_time as start_ti7_0_0_, cycle0_.end_location as end_loca8_0_0_, cycle0_.last_modified_at as last_mod9_0_0_, cycle0_.name as name10_0_0_, cycle0_.organization_id as organiz11_0_0_, cycle0_.start_location as start_l12_0_0_ from cycle cycle0_ where cycle0_.id=?
Hibernate: select cycle0_.id as id1_0_0_, cycle0_.attributes as attribut2_0_0_, cycle0_.created_at as created_3_0_0_, cycle0_.cycle_type_id as cycle_ty4_0_0_, cycle0_.description as descript5_0_0_, cycle0_.end_time as end_time6_0_0_, cycle0_.start_time as start_ti7_0_0_, cycle0_.end_location as end_loca8_0_0_, cycle0_.last_modified_at as last_mod9_0_0_, cycle0_.name as name10_0_0_, cycle0_.organization_id as organiz11_0_0_, cycle0_.start_location as start_l12_0_0_ from cycle cycle0_ where cycle0_.id=?
2021-03-11 20:34:11.533 TRACE 55483 --- [nio-8080-exec-7] o.h.type.descriptor.sql.BasicBinder      : requestId=b53dd96089f748cf942fc46867d32fb5, requestMethod=DELETE, correlationId=b53dd96089f748cf942fc46867d32fb5, requestURI=/cycles/c4c1428e-c296-4199-85f6-8ef16e6999c9, userId=5ae11e82-f8df-4fad-ae10-1aa57423ba31, orgId=c7389774-9afa-452b-aa1c-74e7b18bc04d : binding parameter [1] as [OTHER] - [c4c1428e-c296-4199-85f6-8ef16e6999c9]
2021-03-11 20:34:11.542 DEBUG 55483 --- [nio-8080-exec-7] org.hibernate.SQL                        : requestId=b53dd96089f748cf942fc46867d32fb5, requestMethod=DELETE, correlationId=b53dd96089f748cf942fc46867d32fb5, requestURI=/cycles/c4c1428e-c296-4199-85f6-8ef16e6999c9, userId=5ae11e82-f8df-4fad-ae10-1aa57423ba31, orgId=c7389774-9afa-452b-aa1c-74e7b18bc04d : delete from cycle where id=?
Hibernate: delete from cycle where id=?
2021-03-11 20:34:11.542 TRACE 55483 --- [nio-8080-exec-7] o.h.type.descriptor.sql.BasicBinder      : requestId=b53dd96089f748cf942fc46867d32fb5, requestMethod=DELETE, correlationId=b53dd96089f748cf942fc46867d32fb5, requestURI=/cycles/c4c1428e-c296-4199-85f6-8ef16e6999c9, userId=5ae11e82-f8df-4fad-ae10-1aa57423ba31, orgId=c7389774-9afa-452b-aa1c-74e7b18bc04d : binding parameter [1] as [OTHER] - [c4c1428e-c296-4199-85f6-8ef16e6999c9]

And the Cycle entity:

@javax.persistence.Entity
@Accessors(fluent = true)
@Getter
@NoArgsConstructor(access = AccessLevel.PRIVATE)
@Builder
@TypeDefs({@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)})
public class Cycle {
  @Id private UUID id;
  private CycleName name;
  private CycleDescription description;
  private UUID cycleTypeId;
  private UUID organizationId;
  private Instant createdAt;
  private Instant lastModifiedAt;

  @Type(type = "jsonb")
  private Map<String, Object> attributes;

  private CycleDuration duration;

  @Column(columnDefinition = "Geometry", nullable = true)
  private Geometry startLocation; 

  private Geometry endLocation;

  @OneToMany(mappedBy = "cycleId", cascade = CascadeType.ALL, orphanRemoval = false)
  @Getter(AccessLevel.PRIVATE)
  @Setter(AccessLevel.PUBLIC)
  private List<CycleEntityMapping> entities = new ArrayList<>();
  
  ...
}

Any ideas as to what could be going wrong? Another (simpler) entity deletes fine.

Not sure if its the OneToMany causing issues, but I've tried changing the cascade type, orphan removal and fetch type with no luck. Otherwise, could it be caused by the value objects? Note that updating the entity works fine - it's only failing on delete.

1 Answers

In this case, it was caused by a bug in a before delete trigger. There was a bug in the trigger causing the row to not be deleted:

CREATE OR REPLACE FUNCTION trigger_delete_row_backup()
    RETURNS trigger AS
$BODY$
    BEGIN
INSERT INTO deleted_cycle values (OLD.*);
    RETURN NEW;
    -- should actually be RETURN OLD
    END;
$BODY$
   language PLPGSQL;

Once the trigger was fixed, the issue no was resolved.

Related