long running cursors returning duplicates
Hello everyone, we are experiencing a very silly behaviour with the following scenario: - 120 processes (java/jdbc) creating a cursor on a specific table (no join, roughly 4M rows, statement like 'select * from A where mod(primary_key,120)=xx') - processes are iterating over the cursor (forward only) - runtime 16hours Near the end of the complete process, duplicates appear, not in sequence, but distributed, e.g. [expected,duplicate,expected,..,duplicate,duplicate,..]. The query itself should simply not be able to return duplicates... The process itself on the client is straightforward, it can not happen there. Thus, there has to happen something weird on the server... Does anyone have a clue? Environment: Informix 9.40FC4W3 Solaris 8 04/01, Memory 12GB, Temp 42 GB
On Wed, 27 Oct 2004 06:21:20 -0400, Mathias Mehlberg wrote: > Hello everyone, > > we are experiencing a very silly behaviour with the following scenario: - > 120 processes (java/jdbc) creating a cursor on a specific table (no join, > roughly 4M rows, statement like 'select * from A where > mod(primary_key,120)=xx') > - processes are iterating over the cursor (forward only) - runtime 16hours > > Near the end of the complete process, duplicates appear, not in sequence, > but distributed, e.g. > [expected,duplicate,expected,..,duplicate,duplicate,..]. The query itself > should simply not be able to return duplicates... The process itself on the > client is straightforward, it can not happen there. Thus, there has to > happen something weird on the server... > > Does anyone have a clue? > > Environment: > Informix 9.40FC4W3 > Solaris 8 04/01, Memory 12GB, Temp 42 GB Clue: Your 120 processes are updating rows and the isolation level is less than REPEATABLE READ so they are seeing updated rows again as they are once again encountered scanning the index. Do a set explain on the query I believe you will find that the optimizer is using an index that begins with the primary key but includes one or more columns that your processes are modifying so their entries are moved to another location in the BTREE and encountered again. One solutions is to SET ISOLATION REPEATABLE READ (or SET TRANSACTION SERIALIZABLE) will prevent that from happening. Another option is to use a SCROLL CURSOR even though you are only traversing it one way as the SCROLL CURSOR creates a temp table with the query results and the contents of that temp table are consistent throughout the transaction. Art S. Kagel
"Art S. Kagel" <kagel@bloomberg.net> wrote > On Wed, 27 Oct 2004 06:21:20 -0400, Mathias Mehlberg wrote: > > we are experiencing a very silly behaviour with the following scenario: - > > 120 processes (java/jdbc) creating a cursor on a specific table (no join, > > roughly 4M rows, statement like 'select * from A where > > mod(primary_key,120)=xx') > > - processes are iterating over the cursor (forward only) - runtime 16hours > > > > Near the end of the complete process, duplicates appear, not in sequence, > > but distributed, e.g. > > [expected,duplicate,expected,..,duplicate,duplicate,..]. The query itself > > should simply not be able to return duplicates... The process itself on the > > client is straightforward, it can not happen there. Thus, there has to > > happen something weird on the server... > > > > Environment: > > Informix 9.40FC4W3 > > Solaris 8 04/01, Memory 12GB, Temp 42 GB > > Clue: Your 120 processes are updating rows and the isolation level is less > than REPEATABLE READ so they are seeing updated rows again as they are once > again encountered scanning the index. Do a set explain on the query I believe > you will find that the optimizer is using an index that begins with the > primary key but includes one or more columns that your processes are modifying > so their entries are moved to another location in the BTREE and encountered > again. Yes, the isolation level is set to COMMITTED READ, but actually Ifx is not using the index supplied, instead performing a sequential scan. Yes, we are updating rows but not columns relvant for any index. Actually, this happens in production environment (with 500+ concurrent Users) as well as in the test environment with exclusive use. > > One solutions is to SET ISOLATION REPEATABLE READ (or SET TRANSACTION > SERIALIZABLE) will prevent that from happening. Yes, we might try this. On the other hand, we might just read the rows to be processed into a hashmap (or whatever) and process them locally. > Another option is to use a SCROLL CURSOR even though you are only traversing > it one way as the SCROLL CURSOR creates a temp table with the query results > and the contents of that temp table are consistent throughout the transaction. Unfortunately, AFAIK, JDBC 1.x does not support this... Upgrading is ` not an option. Basically we thought that the resultset would be 'determined' at query execution time, execution time roughly 1 minute. So why should Ifx invalidate the resultset and add rows? Thanks for your reply. Mathias Mehlberg
Mathias Mehlberg wrote: >>Another option is to use a SCROLL CURSOR even though you are only traversing >>it one way as the SCROLL CURSOR creates a temp table with the query results >>and the contents of that temp table are consistent throughout the transaction. > > > Unfortunately, AFAIK, JDBC 1.x does not support this... Upgrading is ` > not an option. > Uhm going from memory, JDBC 1.x does allow you to execute any sql command, so you could do this.