Subject RE: [firebird-support] Re: Getting duplicate records in a table
Author Paul Hope
> > Maybe I'm missing something here - but why not do the insert with a
> SP using
> > IF NOT EXISTS(...) then INSERT INTO ...?
> >
> > Paul
> I can not do this for the exact same reason I have specified
> in my 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

How about if exists try to update it, if locked write the new one else
forget it?