| Subject | Re: Getting duplicate records in a table |
|---|---|
| Author | Adam |
| Post date | 2006-04-27T10:27:57Z |
--- In firebird-support@yahoogroups.com, "Paul Hope" <paulhope@...> wrote:
other responses.
The EXISTS check will return true if there exists a record that has
since been deleted by another transaction. Because I have an isolated
view of the database, I can still see it, but no other transaction
will after the transaction that issued the delete commits. So I think
I am being clever by not inserting a notification record because hey I
don't need to, whereas the next synchronisation would fail to be
notified of that record.
I do not want to have multiple records in the table due to database
growth issues if the applications do not check their notifications.
But I would rather have multiple records then open the theoretical
possibility of losing notifications. 99% of the time it would work
fine, but it is that other 1% that would slip through any test case
you could set up and become a support nightmare.
Adam
>SP using
> Hi Adam
>
> Maybe I'm missing something here - but why not do the insert with a
> IF NOT EXISTS(...) then INSERT INTO ...?I can not do this for the exact same reason I have specified in my
>
> Paul
other responses.
The EXISTS check will return true if there exists a record that has
since been deleted by another transaction. Because I have an isolated
view of the database, I can still see it, but no other transaction
will after the transaction that issued the delete commits. So I think
I am being clever by not inserting a notification record because hey I
don't need to, whereas the next synchronisation would fail to be
notified of that record.
I do not want to have multiple records in the table due to database
growth issues if the applications do not check their notifications.
But I would rather have multiple records then open the theoretical
possibility of losing notifications. 99% of the time it would work
fine, but it is that other 1% that would slip through any test case
you could set up and become a support nightmare.
Adam
>
> > -----Original Message-----
> > From: firebird-support@yahoogroups.com
> > [mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
> > Sent: 27 April 2006 01:02
> > To: firebird-support@yahoogroups.com
> > Subject: [firebird-support] Re: Getting duplicate records in a table
> >
> > Thanks Radu / Ann,
> >
> > Radu,
> >
> > The table is a notification table for a mini replication of a
> > subset of data.
> >
> > Obviously I could declare the combination of FieldA, FieldB
> > unique in the first instance, but this would mean there would
> > be a possibility of losing information if a notification hit
> > at the same time the application checked in and removed the
> > notification. The isolation level would mean that the
> > notification record insert would fail (which could be caught
> > using the -803 thing), but the application would not see the
> > latest version.
> >
> > Your suggestion however has given me an idea that may work. I
> > could insert a new record in this notification table, then
> > attempt to remove any prior records (ignoring any exception
> > if both a new notification and an application synchronising
> > attempt to delete simultaneously).
> >
> > I will do some tests to see if this will solve my problem.
> >
> > Adam
> >
> >
> >
> >
> >
> >
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://firebird.sourceforge.net 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
> >
> >
> >
> >
> >
> >
>