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. > >I see the lock modes WAIT and NOT WAIT, but I think I need a different >alternative like - SKIP or something like that. > >Am I trying to do the impossible, or do I just need a little help? 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. June -- june_t@hotmail.com Grounded in Palo Alto, living on M&M's (plain)