Re: Set transaction isolation level read uncommitted?

Koen Van der Elst wrote: > > Usign SET ISOLATION TO DIRTY READ allows you to switch between > dirty-reads and non-dirty reads during the SAME transaction. > Advantage : you can perform a dirty-read, switch to non-dirty-read and > read the same record again. If you can read it, it has been commited. > This is a work-around for the rather silly index-locking mechanisme > used by Informix. > > record 1 : committed > record 2 : committed > record 3 : NOT committed > record 4 : committed > record 5 : committed > > If you all read records in sequence, you'll hit number 3 and you won't > be able to read beyond it. If you use the method I mentioned above, > you can skip number 3 and continue with number 4. > > Koen Yes, I agree that there are advantages to use SET ISOLATION. But the advantage is exactly what I want to avoid. The definition of a transaction says: only "atomic expression" can constitute a transaction. In the above case, I would use READ UNCOMMITED and committed after every row. I only need to handle the locking in a single block of code, and the locked row can either be skipped or waited. It depends on the application. The latest SQL92 standard still does not support "changing isolation level in the middle of a transaction." I don't remember who said it, but I heard that "the result is unpredictable." Chao