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

>
> At 05:04 AM 22/04/2007, you wrote:
>
> >After analysing my Delphi code the scenario is as follows:
> >
> >I'm doing a query to get a result set (using an IBQuery
> >component).
>
> Is this a query on committed records or on records that were just
> inserted by the same transaction?

The records are comitted by other FB clients.

> >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 transaction).
>
> Is the user doing that selectively? or is the program using the
> iterate-through-a-dataset approach to perform an operation that the
> user doesn't see? If the latter, why?
>

I see that I have to describe my system a little more in detail.
I've implemented an automation system that needs a mechanism
to send messages between the system's client applications. My
decision was to use a table as a message stack. Each client can
send a message to another system client. The target client
does a query selecting its messages. The reason why I am using the
iterate approach is that I'm using Delphi 2006 and the IBExpress
component set for that. From the Delphi side, it's the easiest and
natural approach to get the dataset by a query (w/ the IBQuery
component) and process each record, then jump to the next until an EOF
occurs. I don't know why, but in my iteration loop I didn't use the
IBQuery's Delete method to do this, but an SQL statement "fired" and
comitted by an IBSQL component. I did use CommitRetaining because my
queries' dataset would close immediatly, depending on the same
transaction. So it's my fault
to commit it at this point.

Using a SQL statement from "outside" the query I need some
identifier. Using my IBQuery's delete method and a single server
I don't need that at all, but for scalability I was planning to
use "distributed messagestacks" somewhere in the future with
a message broker between systems/DBs. For implementing a handshake
mechanism and debugging the message propagation I need a really
unique ID (that should be fulfilled by a GUID generated by the
Windows API).

>...
>
> Deleting from a dataset that is in use doesn't cause corruption, per
> se: it is just a client-side snapshot of what was visible to the
> transaction at the time the SELECT request was submitted. Even when
> you are iterating through a dataset that is out-of-synch with the
> transaction's current view of database state (as you do here) it
> should give rise to an exception, not corrupt data....so something
> *wrong* must be occurring, under certain conditions, in the
> accounting that occurs when the server gets requests that change the
> linkage between an index node and a data page.
>...

...in my case that exception would be hidden by a try...except
block.

>...
> I think there *is* something unhealthy about using this combination
> with your unsynchronised approach. You are operating on an
> out-of-date dataset and your settings are saying "do this delete even
> if you have to wait", whilst others are busy inserting and deleting
> (by the same method) at the same time. The commitretaining means
>...

AFAIK in my scenario, one record will never be used by two different
clients or transactions at the same time.

>...
>
> Another element of risk I see with these client-side generated GUIDs
> is the potential for duplication. Ann could explain far better than
> I, what could happen when a new node is created on an index that is
> still holding the stub of an old node that had the same value.
>

...the GUIDs are generated by the Windows API and are guaranteed
to be real unique.

> I'm still curious to know why you need to use a GUID to identify
> these records...
>

Please see above!

> FWIW, the Paradox-style approach of iterating through a dataset to
> perform batch deletes does not belong in client/server. The only
> situation where it might be justified is where the user is deleting
> records selectively.
>

In first place I am processing the messages, then I want to throw them
away.

> Consider:
>
> With your method, you are pulling over a set that is filtered on the
> criteria you want to isolate the records you want to delete. Then,
> row-by-row, you are reading the (hopefully unique) GUID in order to
> identify one record to delete (even though you really want to delete
> *all* of the records that match your filter).
>
> So -- if you have a set that is
>
> select blah, etc. from atable
> where ...whatever
>
> ...then you can write the statement
>
> delete from atable
> where ...whatever
>
> ...and delete them all as a batch (or not, if an exception occurs).
>
> That's how you should do the batch deletes. You don't need the GUID

for that.

>
> ./heLen
>

Thank you very much!
Mark