IDS Forum

Re: Update Index in transaction?

Posted By: Fernando Nunes
Date: Thursday, 14 March 2013, at 7:44 p.m.

In Response To: Re: Update Index in transaction? (Art Kagel)

With all due respect, I have serious doubts that this situation is related
to DIRTY READ. It's a bad effect of non versioned RDBMS (for the record,
the good effect is performance and simplicity)
When you DELETE a row with key "a" and then you INSERT a record with key
"a" again you have no control of the physical location of the record.
Imagine this scenario: The table has 10M "slots". Record with key "a" is on
position 1000 and gets read by a full scan, then when the full scan is
reading around position 10000 you delete and insert the row in the same
transaction (and you commit that transaction), the new row gets inserted in
slot 100000.... There you have it... the full scan will read the same key
again (different record, but same key). There are several solutions, none
of them ideal perhaps...:

1- Force an index read. This may have severe performance impacts. You
already verified that with this you won't get duplicates
2- Use some condition that can distinguish the records... sometimes the
tables have some kind of insertion timestamp or something
3- That being an ETL process you can "clean" the result, but for that you
need a criteria... Maybe if you have it, you can use it to implement the
previous option
4- Run the full scan in REPEATABLE READ. This will put a lot of locks (at
least one for each record) on the whole table and will prevent the
DELETE/INSERT. This is usually not an option in real situations
5- Lock the table in shared mode. Again, not usually allowed in real
customer situations
6- Extract the data from a secondary server where you can STOP_APPLY
7- Change the DELETE/INSERT for UPDATEs... but I imagine that if you're not
already doing it, you simply can't

After March 26 we can discuss this further if upgrading to the future
version is an option. There will be a solution although at this moment I'm
not quite sure how it would affect the performance. But from an SQL writer
perspective it will be very easy.
Be very patient when you explain this to the developers... they will not
understand... been there... done that.

Regards, and don't forget to attend the March 26 webcast:
http://t.co/bpOIJiFnCJ

On Thu, Mar 14, 2013 at 9:16 PM, Art Kagel <art.kagel@gmail.com> wrote:

> Yeah, for that I would use COMMITTED READ LAST COMMITTED isolation level.
> This requires that the ONCONFIG parameter DEF_TABLE_LOCKMODE is set to ROW
> and that the table's being accessed in the session setting this isolation
> level all have LOCK MODE ROW in their definition (not PAGE). But that
> should be true for every table any multi-user database that isn't a data
> warehouse.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Thu, Mar 14, 2013 at 5:05 PM, Informix DBA <in4mixdba@gmail.com> wrote:
>
> > Thank you Art. The user was using dirty read as they were doing
> sequential
> > scans to unload the table for ETL processing and wanted to avoid get
> > blocked by locks. They were not aware of other isolation levels as
> Madison
> > suggested.
> >
> > Thank You,
> >
> > --Dave
> >
> > On Thu, Mar 14, 2013 at 4:55 PM, Art Kagel <art.kagel@gmail.com> wrote:
> >
> > > Keep in mind that GROUP BY clauses create temp tables for sorting and
> > > grouping, so you may not be 'catching' what you think you are catching.
> > >
> > > Why DIRTY READ?
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > Advanced DataTools (www.advancedatatools.com)
> > > Blog: http://informix-myview.blogspot.com/
> > >
> > > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor
> any
> > > other organization with which I am associated either explicitly,
> > > implicitly, or by inference. Neither do those opinions reflect those of
> > > other individuals affiliated with any entity with which I am affiliated
> > nor
> > > those of the entities themselves.
> > >
> > > On Thu, Mar 14, 2013 at 4:19 PM, Informix DBA <in4mixdba@gmail.com>
> > wrote:
> > >
> > > > Thank you Madison. That is what I thought.
> > > >
> > > > However, we have a scenario where user A is doing a dirty read on the
> > > table
> > > > and selecting all the data. However, they are getting multiple
> records
> > of
> > > > columns a,b,c where the table has a unique index of a,b,c,d. Now all
> > > > throughout the day there is a batch process constantly updating this
> > > table
> > > > by doing insert/delete combinations instead of updates (done for
> > various
> > > > reasons one of which is to generate a new rec_id where rec_id is a
> > serial
> > > > column). However when we query the table in dirty read to try and
> catch
> > > > the duplicate records and run:
> > > >
> > > > set isolation to dirty read;
> > > >
> > > > select a, b, c, count(*)
> > > > from mytable
> > > > group by a, b, c
> > > > having count(*) > 1 ;
> > > >
> > > > we don't get any records returned. This was a bit confusing. However
> > > > after looking at the explain plan, we saw the optimizer was only
> > > selecting
> > > > from the index as the unique index had all the required columns. So
> we
> > > > thought that maybe after the batch process does the insert into the
> > table
> > > > in a transaction that the index is only getting updated after the
> > commit
> > > > which would explain why the "group by" to get the dups didn't return
> > any
> > > > records. However, if the index gets updated right away in the middle
> of
> > > > the transaction, then I would expect the engine to return the dups.
> > > >
> > > > Any ideas ?
> > > >
> > > > --Dave
> > > >
> > > > On Thu, Mar 14, 2013 at 4:00 PM, Madison Pruet <mpruet@us.ibm.com>
> > > wrote:
> > > >
> > > > > immediately.
> > > > >
> > > > > From: "Informix DBA" <in4mixdba@gmail.com>
> > > > > To: ids@iiug.org,
> > > > > Date: 03/14/2013 02:56 PM
> > > > > Subject: Update Index in transaction? [29743]
> > > > > Sent by: ids-bounces@iiug.org
> > > > >
> > > > > Hi,
> > > > >
> > > > > I am running Informix 11.50.FC6. Does anyone know when informix
> > > updates=
> > > > >
> > > > > the indexes after inserting a record into the table. Does it happen
> > > rig=
> > > > > ht
> > > > > away after the insert or only after the transaction gets committed.
> > > > >
> > > > > Thank You,
> > > > >
> > > > > Dave
> > > > > in4mixdba@gmail.com
> > > > >
> > > > > --e0cb4efe33de725b8b04d7e7e5d8
> > > > >
> > > > >
> > >
> ***********************************************************************=
> > > > > ********
> > > > >
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > > =
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > >
> > > > --e0cb4efe33deb89c7004d7e83b0f
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --f46d04016b49e7a82104d7e8bd29
> > >
> > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --bcaec54b48d0ac2ebe04d7e8e0a7
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --bcaec54b48d0508b0204d7e908c4
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

--20cf307f37f4a9225e04d7eb1b4d

Messages In This Thread

• Update Index in transaction?
  Informix DBA -- Thursday, 14 March 2013, at 3:54 p.m.
  • Re: Update Index in transaction?
    Madison Pruet -- Thursday, 14 March 2013, at 4:00 p.m.
    • Re: Update Index in transaction?
      Informix DBA -- Thursday, 14 March 2013, at 4:19 p.m.
      • Re: Update Index in transaction?
        Madison Pruet -- Thursday, 14 March 2013, at 4:27 p.m.
      • Re: Update Index in transaction?
        Art Kagel -- Thursday, 14 March 2013, at 4:55 p.m.
        • Re: Update Index in transaction?
          Informix DBA -- Thursday, 14 March 2013, at 5:05 p.m.
          • Re: Update Index in transaction?
            Art Kagel -- Thursday, 14 March 2013, at 5:16 p.m.
            • Re: Update Index in transaction?
              Fernando Nunes -- Thursday, 14 March 2013, at 7:44 p.m.
              • Re: Update Index in transaction?
                Fernando Nunes -- Saturday, 30 March 2013, at 7:00 p.m.
  • Re: Update Index in transaction?
    Art Kagel -- Thursday, 14 March 2013, at 4:53 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.