Subject Re: [firebird-support] Re: what is the most fastest isolation level ?
Author Dmitry Kuzmenko
Hello, nathanelrick!

Friday, March 2, 2012, 9:48:32 PM, you wrote:

n> no one have an idea about what is a

n> deadlock
n> Error: 16

n> ?

n> the isolation of the transaction was: isc_tpb_read_committed +
n> isc_tpb_no_rec_version + wait => normally no deadlock must appear ??

Maybe you have 2 wait transactions that locks each other.
Since you are using no_read_committed, it "locks" even
on reading, so, any reading in trigger, etc, can cause
real deadlock (since you said that you have "ddl is a little long").

>> i know the behavior of each, but i need to know the difference in speed / resource usage between each of them ...

no speed difference or resource usage between rec_version and
no_rec_version. And even more, I can say that only shapshot
(concurrency, consistency) transactions consume resources, and
resourses is the size of local copy of TIP for that transaction.

n> what is the most fastest isolation level ?

No one. When transaction works alone, none difference in speed, for
any isolation level. Versioning engine doesn't place any locks
somewhere in DB or memory.
But, when you start update and delete records, you produce
versions, and here is the main performance source - there more
versions transaction reads to understand what it can show
and what not to show, the slower reading will be.

For me no_rec_version itself is the worst case, nearly useless.
I even wonder why it exist - versioning engine designed not
to block readers.
And wait - also not good for most cases. It's again a versioning
engine, not designed to wait on locks.
So, I prefer application to know the problem exactly when
it happens, not to wait for unknown time and then ... oops!,
whe have lock conflict.

Dmitry Kuzmenko,