|Subject||Re: Bad design causing problems|
> Thanks. Pavel pointed this out too. I had thought a PK Select waslikely
> to be faster, so if the record was already in the table, I'd have savedNot really. You have a constraint declared on the field. That means
> some time.
that if Firebird is doing its job properly, it must prevent you
inserting a duplicate value. The only way it can do that is if it
checks first. So even if you do a check first, Firebird isn't going to
trust you on your word, and because of the isolation of your
transaction, you can not totally trust the result of the select as a
proof it does not exist.
It doesn't hurt to select first, it just takes time and doesn't
> >After receiving the exception, you say you wait. You do not sayZeos as my
> >whether it is the same transaction though.
> Mostly because I'm not sure. I think I might have to stop using
> connection or spend a day reading up on it. I'm not explicitlystarting a
> transaction in SQL, I've been relying on its autocommit and thetransaction
> isolation level to handle all transactions for me. Likewise, I don'tWith Firebird, you need to understand transactions. There is no such
> rollback at the moment. I've just been opening a query, reading the
> results then closing it again.
thing as a query without a transaction. A transaction is a unit of
work. Your queries must be executed within the context of one, and
every query you execute is treated as atomic. That is, when you
commit, they all immediately succeed, when you rollback, they all
If each adding of an artist is a separate unit of work (which I
suspect to be the case), then you pretty much go
I generally prefer the approach to dump all the exceptions into a
queue to process later. This has the same affect as waiting for a
period of time but at least you get others done as well.
> >If you are using a snapshot
> >transaction, you will never see that record.
> >If you get an exception,
> >then commit/rollback as appropriate before your 30 second wait. When
> >you wake up, Start a new transaction.
> That made my head snap up. This is a the crux of the whole problem,
> it? I'm using the wrong isolation level. What I need to be usingis Read
> Committed.This will certainly help, although it is still possible to end up with
> >Don't use AutoCommit. You can't be confident of when it happens, and
> >for your model you need that confidence.
> I think this is my lack of knowledge showing again. I tried that as
> ditch attempt yesterday, but clearly did it wrong. I turned offSQL blew
> Autocommit, added ";Commit;" to the end of all my inserts and the
> up. I planned to try this again today.Clearly then the record is in the database and your isolation level is
> >Out of curiousity, can you see that record that you are continuously
> >delaying for from another database browsing tool?
> Yes I can.
why no ID was returned.