Thanks Dmitry,

i will try to simulate this to see how 2 wait transactions that locks each other ... thanks i will come back here with the result !

>
> 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, www.ib-aid.com
>