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:
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
asked 10 Nov '14, 10:07