Re: LOCK IN ROW

Frido van Orden (FridoO@faapartners.com) wrote: : The problem probably occurs because your query requires a full table : scan on the table in which you have locked the record. Or requires scanning an index which is not sufficiently selective. Even if you are using an index, if the row one user has locked has the same index value as the one you are looking for (but other columns differ), you will get a locking error. For example: Customer table contains columns fname, lname, address Index on customer(lname) Data contains rows: "Mickey" "Mouse"; "Minnie" "Mouse" User1: UPDATE customer SET address = "Disneyland" WHERE lname = "Mouse" and fname = "Mickey" User2: SELECT * FROM customer WHERE lname = "Mouse" and fname = "Minnie" User1 locks row "Mickey" "Mouse" User2 scans index, finds lname="Mouse", which contains two rows. User2 tries to read first "Mouse" row, it's locked. Note, that since the row is locked and the fname is not part of the index, there is no way to be sure whether the value of fname is "Minnie" or not. If the value used to be "Minnie", but User1 has changed it, User2 should get a lock error. If, as in this case, fname is not and never was "Minnie", then User2 should be able to skip it, and would be able to if the index were sufficiently selective -- ie. if the index had been created on (lname, fname). : (what about something fundamental in relational databases : like 'physical data independence', which assures that queries return the : same results, no matter what execution plan is chosen). You do get the same results, you just also get some locking errors :) : Remember part of the high performance of Informix is due to the fact : that updates are done directly in the database and old values are : written to the log. This means that the only way other transactions can : access the old values is using the log, which is not supported by the : engine, hence the locking problem. : Other RDBMS's (e.g. Oracle) do it the other way: updates are written to : a special log segment which can be used in queries within the same : transaction. Other transactions can find the old values in the normal : table space and hence do not suffer from locks or slow performance. Hmmm... I'm not an expert on Oracle, but I always thought it was the other way around; updates are done to normal table space (just as ours are), and old values are stored in rollback segments. So I don't think this has anything to do with why our performance is better :) I have no idea whether the queries having to look in the rollback segments for their data affects their performance. Also, I believe that Oracle's read consistency is just one of the levels of isolation they offer; I'm assuming others would return lock errors (if not, I don't think they'd be very useful, so I don't mean this as a slam on Oracle), just as another isolation level we offer would NOT return a lock error. Also (again), Oracle's read consistency would introduce another possibility which the poster might not like: not knowing whether someone had updated the row that he IS looking for. That is, the original problem was that when he was querying for Minnie Mouse, he got a locking error because someone had locked Mickey Mouse. If you use Oracle's read consistency (or whatever it's called), you not only don't get a locking error when someone has locked Mickey Mouse, you also don't get a locking error when someone has locked Minnie Mouse. If that's not okay, then this isolation level is not for you. Again, I don't mean to slam Oracle's features; I'm just saying that it doesn't necessarily address the original poster's problem. Anyone with more technical knowledge of how Oracle works, feel free to jump in at any time... June ---- June Tong Informix Software ---- ---- Senior Consultant (650) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Ashford, England ---- * * Standard disclaimers apply * - Please do not send me requests/questions by mail. When I have the knowledge - and time permits, I try to answer questions on comp.databases.informix, but - travel schedule, time, and volume make responding to personal requests - difficult and often slow. Please call your local Informix Technical Support - organization for assistance with technical issues.