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 >
