Update Index in transaction?

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 right away after the insert or only after the transaction gets committed. Thank You, Dave in4mixdba@gmail.com --e0cb4efe33de725b8b04d7e7e5d8

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. =

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

You are doing dirty reads. The home row is inserted before the index i= s changed. If during the index insert we discover that the column is inserted is duplicate, then there is a 'save-point rollback' and the operations are undone. So if your query is doing dirty reads and is collecting data via a sequential scan, then you are vulnerable to picki= ng up the row after the insert of the row but prior to it's rollback due t= o duplicated value in a unique index. Cautionary note. If it is a unique constraint and not a unique index, = it is possible for the transaction to set deferred constraint checking whi= ch would mean that the commit would fail, not the insert. Suggestion - use committed read last committed instead. From: "Informix DBA" <in4mixdba@gmail.com> To: ids@iiug.org, Date: 03/14/2013 03:20 PM Subject: Re: Update Index in transaction? [29745] Sent by: ids-bounces@iiug.org Thank you Madison. That is what I thought. However, we have a scenario where user A is doing a dirty read on the t= able 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 ta= ble by doing insert/delete combinations instead of updates (done for variou= s reasons one of which is to generate a new rec_id where rec_id is a seri= al 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 select= ing 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 tab= le in a transaction that the index is only getting updated after the commi= t which would explain why the "group by" to get the dups didn't return an= y 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> wrot= e: > 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 updat= es=3D > > the indexes after inserting a record into the table. Does it happen r= ig=3D > ht > away after the insert or only after the transaction gets committed. > > Thank You, > > Dave > in4mixdba@gmail.com > > --e0cb4efe33de725b8b04d7e7e5d8 > > *********************************************************************= **=3D > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > =3D > > > > ***********************************************************************= ******** > 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. =

Index updates are immediate, however updated rows and index keys will remain locked until the transaction is committed. 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 3:55 PM, Informix DBA <in4mixdba@gmail.com> wrote: > 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 right > 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. > > --e89a8f22beb904431704d7e8b6cc

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

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

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

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 > > > > > > > > > > > > > > ***********************************************************************= > > > > > ******** >

And since we're past March 26.... Here it is: http://informix-technology.blogspot.pt/2013/03/olap-window-functions.html Regards. On Thu, Mar 14, 2013 at 11:44 PM, Fernando Nunes <domusonline@gmail.com>wrote: > 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, > >