I am new too ingres (so please be gentle), there is a report we run every month or so which adds up a bunch of numbers in the database and converts them too minutes. When i try to run the report this month it hangs... The following is what iimonitor tells me for this query:
Session 31AEC700 (dba ) cs_state: CS_COMPUTABLE cs_mask:
DB Name: dbname (Owned by: dba )
User: dba (dba )
User Name at Session Startup: dba
Application Code: 00000000 Current Facility: QEF (00000006)
Client user: dba
Client host: machinename
Client tty: 18
Client pid: 24874
Client connection target: dbname
Client information: user='dba',host='machinename',tty='18',pid=24874,c onn='dbname'
Query: select * from tablename where columnname = number
And in ipm it seems to have a lock on the table...
ID RQ State Lock Type DB Table
53 S GR PHY Control dbname, TABLE: tablename
133 S GR PHY Control column1
c9 S GR PHY Control column2
50 X GR PHY Table ??=-65381,0
Does anyone know what this means exactly, i can gather it has a lock, but im unsure as to what caused it and how to prevent it.
What you have there are control locks. These are not going to block other sessions from reading the table, or even updating it. They will block another session from making changes to the structure of the table - modify, alter table, drop. These control locks are usually only held while a query is executing - not for the duration of a transaction. You can't tell ingres not to take out control locks.
The other lock you have there is an exclusive lock on a temporary table - you can tell it's temporary because the reltid (-65381) is negative. Since it's a temporary table it's either the result of a join or a global temporary table - either way it's private to your session and so that lock won't affect other sessions either.