[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3608) DB sequence numbers are not unique when using the pooled SequenceStyleGenerator in multiple JVMs with the same DB

[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3608?page=c... ] errorken commented on HHH-3608: ------------------------------- Ok, I looked at this code and according to me this implementation is like completely fubar, or else I'm missing something major here. This solution will only work when there is only ONE sessionFactory active for the table/sequence. Multiple sessions will not trigger duplicate id's because of the proper synchronization. However, this solution will NOT work (and thus allow duplicate id's) when more then one system is using the same table and sequence. I say system, since the other system might be a JVM with hibernate (using the same SequenceStyleGenerator) or another type of system that inserts in the same table and uses a similar mechanism for caching id's. First part of the problem: When the 'value' and 'hiValue' are initialized the first time, the sequence is called TWICE (as stated in the comments above) See Line 173 and 181 of the nested class PooledOptimizer in OptimzeFactory. Apparantly the second call is done to calculate the hiValue. My first thought was; why? Why don't we simply take the increment_size ? Like; value= getValue() and hiValue = value+incrementSize ? Anyway: For example (A = application, T = transaction, increment size = 20) : A1 T1 : Line 173: value = getValue() ---> sequence returns '1' A2 T1: Line xxx : select nextVal from seq ----> sequence returns '21' A1 T1: Line 181 : hiValue = getValue() ---> sequence returns '41' Now hibernate will give out id's starting from 1 untill it value >= hiValue. In other words hibernate will keep giving id's untill we reach 41. But wait, the block starting from 21 till 41 has allready been given to application 2 => so we will get duplicate id's starting from 21 When getValue() is called at line 181, an external system might have called the sequence just before. There is no guarentee that the call at 181 will return a sequence value that is sequentially after the first acquired block. Synchronization here will only prevent this within one JVM/SessionFactory, but not with multiple systems. Second part: Ok, supose the initialization has happened without a problem. We were lucky and the two sequence calls returned two sequential blocks. So value is '1' and hiValue is '21' At some point in time we will run out of id's and a next call to getValue() will be done. This happens in the code at line 183. Hower, while our first, hibernate, application was inserting, the second application did a 'select nextVal from sequence'. The sequence counter is now 41. So, at line 183 we will retrieve the nextValue() and hiValue becomes '61' Now we calculate the value by subtracting the increment size, making it 41 (so value = 41 and hiValue = 61) But wait ! application2 is already using 41-61 ! => duplicate id's Third part: Allthough not a real problem, doing the getValue() twice will always result in one 'block' of id's getting lost. My increment size is 20, so when my application (and my application alone) is using the sequence I would expect the sequence to start from 21 when I restart my application. So, I start my application for the first time and I insert 3 records. The sequence is at its initial value, so the id's in database will become 1,2 and 3. Next, I restart my application, and again insert 3 records. But suddenly my id starts with 41 ? Conclusion: the solution presented by Adrian Shum seems to be the correct one. Thats the one I was expecting to see in the PooledOptimzer in the first place. But maybe there are special reasons while its implemented otherwise? -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira