Uploaded image for project: 'Hibernate ORM'
  1. Hibernate ORM
  2. HHH-9287

Pooled optimizer identifiers clash with INSERT rows calling sequence directly

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 4.3.5
    • Fix Version/s: None
    • Component/s: hibernate-core
    • Labels:
      None
    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true

      Description

      Giving the following identifier:

      @Id
      @GenericGenerator(name = "sampleGenerator", strategy = "enhanced-sequence",
      parameters =

      { @org.hibernate.annotations.Parameter(name = "optimizer", value = "pooled"), @org.hibernate.annotations.Parameter(name = "initial_value", value = "1"), @org.hibernate.annotations.Parameter(name = "increment_size", value = "2") }

      )
      @GeneratedValue(strategy = GenerationType.TABLE, generator = "sampleGenerator")
      private Long id;

      When executing the following code:

      doInTransaction(new TransactionCallable<Void>() {
      @Override
      public Void execute(Session session) {
      for (int i = 0; i < 5; i++)

      { session.persist(new SequenceIdentifier()); }
      session.flush();
      assertEquals(5, ((Number) session.createSQLQuery("SELECT COUNT FROM sequenceIdentifier").uniqueResult()).intValue());
      insertNewRow(session);
      insertNewRow(session);
      assertEquals(7, ((Number) session.createSQLQuery("SELECT COUNT FROM sequenceIdentifier").uniqueResult()).intValue());
      List<Number> ids = session.createSQLQuery("SELECT id FROM sequenceIdentifier").list();
      for(Number id : ids) {
      LOGGER.debug("Found id: {}", id);
      }
      for (int i = 0; i < 3; i++) { session.persist(new SequenceIdentifier()); }

      session.flush();
      return null;
      }
      });

      I get the following error:

      Caused by: java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: unique constraint or index violation; SYS_PK_10104 table: SEQUENCEIDENTIFIER

      The last inserted id is 11, which was assigned by the database sequence upon inserting a row with a manual JDBC call.

      When the pooled optimizer runs out of low values it calls the sequence and gets the max hi value of 13, so it will try to insert 11 and 12, which will clash with the manually inserted rows.

      Isn't this optimizer supposed to offer a HI/LO implementation that manages to work with external systems which are unaware of Hibernate internal identifier strategies?

        Activity

        Hide
        mih_vlad Mihalcea Vlad added a comment - - edited

        In the PooledOptimizer, changing:

        generationState.value = generationState.hiValue.copy().subtract( incrementSize );

        to this:

        generationState.value = generationState.hiValue.copy().subtract( incrementSize - 1);

        fixes this issue.

        That's because when loading a new high value (from the db sequence) the new lowest boundary should not match a previous sequence number (e.g. generationState.hiValue.copy().subtract( incrementSize )) but the next consecutive value (e.g. generationState.hiValue.copy().subtract( incrementSize - 1 ))

        Show
        mih_vlad Mihalcea Vlad added a comment - - edited In the PooledOptimizer, changing: generationState.value = generationState.hiValue.copy().subtract( incrementSize ); to this: generationState.value = generationState.hiValue.copy().subtract( incrementSize - 1); fixes this issue. That's because when loading a new high value (from the db sequence) the new lowest boundary should not match a previous sequence number (e.g. generationState.hiValue.copy().subtract( incrementSize )) but the next consecutive value (e.g. generationState.hiValue.copy().subtract( incrementSize - 1 ))
        Hide
        mih_vlad Mihalcea Vlad added a comment -

        The "pooled-lo" optimizer isn't affected by this issue.

        Show
        mih_vlad Mihalcea Vlad added a comment - The "pooled-lo" optimizer isn't affected by this issue.
        Hide
        steve Steve Ebersole added a comment -

        Andrea Boriero Please verify this one too. Thanks!

        Mihalcea Vlad I saw you had a nice description of this on your blog somewhere. Mind posting that blog link here for Andrea? Thanks

        Show
        steve Steve Ebersole added a comment - Andrea Boriero Please verify this one too. Thanks! Mihalcea Vlad I saw you had a nice description of this on your blog somewhere. Mind posting that blog link here for Andrea? Thanks
        Hide
        mih_vlad Mihalcea Vlad added a comment -

        I have three posts related to enhanced identifiers, although not strictly linked to this particular issue but it might help anyone investigating it:

        Hibernate hidden gem: the pooled-lo optimizer

        From JPA to Hibernate’s legacy and enhanced identifier generators

        The hi/lo algorithm

        Show
        mih_vlad Mihalcea Vlad added a comment - I have three posts related to enhanced identifiers, although not strictly linked to this particular issue but it might help anyone investigating it: Hibernate hidden gem: the pooled-lo optimizer From JPA to Hibernate’s legacy and enhanced identifier generators The hi/lo algorithm

          People

          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:

              Development