by I posted this question a while ago and some one asked for more details.
Question for INGRES buffs.
I have a database with 4 master tables and 3 detail tables.
I have INGRES version 6.3 running on VMS.
I wrote the application in ABF (INGRES 4GL), the SQL query is :
select a.call_date, a.notes, a.id, b.id
from mstr_a a, notes_tbl b
where a.id = b.id
order by a.call_date
Table mstr_a is s hash unique on id, the record length is 312bytes.
Table notes_tbl is hash on id and the record length is 104.
Table mstr_a has one other index. Table notes_tbl has none. Table mstr_a
has 42,000 records and table notes_tbl has 130,000 records.
The involved variables data types are:
a.id, b.id Varchar(14)
call_date date
notes varchar(66)
The application was designed with reasonable care (my opinion since I
designed it) there has been nolocking problem in the 3 months that it
has been out there. There are ususally 9-12 users on the system. The
application is designed so that I do not hold any locks for any extended
amount of time. (I take a snapshot; do a commit; and work with it).
Everytime I do a delete, update and insert I commit immediately. I
perform error trapping every time I access the database.
Sounds resonable I hope.
We had an INGRES consultant from INGRES CORP. and he did not accept iT
when I had users complain about the following problem. I have not been
able to replicate it.
I have had users (2) on 2 different occasions complain that previous
records (sorted by date) are missing. But have reappeared later.
Unfortunately I was not around when it happened. There was no error
message and no error number returned (there is an error log file). The
users could not have changed the system because they only have select
and insert grants. Also the call_date field in the apllication is filled
in by the system (notes_tbl.call_date = 'today'), also on the form in
the application it is a display only field.
What has happened is this : The users sometimes do not see a bunch of
records from the notes_table. I am not absolutely sure that this is
happening since this is only the users complaint. The last one was a
month ago and I have not made a new upgrade since then but it has not
been reported again. However a colleague says that he may have seen
something like that but is unable to confirm it (and he is a pretty
reliable programmer).
I hope this is not as serious as it seems but I would like to know if
anyone has faced anything similar. Or all the other rdbms experts if
they would like to make a guess as to why this happens.
Sanjay
408-439-7182