Subject RE: [firebird-support] database and structure problem
Author Svein Erling Tysvær
A large gap would result in a slower database, but results still ought to be correct. However, it does mean that there's a long running transaction, something that may be due to developers not taking proper care of transactions. Though it may be almost any kind of transaction, so if you started a transaction a while ago (60Kb transactions ago) to monitor the activities, then that could be the explanation for the gap.

If we assume that the developers hasn't handled the transactions properly, then I'd say the odds for them not thoroughly considering multi-user access are high. Yesterday, I asked how the sequential numbers where calculated, and I cannot see that you've answered that (except showing an example when they are in order). So I'll specify my question: What happens if two different transactions try to insert a record at the same time? It is unlikely that they'll see each others' record, all (sensible) transaction isolation levels would prevent other transactions from seeing changes done in your transaction at least until your transaction has committed, and some transaction isolation levels would never see any records that were inserted after they themselves were started. So, a sequential number determined through MAX+1 or COUNT or WHERE NOT EXISTS is generally not a good idea in Firebird and a likely cause of error (even more so when the transaction gap increase).

Suppose two records got the same sequence number. How would your stored procedures handle that? If that could be similar to what you observe now, then I guess you've found something to investigate closer.

Ensuring that numbers are unique within a Firebird database is simple when using generators. It can be done without generators, but is not straight forward and I doubt it can be done without a unique index or constraint (well, it can be done if you have one record that everybody modifies, but that would serialize things and be a bottleneck that is against the idea of multiuser access).

There may of course be other reasons for your problem, but it is hard to guess without seeing some of the faulty code.

Svein Erling

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Olaf Kluge
Sent: 20. januar 2009 17:10
To: firebird-support@yahoogroups.com
Subject: AW: AW: [firebird-support] database and structure problem

Hello,

thanks again for help.

The difference is very large:

Flags: 0
Checksum: 12345
Page Size: 4096
ODS version: 11.1
Oldest Transaction: 60347
Oldest Active: 60348
Oldest Snapshot: 60348
Next Transaktion: 119652 !!!
Bumped..:1
Sequence..:0
Next att. ID: 42
Implem. ID: 16
Shadow Count: 0
Page buffers: 0
Next hp: 0
Dialect: 3
Erstellt: This day 7:54AM
Sweep intervall: 20000

Does this difference so great impact on the functioning of the database?



Regards.

Olaf Kluge


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links