Results 1 to 4 of 4

Thread: in-memory Table

  1. #1
    Join Date
    May 2009

    Unanswered: in-memory Table

    I am trying to optimize a system which is handling considerable amount of traffic. Now I am trying to improve the through put still more. There was a row level lock for every request on a table which has 4million rows. I am trying to avoid this locking mechanism by inserting a unique value(one column) into a small table and deleting it once serviced.
    The problem is insert happens successfully, but some times delete fails with the following error "Could not lock a row for delete".
    Is there is any way i can put this table in DB server's memory so that no logging is done. The table has a unique index and a PK is also on the field.

    Any suggestions!

  2. #2
    Join Date
    Jul 2008
    what is the error nmber?
    did you try drop table after?

  3. #3
    Join Date
    May 2009
    Thanks for your reply Patrick

    These are the errors
    Could not position within a file via an index (SQL error: -245, ISAM code: -154).
    Could not delete a row (SQL error: -240, ISAM code: -154)
    Could not position within a table (psaadmin.online_trans) (SQL error: -243, ISAM code: -154)

    Since this table is the entry and exit point of any transaction I cannot put a sleep or increase the time-out waiting period.(Doing so will result in lower performance.)

    Is there is any other way to lock a transaction( i.e to sync)


  4. #4
    Join Date
    Apr 2007
    Jundiai / SP - Brasil
    This appear be a problem with SET ISOLATION and not lock release (COMMIT/ROLLBACK delay)...

    You need to debug a situation step-by-step and monitor how your locks are created and when they are released to make sure all is running ok.

    Need make sure you aren't put any SHARED lock in any row .

    If your table are small, remove the PK and create just a CONSTRAINT if need , probably the Informix not using the PK .

    To see if the table are in memory , use the command "onstat -P" .
    If you work with version 7.31 , you can force the table in memory with SET TABLE xyz MEMORY_RESIDENT , if the version is newer this statement do not have any effect, Informix are smartest enough to keep the table in memory.
    César Inacio Martins
    Jundiai / SP - Brasil - em Português - English (translated by Google).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts