0

I have problem with MSSQL ID jumping feature.

My Requirement is like,I need to generate a sequence number say,starting from 1000 and increment one by one and my application is hosted in multiple servers and points to same DB.Multiple host are balanced with Nginx.

For this, wrote an Entity Class with SequenceGenerator 

@Entity
@SequenceGenerator(name = "IdSequence", 
sequenceName = " ID_GEN",  
allocationSize = 1)
public class Example implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;


    @Id
    @GeneratedValue(generator = " IdSequence ")
    private long id ;

}

And it works fine. When I restarted the DB, the next ID is jumped to current+1000 To resolve this I add a hibernate property, and its work fine against the ID jumping.

<property name="hibernate.id.new_generator_mappings" value="true"/>

The real problem now is already existing entities with Auto generation strategy has getting exception like

Cannot insert explicit value for identity column in table ‘USER' when IDENTITY_INSERT is set to OFF.

  • Is there any way to resolve the ID jumping issue in JPA/Hibernate
  • How can I avoid the IDENTITY_INSERT is set to OFF if am going with same fix.

Could you please suggest a better option to generate sequence number that should be unique.

Thanks in advance

Abin Manathoor Devasia
  • 1,945
  • 2
  • 21
  • 47

2 Answers2

1

It sounds like what you're experiencing is this, rather than anything related to Hibernate.

In SQL Server Configuration Manager, right-click on your instance name on the right pane and click Properties then Startup Parameters On the "specify a startup parameter" enter -T272 to set the 272 trace flag.

I get your confusion and frustration, but ultimately, you shouldn't really need to worry about what your next identity value will be.

There's also a closed Microsoft Connect post indicating that this is the way it is with some other possible workarounds.

Community
  • 1
  • 1
InbetweenWeekends
  • 1,405
  • 3
  • 23
  • 28
  • thanks, my requirement is like Id should be sequence value stating from1000 and increment 1 by 1. I don't think its problem with JPA/Hibernate. But Its a technique by MSSQL, am concern about is there any way to control this – Abin Manathoor Devasia Nov 25 '15 at 05:10
  • If this is a requirement, then one recommendation is to roll your own increment solution. Create a table with an integer field. Inside a transaction, on insert, increment the integer field by one, and select this value to use for your pseudo-identity field, then commit. Otherwise - the integer data type can go to just beyond 2 billion - quite sufficient for most applications. I'd recommend just going with it and let the database do what it's supposed to. – InbetweenWeekends Nov 25 '15 at 05:15
1

in sql server, you may not insert a value for an identity column unless you specify SET IDENTITY_INSERT <yourTableName> ON before your insert statement.

t1t1an0
  • 281
  • 1
  • 16