--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>...
> >idea. It's interesting that it's always the same index that get's
> >corrupt. So the solution could be to mark received messages instead
> >and delete them from time to time?
>
> Yes. I'm curious about a few things:
>
> 1. Have you been deleting these message records whilst they are
> still uncommitted? From your description I could envisage a large
> batch of inserts where the earlier message records are deleted before
> the batch as a whole is completed and committed.
>
> 2. If these are transient records that are written and not read, and
> there is no foreign key involved, what is the purpose of indexing the
> GUID? What's the purpose of using a GUID at all?
>
> 3. Where is the GUID coming from? Is it a UDF? or some mechanism
> where the GUID is being created on the application side?
>
> 4. Are your applications doing these multiple concurrent batch
> inserts in Autocommit transactions?
>
> ./heLen
>

...thank you for pushing me into the right direction!
After analysing my Delphi code the scenario is as follows:

I'm doing a query to get a result set (using an IBQuery
component). Then I am iterating through my result set,
reading and deleting each record using a SQL statement
(using an IBSQL component, depending on the same trans-
action). To delete a record I'm using the FGUID field
(the GUIDs were generated by a Windows API call) and
doing a CommitRetaining while the dataset is still open
and in use => this corrupts the index.

The settings are:
read_committed
rec_version
wait

Unfortunately this worked w/o problems except from the
database corruption from time to time (sometimes weeks).

Mark