Re: lock modes, isolation levels, and scanning

Roger Reynolds wrote: > >Now, assume that transaction 2 (also an update type transaction) > >wants to scan the table with something like > > select * from onsale where perf_id=1> > > >The question is, what should happen when transaction 2 encounters the > >row that transaction one has locked? > > > >What I need is for it to just skip over it (and any such locked > >rows), and return the next matched row from the set. > > > >The default behavior is that the query completely fails, giving some > >message about not being able to position within the table. > > > >Am I trying to do the impossible, or do I just need a little help? June Tong responded: > You just need a little help. Check out my unofficial FAQ: > http://www.geocities.com/SiliconValley/Bridge/4578/faq.html > In the OnLine section (hmm, I wonder if this is the right place for > it), there is the question: > "If I declare a cursor using an isolation higher than dirty read, can > I get it to skip locked rows? It seems like when I hit a lock and > get an error, the next FETCH will return the same error." > > I think this will answer your question. Hi June, I just took a gander (not to be confused with ye olde undomesticated semi-aquatic avian) at your solution. Loved it! I mentioned it to my friend Mike at work here. He asked a simple question: WHY? Why would you want to read under these circumstances? If your query requires correct data (hence, the need for the committed read) then you have sacrificed the accuracy of your query's active set by skipping the locked rows. You certainly wouldn't want to sum on these rows! On the other hand, if that kind of pinpoint accuracy is not needed, you might as well have used dirty read in the first place. Just adapting someone's $.02(US) to stick into this discussion. -- -- Jake (Retrospectively realizes there is no future in hindsight) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+