Re: message.log: lock table overflow
Xiong wrote:
}
} In informix message.log file, I found the following messages :
}
} 20:21:42 lock table overflow - userid ..., sessionid ...
} ... ( Similar lock table overflow messages omitted )
} 20:28:40 lock table overflow - userid ..., sessionid ...
}
} By running 'onstat -g sql', I found 4 sessions with SQL ERR and
} ISAM ERR as follows
}
} SQL ISAM SQL
} ERR ERR stmt
} -240 -134 DELETE
} -211 -134 ...
} -211 -134 CREATE INDEX
} -243 -134 UPDATE
}
} -134 ISAM error: no more locks.
} -211 Cannot read system catalog catalog-table.
} -240 Could not delete a row.
} -243 Could not position within a table table-name.
}
} LOCKS of $INFORMIXDIR/etc/$ONCONFIG is set to 350000
}
} Does the 'lock table overflow' message mean the server need more LOCKS?
} Can I solve the problem by increasing LOCKS value ?
} Any response to solve the problem is appreciated !
}
} --
} Fu-Xiong Lin
Yes you can, but each lock does require more memory, so you need to be
careful you don't just keep increasing the number until you have memory
shortage problems.
The question I would ask is:
Is it acceptable to have 350,000 concurrent locks ?
It may be depending on your number of users, work type, etc, but you
may be able to reduce the requirement for locks by either decreasing
lock granularity by changing lock mode on tables from row to page, or
by locking tables within transactions, such as lock table xxx in
exclusive mode.
Both of these option have consequences however, and you must ensure you
are aware of them if you need or want to go down this route.
--
Rick Ward
Senior DBA
Great Universal Stores Home Shopping
MANCHESTER, UK
All views and comments in this e-mail are expressed by myself only, and
are not necessarily representative of my employer, Great Universal
Stores.