[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Users are unable to see DML Changes after commit.

Posted on 2007-07-20
17
Medium Priority
?
1,145 Views
Last Modified: 2013-12-18
Hi GURUs,

Here is another interesting thing came up in my environment. My users are complaining that they perform DML on tables some of them can see data others can't and sometime after few minutes some time after reboot sometime after a day user can see that data and sometime they never see that data and uses the other user's terminals and user id (System's USER ID not Oracle because it's the same user at the backend)
to see the reports.

Again My front end is Oracle 6i and Backend is Oracle 10g R2.
It sound illogical to me that this may be a parameter setting issue since some of the users can see data, but may be since it's my opinion.

Please comment and advice.

Regards,
Zee
0
Comment
Question by:Mateen
  • 5
  • 3
  • 3
  • +3
14 Comments
 
LVL 11

Accepted Solution

by:
sujit_kumar earned 128 total points
ID: 19530917
as far as i know this sound illogical to me too.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 124 total points
ID: 19531860
you need to collect information as to when exactly the issue is seen by the users..i can understand that the issue is vague or sporadic but you need to get some pattern information from the users and then we can analyze as to what could be the problem
0
 

Author Comment

by:Mateen
ID: 19532285
Thanks for you guys quick reply:
My friends here is a little clue I am able to find through internet surfing it may or may not be the issue I'll check when I'll return to work on Monday.

Sync (On Commit) new in Oracle 10g
Creating an index with this option might look like this:
  create index my_index on my_table (text_col)
  indextype is ctxsys.context
  parameters ('lexer my_lexer sync (on commit)');

This means that a sync is launched immediately after the commit occurs. The way this works is slightly unusual, in that the SQL call to commit will not return until the sync is complete, and yet the sync is done in a separate transaction from the main commit.
So from the point of view of the user calling the commit, the index will appear to be updated as soon as the commit completes. However, another user might see a period when the rows have been committed to the table, but the index has not yet been updated. The period during which this occurs will depend on the amount of data to be indexed - that is, how long the sync takes. Using Sync (On Commit) can be bad for index performance if small numbers of rows are committed at a time. On the other hand, if the commit is only called every few thousand rows, this might be a reasonable option.

You comments on this are appreciated.

Thanks again,
Zee
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Expert Comment

by:Dr_Billy
ID: 19533393
May be you need to monitor the DB activities for a while , or may be rubn a trace for a certain user's transactions  that doens't see the data as you see , and see what's happening in the backend ...

since that sounds illogical to me too !
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19534620
Your question was about DML activity (which includes: insert, update and delete statements) but the example you found describes a particular type of DDL statement ("create index...").  DDL statement and DML statements are two different things in Oracle.  DDL statements are automatically commtted immediately.  DML statements are not.  I don't think that particular example that you found explains what your users see.

It is possible (and common in some applications) for a user to make a number of changes (inserts, updates and/or deletes) but not commit them immediately.  The user who made the changes will see them all, but no one else does until the user (or the application) issues a "commit".  At that point everyone can see them *IF* they repeat a query or start a report *AFTER* the commit.  If they had a query screen open though with data displayed, or started a report before the "commit", they will *NOT* see the latest DML changes from other users that are not committed.
0
 

Author Comment

by:Mateen
ID: 19543405
My Friend Markqeer,

I guess you did concentrate on the final comment it tells u that the delays may occur due to this indexing . I am quoting the point of intrest three sentences here read it carefully please:

"So from the point of view of the user calling the commit, the index will appear to be updated as soon as the commit completes. However, another user might see a period when the rows have been committed to the table, but the index has not yet been updated."

SO it may be the above case with me but as I said the backend DB user is SAME. Even in above mention sentence the period of delay may occur if the DB user are different.

I just qouted it hear to pop another stream of ideas for brainstorming. I agree that example in text is a DDL but it is impacting a DML operation and this is the point to be noted here.

Thanks and regards,
Zee
0
 
LVL 7

Assisted Solution

by:vishal68
vishal68 earned 124 total points
ID: 19545523
This clause is applicable only to Oracle Text Index and not to normal indexes. Is your application/database using Oracle Context objects ? if yes then this can be a reason for the delay, if no then this is not applicable.

HTH
Vishal
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 19546634
Please tell us:
1. Does your application include Oracle Text indexes, or not?
2. Does your application allow users to make multiple changes before doing a commit, or not?
0
 

Author Comment

by:Mateen
ID: 19553838
My Friends,

Q1: Does your application include Oracle Text indexes, or not?
No, It doesn't
Q2: Does your application allow users to make multiple changes before doing a commit, or not?
Yes, But the issue is that some application users not database users can't see data DB user is same for all application users.

Then I login with a different Application user which is application super user run the same report it shows me data and relogin with the who wasn't seeing data and it start seeing it. Again on this option or report the rights in application are same for super user and normal user.

I don't know what to check in the database. I came back after a long time in development and DBA side, means I working as a Project Manager then I relocate and got this job as interim solution. So if you guy could be a little more elabrate on answer i'll appreciate that.

Thanks and Regards,
Zee
0
 
LVL 7

Expert Comment

by:vishal68
ID: 19553953
if you are not using Oracle Text then sync on commit has no relevence. In any case as you are saying that the backend database user is same for all users, it would nots t have any impact.

The only reason that i can think of for this strange behaviour is application logic. One reason can be that the reports that you are running are based on some summary views (either application defined or may be materialized views). These views are not getting updated on commit. They are getting updated either at specific time or at specific event. Specific event can be Application Super User running the report. Once the views are updated you can see the data.

I would discuss the problem with Application design team or application vendor to understand how these reports are working

HTH
Vishal
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 124 total points
ID: 19556013
We understand that the DB user is the same for all applciation users, but that is not important.  Each application user likely has his/her own session though in the database.  That *IS* important, because different sessions can see different data at the same time.  Each session sees all of the committed data, plus only what they have changed but not yet committed.  If other sessions are also changing (but not committing) data at that time, the uncommitted data is *ONLY* visible in the session that changed it.  Later, after everything gets committed, everyone sees everything.

O, as vishal68 suggested, there may be some delays in the database of populating materialized views, or the application could be caching some data and only refreshing it at certain intervals (like when the user establishes a new connection).
0
 

Author Comment

by:Mateen
ID: 19622306
Hi Friends,

Thank you very much for your kind opinions and suggestions. And I am sorry again for not coming back to you guys for a while. I was working on this issue and I agreed with vishal that it was a application logic issue or rather logical error.

Actually we have two triggers written on login form key-next-item and when-mouse-click to proceed after entering data mean id & password. I know it sound silly but one of the triggers has a hard coded last year user and password so when user enter then he/shoe login into the currrent year user and when user click he/she enters into last year user so this was the bug in the code.

Which again justify Vishal's opinion of checking application logic. but as a matter of knowledge I am considering to use materialized views for one of my remote site can you guy comment on it. The site will be read only web users only see day end data and for that export and import data delete table and so on which is not a good practice in my opinion. I wana use materialized views replication using script for this purpose.

An other issue we were using & in our report data model/queries for making it generic now e.g.
Select a,b,c
From &table
where column in(&list);
it was working fine up until we migrate to Oracle 10g R2 and just to remind u guys we have 6i as a development environment.

Can u guy give comments on our we can resolve this issue since it says invalid Identifier now on all of the report. We have solve this issue partially like we pass parameter from calling form for a particilar value or if we wana run it  on all data we pass null and in report we usse col1=nvl(particulatvalue,col1) so it partially resolve the issue.

We have two other senarios

1) We are Stock Brokrage House and we have different type of house clearing processess about 5 types. User may run reports on 1 type, 2 types, 3 types or all types like in('ATYPE','BTYPE' .....) which is not resolved with nvl type of solution.

2) At from level user can run report from current transaction, history table or both means combine and select the desired option in parmeter form (not report builder but our own customize para form) for this we need a variable at from level &table.

Or if it is possible to change the query in before report trigger like query='select ....'

Please advice.
Thanks and Regards,
Zee
0
 
LVL 7

Expert Comment

by:vishal68
ID: 19622902
I am not an expert on developer 6i. But i do know that developer 6i has issues working with 10g DB. Oracle certifies only developer 6i with patch set 17 to work with 10g db. Please ensure that you are using developer 6i with patch set 17 that is report version 6.0.8.26.0

HTH
Vishal
0
 

Author Comment

by:Mateen
ID: 19634199
Yes My friend I am aware of it and we already have Patch 18 installed and thats why we are able to run application to some extent.

regards,
Zee
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month17 days, 20 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question