Sequence number not decremented when rollback

Classic Servoy
1

Hi everybody,

I’m using Servoy Sequence to create unique numbers for records. Simple one with step size = 1.
The problem is that when I rollback a record creation, the number is lost and the next new record will have the next number and not the rolledback one.

E.g. if the last record added is the number 105. If I create a new record, it uses the number 106. Then I rollback this new record (nothing saved in database). Then I create a new record. This last one will have the number 107.
So now I have a gap. There is no record 106.

Is that a bug of Servoy or there is a way to avoid it?

2

That is expected. Why does it matter? You will also have “holes” if you delete records…

3

patrick:
That is expected. Why does it matter? You will also have “holes” if you delete records…

Deleting records is much more rare than undo a new record form (in our case). And this number is used in lists so having many gaps is not really “professionnal”, even if it’s not the fault of the application :roll:

4

It is really not possible what you want, you must write your own sequencenumbers if you really don’t want gaps.

Look at the following situation:

User A BEGIN TX
User B                               BEGIN TX
User A newRecord (nr 105)
User B                               NewRecord(nr 106)
User B                               COMMIT TX
User A ROLLBACK TX

So what do expect? Next time you get 105 and then 107?

You must use your own sequencetable and use locks:

User A BEGIN TX
User B                               BEGIN TX
User A lock countertable
User A newRecord (nr 105)
User A UPDATE countertable
User B                               lock countertable
User B                               wait till lock on countertable (by user A) is released
User A ROLLBACK TX
User B                               retrieves lock on countertable
User B                               NewRecord(nr 105)
User B                               UPDATE countertable
User B                               COMMIT TX

Martin

5

Yes I also though about this concurrency case. But I didn’t know if Servoy handled this case or if it simply just worked as I noticed.
I’ll not develop my own sequencenumbers, that’s sure. Too much other things to do before spending lot of time on something which will just make the application look nicer :lol:

Thx anyway for your help :D

6

In case you’re not allowed to have gaps (for example invoicenumbers) you’ll have to use your own sequencenr tables.
Otherwise will get in troubles with the tax department (they don’t allow gaps in sequence nrs)