Locking issue
Hi All, Is it possible to retreive data on a table even if there some rows locked by another transaction without using DIRTY READ isolation level? We would like to be able to retreived only committed data from a table even if it's currectly being modified by someone else. Informix returns an error instead. Thanks, Steeve steeve_boulanger at baan dot com -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
steeve_boulanger@baan.com wrote: > Is it possible to retreive data on a table even if there some rows locked > by another transaction without using DIRTY READ isolation level? > > We would like to be able to retreived only committed data from a > table even if it's currectly being modified by someone else. Informix > returns an error instead. If you mean, you want to skip over the locked rows, yes, it's possible, but it isn't pretty. See the Unofficial FAQ at http://www.geocities.com/SiliconValley/Bridge/4578/faq.html . Next question: Why would you want to do that? Seems to me that this is even more misleading than using Dirty Read (depending on what your application is). Suppose you're my bank, and at the exact moment that you are running your report, I am withdrawing money from the ATM. Here are your choices: - Use Dirty Read: depending on when you hit it, you could get $1,000,000 (yes, I'm fantasizing ;-) or $999,980. - Use LOCK MODE WAIT: your program waits a little while, and then gets $999,980, the amount after I've finished. - Skip the locked row: you think I don't even have an account at your bank. Boy will I be peeved when you tell me I have no money. June -- june_t@hotmail.com Grounded in Palo Alto, living on M&M's (plain)
June Tong wrote: > steeve_boulanger@baan.com wrote: > > > Is it possible to retreive data on a table even if there some rows locked > > by another transaction without using DIRTY READ isolation level? > > > > We would like to be able to retreived only committed data from a > > table even if it's currectly being modified by someone else. Informix > > returns an error instead. > > If you mean, you want to skip over the locked rows, yes, it's possible, but > it isn't pretty. See the Unofficial FAQ at > http://www.geocities.com/SiliconValley/Bridge/4578/faq.html . > > Next question: Why would you want to do that? Seems to me that this is even > more misleading than using Dirty Read (depending on what your application > is). Suppose you're my bank, and at the exact moment that you are running > your report, I am withdrawing money from the ATM. Here are your choices: > - Use Dirty Read: depending on when you hit it, you could get $1,000,000 > (yes, I'm fantasizing ;-) or $999,980. > - Use LOCK MODE WAIT: your program waits a little while, and then gets > $999,980, the amount after I've finished. > - Skip the locked row: you think I don't even have an account at your bank. > Boy will I be peeved when you tell me I have no money. I think there is another possibility: - To get the last commited value of the locked row, when reading it. In that case the report will get $1.000.000 (yeaahh!) until you commit your transaction, consistently, so it would be better than dirty read. A "SELECT ... FOR UPDATE" would wait for the lock. A simple "SELECT .. " would get the last committed value. I don't know wether Informix supports this (I think not), but I think there might be performance issues making this behaviour acceptible. But there can been situations where this can cause some problems also. > > > June > -- > june_t@hotmail.com > Grounded in Palo Alto, living on M&M's (plain) Tamas Posa
June, I don't want to skip over the locked rows, but I'd like to be able to see the "old" value. As in your example, suppose I'm your bank. At the same time you're at the ATM looking at your balance account, let's say I'm making your monthly interest deposit and instead of $10 I enter $1,000 (but I haven't committed yet). I'd like you to see your balance of account without the $1,000 mistake. With dirty read, you'd see the $1,000 mistake, and with another isolation level you'd get a locking error! (which we don't want). And in our situation, we have VERY long transactions (we do not have the choice, beleive me!), so the LOCK MODE WAIT won't work for us. With Oracle, we can retreive the "old" value even if it's being modified by another session. The "new" value will be accessible when it will be committed. So we'd like the same behavior in Informix. Is it possible? Regards, Steeve In article <369C066F.2E4C34D1@hotmail.com>, June Tong <june_t@hotmail.com> wrote: > steeve_boulanger@baan.com wrote: > > > Is it possible to retreive data on a table even if there some rows locked > > by another transaction without using DIRTY READ isolation level? > > > > We would like to be able to retreived only committed data from a > > table even if it's currectly being modified by someone else. Informix > > returns an error instead. > > If you mean, you want to skip over the locked rows, yes, it's possible, but > it isn't pretty. See the Unofficial FAQ at > http://www.geocities.com/SiliconValley/Bridge/4578/faq.html . > > Next question: Why would you want to do that? Seems to me that this is even > more misleading than using Dirty Read (depending on what your application > is). Suppose you're my bank, and at the exact moment that you are running > your report, I am withdrawing money from the ATM. Here are your choices: > - Use Dirty Read: depending on when you hit it, you could get $1,000,000 > (yes, I'm fantasizing ;-) or $999,980. > - Use LOCK MODE WAIT: your program waits a little while, and then gets > $999,980, the amount after I've finished. > - Skip the locked row: you think I don't even have an account at your bank. > Boy will I be peeved when you tell me I have no money. > > June > -- > june_t@hotmail.com > Grounded in Palo Alto, living on M&M's (plain) > > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
steeve_boulanger@baan.com wrote: > June, > > I don't want to skip over the locked rows, but I'd like to be able to see the > "old" value. As in your example, suppose I'm your bank. At the same time > you're at the ATM looking at your balance account, let's say I'm making your > monthly interest deposit and instead of $10 I enter $1,000 (but I haven't > committed yet). I'd like you to see your balance of account without the > $1,000 mistake. > > With dirty read, you'd see the $1,000 mistake, and with another isolation > level you'd get a locking error! (which we don't want). > And in our situation, > we have VERY long transactions (we do not have the choice, beleive me!), so > the LOCK MODE WAIT won't work for us. > > With Oracle, we can retreive the "old" value even if it's being modified by > another session. The "new" value will be accessible when it will be committed. > So we'd like the same behavior in Informix. Is it possible? No, Informix doesn't do that. At least, it doesn't unless you code it yourself, using temp tables or whatever other avenues your own creativity will open up for you. Sorry. June -- june_t@hotmail.com Grounded in Palo Alto, living on M&M's (plain)