Restore the current value of the sequence from database failure on ASA12-database

I noticed that if there is "assertion failed"-error, then restore the database from backup copy and current log (for example, using -a database option) incorrectly restores the currval/nextval of sequence.
Probably, this is normal behavior because the checkpoint has not been confirmed.
Is it really?

sa-12 sequence

asked 09 Aug '16, 10:10

Ilia63
1.2k●50●55●78
accept rate: 44%

flat view

One Answer:

active answers oldest answers newest answers popular answers

If your sequence definition is not declared with a NO CACHE clause there is a possibility this can happen. Fatal errors, assertions, power outages, unhandled systems exceptions could all cause the server to stop without a clean checkpoint.

If your 'gap' is less than the default 100 values (or whatever you have your CACHE setting set to) then the above could be your issue.

If your 'gap' is larger I would suspect an incomplete (-a) recovery.

permanent link

answered 09 Aug '16, 12:50

Nick Elson S...
7.3k●35●107
accept rate: 32%

CACHE 100 sounds like a violation of The Watcom Rule, but what do I know?

I use DEFAULT AUTOINCREMENT and...

I also hate Java...

(09 Aug '16, 13:17) Breck Carter

Replies hidden

Thanks Nick.
Now I set the cache as CACHE 20 (INCREMENT BY 1).
Do I get rid of the my problem, if I set the default cache (CACHE 100)?
Or do you recommend 'NO CACHE'-parameter?
P.S. In my opinion, the use of 'NO CACHE'-parameter can degrade application performance.

Thanks Breck.
In my application numerous customers can request own ID at the same time. I am afraid to use 'DEFAULT AUTOINCREMENT', as I expect problems of locking the table (with 'DEFAULT AUTOINCREMENT') in this case.

(10 Aug '16, 02:24) Ilia63

Changing that to NO CACHE should prevent that entirely, but could have an affect upon performance.

Going to the default cache of 100 would increase the pential for an even bigger gap than your current setting.

In contrast assertion failures, fatal errors, unhandled expections, and system level shutdowns should be rare so the tradeoff is a function of rarity of the current risk verses the impact/consequences if/when it occurs.

(10 Aug '16, 09:03) Nick Elson S...

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-12 ×438
sequence ×6

question asked: 09 Aug '16, 10:10

question was seen: 2,245 times

last updated: 10 Aug '16, 09:03

SAP SQL Anywhere

Restore the current value of the sequence from database failure on ASA12-database

Follow this question

Related questions