RDBMS INGRES query (locking) problem

RDBMS INGRES query (locking) problem

Post by Snehylata Gup » Sun, 15 Jul 1990 03:15:28

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.




1. US-MN Ingres Admin-to $70k--will train strong RDBMS candidates in Ingres / Will Learn Oracle

US-MN Ingres Admin-to $70k--will train strong RDBMS candidates in Ingres

    Be a part of a company that has sales in excess of $1.3 billion
and over 5,000 employees worldwide!  This global leader in the
medical device industry provides innovative, minimally invasive and
cost-effective products and services for the treatment of
cardiovascular and vascular disease.  This team is looking for an
  This qualified individual will be responsible for building,
maintaining and optimizing Ingres databases on the UNIX and VMS
platforms.  This includes production, development, and testing
databases for both business and engineering systems.  This position
will be a member of the central IS DBA team and have significant
interaction with application development and support teams.  Other
responsibilites may include support for UNIX scripting, developing
procedures used for data backup & restoration, database disaster
recovery planning, and SQL optimization.  This key role will be in a
fast paced environment and offers the opportunity to learn ORACLE
Database Administration.
  To qualify you must have a bachelor's degree in Computer Science or
 related field, 3 years experience in IS systems development, support
 or systems administration, minimum of 1 years of Ingres experience.
 Candidates with strong RDBMS background in other systems will also
be  considered and trained in Ingres.  Must have strong UNIX and or
VAX/VMS relational database experience.
  In addition, this company offers the best benefits you will ever
find... no kidding.... they offer a 30% bonus!!! 10 paid vacation
days, paid holidays, tuition reimbursement, on and off-site
professional and personal development programs, on-site daycare, year
 end shut down (the week between Christmas & New Years the whole
company is off, with pay and it doesn't cost you vacation), 401K

  Reference #: 4698-1733

Nicole Burns
The NYCOR Group
4930 West 77th Street, Suite 300
Minneapolis, MN  55435
Phone: (612) 831-6444
FAX: (612) 835-2883

2. Solution: redirected COM port

3. Ingres 6.4.06 : Archival process failure & Locking problem

4. Approach slows to stop?

5. Powerbuilder-ODBC-Ingres: locking problem

6. table join question

7. ingres II locking problems

8. Storing web client IP addresses

9. VB/ODBC/Ingres Locking Problem

10. Strange locking problems with Ingres Star

11. Lock problem on Ingres, Please help.

12. INGRES locking problem?

13. Ingres compiled under SYSV; Problem with lock daemon