We have a central database which uses the GLOBAL AUTOINCREMENT way to generate primary keys in a sqlremote environment.

So far this works fine and the keys are just as they need to be.

We have defined an event which assigns a new global_data_id when the available range is near the end of the range and RemainingValues < 50000

Most of the time this event works fine, but every 1-2 months the DB gets stuck for many minutes before the new global id is assigned, which then raises

I. 05/11 13:13:44. Finished checkpoint of "xxx" (xxx.db) at Mon May 11 2020 13:13
I. 05/11 13:28:27. Setting new dbID with [set option public.global_database_id = 357]
I. 05/11 13:28:27. Setting new dbID with [set option public.global_database_id = 357]
I. 05/11 13:37:47. Setting new dbID with [set option public.global_database_id = 358]
I. 05/11 13:46:29. Setting new dbID with [set option public.global_database_id = 357]
I. 05/11 13:46:29. Setting new dbID with [set option public.global_database_id = 357]
I. 05/11 13:55:05. Setting new dbID with [set option public.global_database_id = 358]
I. 05/11 14:03:45. Setting new dbID with [set option public.global_database_id = 357]
I. 05/11 14:13:07. Setting new dbID with [set option public.global_database_id = 358]
I. 05/11 14:13:36. Starting checkpoint of "xxx" (xxx.db) at Mon May 11 2020 14:13

What is the clean way to increment the global database id when the db is under heavy load?

asked 11 May, 10:23

Andr%C3%A9%20Schild's gravatar image

André Schild
76116
accept rate: 0%

1

FWIW, it this somewhat related to this much older question?

(11 May, 10:50) Volker Barth

Yes, I think it's the very same problem. I have implemented the numActive test, let's see if it helps

The difference is, until one month ago we had ASA 10.x in use, now we are at 17.0.10.6041 and that the load is much higher now

(11 May, 11:03) André Schild

Is it possible that this event runs with more than one instance now and then, trying to increase the GlobalDatabaseID in parallel?

If so, adding a condition within the handler code such as

...
   IF EVENT_PARAMETER('NumActive') = 1 THEN
      ... -- set the option
   END IF;
...

might help.

permanent link

answered 11 May, 10:45

Volker%20Barth's gravatar image

Volker Barth
36.1k342501749
accept rate: 34%

converted 12 May, 05:01

Your answer
toggle preview

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

Question tags:

×6
×2

question asked: 11 May, 10:23

question was seen: 55 times

last updated: 11 May, 11:03