chesky@portia.Stanford.EDU (Snehylata Gupta) (07/14/90)
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