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?

        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