set option public.global_database_id=? locks DB server

[André_Schild]

Hello,

we are still using SQL 10.x (Yea, know, not supported any longer) with global autoincrement in a sql remote replicated environment.

To set a new database_id when needed, we have defined a event which is fired when less than 5000 freed ID's remain. This is fired approx. every 3 months, and worked just flawless in the last 7 years. But this morning.... it did not.

We had freezes of the database engine, such as it was still listening on the TCP port, but connections could not be established. But they did also not return a error or timeout.

After a log of searching (Database validation, lock clients out, restart engine etc.) we did find out that the system did get freezed when it did fire the "GLOBAL AUTOINCREMENT near end of range" trigger.

We have a table which holds the available free range ID's. The code fo the event is this:

begin
  declare @dbID unsigned bigint;
  declare @cmd varchar(500);
  select min(GlobalDBId) into @dbID from dba.db_glob_ids where current publisher = DatabaseID and Available = 1;
  set @cmd='set option public.global_database_id = '
    +cast(@dbID as varchar);
  execute immediate @cmd;
  message 'Setting new dbID with [' || @cmd || ']' to console;
  set @cmd='update dba.db_glob_ids set available=0,taken_at=current timestamp where globaldbid='
    +cast(@dbID as varchar);
  execute immediate @cmd
end


Any idea why it could have freezed the whole engine this morning?
(Monday morning is the peak day in number of transactions on that database)
So after reducing the number of concurrent accesses suddenly the global_database_id what set correctly by the event/trigger.
The System is running under Debian Squeezy, 64 bit, with SQLA 10.0.3.3900 - 64Bit

[Former Member]

I don't see any fixes that apply to either the setting of the global_database_id option or execute immediate or your update but you should probably check to see if the behavior is current in: the last 10.0.1 EBF and the current releases; just in case there is something fixed.

But I suspect you are just blocked on resources. Did you step through this event/procedure using the debugger to see which exact statement is blocked?

One other possibility, if you are running on a multi-core machine, is the possibility that parallelism has entered the equation and you are seeing a limitation that was not fixed in version 10. This can pop up if the application is moved to a machine with more cores (for example). As such, you might want to set the option max_query_tasks=1; as the workaround until you can port to a current release.

Best of luck.

[André_Schild]

The server has 8 active cores, so it might well be this problem. Since it was stuck on a productive system we had not time to step through it via debugger. Anyway, it did work with less db load, so it might well have been a concurrent effect. Thanks for your ideas on the subject.

[VolkerBarth]

Just as a very wild guess: Is it possible that an GlobalAutoincrement system event can be triggered more than once at the same time? If so, would it be helpful to embrace the handler code that sets a new global_database_id with an "if NumActive = 1" condition?

[André_Schild]

The event definition is number of free id's < 5000, so it could be triggered multiple times. But I'm not sure a trigger at exactly 5000 free id's is guarateed to be triggered (One and onyl once)

[VolkerBarth]

I would think that the event stops getting triggered as soon as the new global_database_id is set (though I don't know whether that option's change has an immediate effect on the PK generation or requires a checkpoint to do so), so the typical "RemainingValues < n" condition seems reasonable. (And we do use that as well without problems.)

However, my actual question was focussed on the "at the same time" part, in other words: whether that event may run in parallel when several connections try to insert PKs into the same table and reach the specified "RemainingValues" nearly at the same moment, so the event cannot already have changed the option in-between...