Re: OnLine 7.10 Locking

Thomas Boldt (tom@cayman.oncontact.com) wrote: : I have been having a serious row locking problem with Online 7.10 : on an IBM RS/6000 J30 with AIX 4.1.2. What seems to be happening : is that whenever a row is updated (and changed), several adjacent : rows (at least 20), or possibly the whole page, is being locked : until the transaction is committed. This is causing the following : error when another user attempts to update a nearby row: Did you look at your lock table (onstat -k) at this point to see how many/which rows/pages are locked? My guess is that you are not using an index (or using too broad of an index) on your update. Even with ISOLATION DIRTY READ, your UPDATE cannot skip over locked rows. If it doesn't know whether the locked row matches your criteria, then it will give you back this error. For example: Customer table (from stores database) WITH NO INDEXES If I execute UPDATE customer SET state = "CA" WHERE lname = "Smith";I lock the row where lname = "Smith". Note that since there are no indexes, I had to do a sequential scan of the table to find this row. Another users tries to execute UPDATE customer SET state = "MD" WHERE lname = "Jones";This also does a sequential scan, and bombs when it hits my locked row, because it doesn't know whether my row matches the criteria. If there was an index on lname, then we would each use the index to find the row(s) that meet the criteria, and there would be no conflict. This also happens when you have criteria on multiple columns but only one is indexed. For example: Customer table (again) with index on lname If I execute UPDATE customer SET state = "CA" WHERE lname = "Smith" AND fname = "Bob";I lock the row for Bob Smith. Another user tries UPDATE customer SET state = "MD" WHERE lname = "Smith" AND fname = "John";He uses the index on lname, but within the index value "Smith", there are multiple rows. I have one of them locked. He will still get an error because he is basically doing a sequential scan on all rows where lname = "Smith". To avoid this, you need an index on (lname, fname). The only isolation level that has any affect on UPDATE is REPEATABLE READ, which will actually lock every row it touches. Other isolation levels just lock the single row that is updated. However, using DIRTY READ on an UPDATE is NOT the same as doing a Dirty Read to select the row, and then only updating the row if it matches. You can't actually do a "dirty update". Hope this helps. June ---- June Tong Informix Software ---- ---- Senior Consultant (415) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Menlo Park ----