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: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.3.5
    • Fix Version/s: 5.0.0.CR1, 4.3.11
    • 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?

        Gliffy Diagrams

          Attachments

            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
            Hide
            dreab8 Andrea Boriero added a comment -

            Thanks Mihalcea Vlad I verified the issue and created 2 PR that should fix it.

            in order to avoid gaps in the id generation i also changed

            // Some comments here
            else if ( ! generationState.hiValue.gt( generationState.value ) ) {
            ...
            }
            

            to

            // Some comments here
            public String getFoo()
            else if ( generationState.value.gt( generationState.hiValue ) ) {
            ...
            }
            

            PR for 4.3 branch https://github.com/hibernate/hibernate-orm/pull/971
            PR for master branch https://github.com/hibernate/hibernate-orm/pull/972

            Show
            dreab8 Andrea Boriero added a comment - Thanks Mihalcea Vlad I verified the issue and created 2 PR that should fix it. in order to avoid gaps in the id generation i also changed // Some comments here else if ( ! generationState.hiValue.gt( generationState.value ) ) { ... } to // Some comments here public String getFoo() else if ( generationState.value.gt( generationState.hiValue ) ) { ... } PR for 4.3 branch https://github.com/hibernate/hibernate-orm/pull/971 PR for master branch https://github.com/hibernate/hibernate-orm/pull/972
            Hide
            dreab8 Andrea Boriero added a comment -

            PR applied to 4.3 and master branches

            Show
            dreab8 Andrea Boriero added a comment - PR applied to 4.3 and master branches
            Hide
            mih_vlad Mihalcea Vlad added a comment -

            Thanks for fixing it.

            Show
            mih_vlad Mihalcea Vlad added a comment - Thanks for fixing it.
            Hide
            steve Steve Ebersole added a comment -

            Andrea Boriero Isn't this actually part of CR1?

            Show
            steve Steve Ebersole added a comment - Andrea Boriero Isn't this actually part of CR1?
            Hide
            dreab8 Andrea Boriero added a comment -

            Steve Ebersole yes it is

            Show
            dreab8 Andrea Boriero added a comment - Steve Ebersole yes it is

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Development