Hi Tom,

We have an issue with one of our select queries. The query returns in-consistent results (at times) for the same input.

The query is fired from a Web Application (named PIPES) using JDBC templates (Spring). A Transaction is created for the Select query.

Query and Table (ACCOUNT) details given below

SELECT channel_account_id "existingAccountId" FROM PIPESSCHEMA.ACCOUNT WHERE mac = 'xxxx'

We are pretty sure that at the time of the 'select' query for a given 'mac', there is no INSERT or DELETE happening for the same 'mac'.The code has been reviewed multiple times.

100 percent sure that there are no inserts/deletes happening.All Logs have been turned on.The issue could not be replicated in Testing environments.It happens only in Production and that too intermittently.FYI- The testing environments Databases have only one schema, whereas the PROD database, which cates to multiple Web Apps has multiple schemas (and there is a Table by the name ACCOUNT in more than one schema)

I am pasting below the snippet from the Production Logs. At 5.12 PM the query returned 9 Account IDs for the 'mac' 32641870. At 5.19 PM in a span of 7 minutes, the same query returned only 1 Account-id. No inserts or deletes have happened during that time.

Session -1 : 2016-06-29 17:12:45+1000,295 log_level=DEBUG, event_source=UpdateAccountsTable Accounts from DB for Mac 32641870 in Thread 672 is [COR000174227-182182-AU-AUD, COR000111559-182182-AU-AUD, COR000111567-182182-AU-AUD, CRD53b432ce5db8cf2022c584b4295e64ec6dbde9d6-646a2ae9d23b6ba91168e4ff7fd26978, CSH000123796203, COR000184226-182182-AU-AUD, COR000184564-182182-AU-AUD, COR000184622-182182-AU-AUD, COR000184630-182182-AU-AUD, COR000184820-182182-AU-AUD]

Session -2: 2016-06-29 17:19:32+1000,900 log_level=DEBUG, event_source=UpdateAccountsTable, Accounts from DB for 32641870 in Thread 503 is [CRD53b432ce5db8cf2022c584b4295e64ec6dbde9d6-646a2ae9d23b6ba91168e4ff7fd26978]


Both Session-1 and Session-2 create a Transaction and execute the same query which is

SELECT channel_account_id "existingAccountId" FROM schema.ACCOUNT
WHERE mac = :mac

The 'mac' is 32641870.

Session-1 and Session-2 do not insert/delete . They just do a 'Select'. There are no jobs running in the Database that removes/adds Accounts.


There are no deletes/inserts happening for the 'mac' by another session between session-1 and session-2. We have verified this by adequate placing Log-Debug statements. Exhaustive code-review was also done and this has been ruled out.


There are multiple schemas in the same Database and all of them have a Table named - ACCOUNT. We have added the schema name (PIPES) to the query but the issue still persists.

Is this an oracle related issue. A thread pointing to a similar issue is in the below link. Please have a look

http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l/has-anyone-seen-oracle-return-a-different-result-set-when-the-same-query-is-run-multiple-times-4083907


ACCOUNT TABLE

'CREATE TABLE ACCOUNT
(
ID NUMBER NOT NULL,
CHANNEL_ACCOUNT_ID VARCHAR2(100) NOT NULL,
ACCOUNT_ID VARCHAR2(100) NOT NULL,
SYSTEM_CODE CHAR(3) NOT NULL,
MAC VARCHAR2(16) NOT NULL,
ACCOUNT_NUMBER VARCHAR2(64),
ACCOUNT_SHORT_NAME VARCHAR2(64),
ACCOUNT_NAME VARCHAR2(120),
BSB_CODE VARCHAR2(20),
BILLER_CODE VARCHAR2(20),
PRODUCT_TYPE VARCHAR2(30),
ACCESS_LEVEL VARCHAR2(30),
CONSTRAINT ACCOUNT_PK PRIMARY KEY (ID) using index tablespace USER_INDEX,
CONSTRAINT ACCOUNT_UK1 UNIQUE (CHANNEL_ACCOUNT_ID, MAC)
)'

Request your assistance.

Please let me know if more information is needed

Thanks,
Kalyan