IDS Forum

Locking & Logging Issue

Posted By: MICHAEL HOFFMAN
Date: Thursday, 16 September 2010, at 11:38 a.m.

Hi All,
[Please let me know what other information I should provide]

Here's the full story on our problem. First off: versions!
Informix Dynamic Server Version 11.50.FC6 (development) and 9.40.FC4 (production)
Excalibur Datablade ETX.1.30.FC7
UNIX SunOS 5.9 Generic_122300-51

Issue Synopsis: We use the Excalibur Datablade to search legal documents, utilizing the etx_contains function. New documents are inserted on an as-needed basis (potentially several times a day). The users complained that 'large' documents were failing to load, since time of installation.
In the last month, however, they can now only insert the smallest of documents. Coincidentally, we observed a higher load of searches on our database. Also, the Logical Log usage has increased exponentially (from roughly 6 5MB logs filled per day to over 100!).

Document content is stored in an sbspace, connected to the Datablade.

No code changes have been made in over 9 months.

We noticed in our logs that the Inserts take roughly 600 - 700 msecs. The searches take upwards of 1600 msecs.

*** Problem1: Logical Logs are filling excessively fast, when only searches are being performed. Searches are NOT done inside of explicit transactions. However, onstat -x shows every select using the etx_contains function is creating a logged transaction.

*** Problem2: Sometimes, a Select using the etx_contains function begins and then an Insert is started on the same table, while the Select is in 'prepare' mode. When the Select begins to process, the insert statement fails!

onstat -k shows HDR+IS locks being placed on the Large Smart Object Header as soon as the Select kicks off:
10af47ee8 0 14f0ba9c8 10af19168 HDR+IS 40000b 0 0

Could this be blocking the Insert from exclusively locking the table? Why wouldn't one statement wait for the other to finish?
SET LOCK MODE TO WAIT 30 for the insert
SET LOCK MODE TO WAIT 10 for the select

Both processes are using Isolation Level Committed Read -- we cannot use Dirty Read for the select due to business requirements. But we tried Dirty Read anyway, and the HDR+IS locks were still placed.

There are no errors listed in the Informix message log (onstat -m).

===========================
Our error logs read:
------
[9/13/10 12:25:12:558 MDT] 00000044 SystemOut O [0000] anonymous DEBUG ContentDAO -
insert into ct(ct_id, sdoc_id, xml_1) values (?,?,?)
[9/13/10 12:25:12:801 MDT] 00000044 SystemOut O [0000] anonymous ERROR .CtDAO -
java.sql.SQLException: A select is in progress, so updating the index is not
allowed.
[9/13/10 12:25:12:807 MDT] 00000044 SystemOut O [0000] anonymous ERROR FileERDAO -
Exception in FileERDAO.insertNewCt:com.rustts.model.DAO.DAOException: Error in CtDAO.create(Connection, CtVO).
A select is in progress, so updating the index is not allowed.Error Code:-937
------

Most of the inserts process without incident, but the issue arises when a different thread passes a Select to the 'ct' table at the same time.
The Select statement takes about 400 msecs to Prepare, and as soon as it kicks off, the Insert is blocked and causes an error.

This is from a second log file, ***compare the timestamps***:
-------
[9/13/10 12:25:12:382 MDT] 0000003e SystemOut O [0000] anonymous INFO KeywordSearchDAO -
select p.ct_id, p.xml_1, s.sdoc_id,cc.ccrnum,cc.ccrn_vol
from ct p,sdoc s,ccd cc
where p.sdoc_id=s.sdoc_id and s.ccd_id=cc.ccd_id
and etx_contains(p.xml_1, Row(?, 'SEARCH_TYPE=PHRASE_EXACT & MAX_MATCHES=1000'), rc # etx_ReturnType)
order by cc.ccrn_vol, p.ct_id
[9/13/10 12:25:12:383 MDT] 0000003e SystemOut O [0000] anonymous INFO KeywordSearchDAO - Binding keyword "silence", 100
[9/13/10 12:25:12:800 MDT] 0000003e SystemOut O [0000] anonymous INFO KeywordSearchDAO - START ROW100
[9/13/10 12:25:12:801 MDT] 0000003e SystemOut O [0000] anonymous INFO KeywordSearchDAO - total time 428 msecs
--------

Thanks for any and all assistance!
Michael Hoffman

Messages In This Thread

• Locking & Logging Issue
  MICHAEL HOFFMAN -- Thursday, 16 September 2010, at 11:38 a.m.
  • Re: Locking & Logging Issue
    John Miller iii -- Thursday, 16 September 2010, at 12:49 p.m.
    • Re: Locking & Logging Issue
      MICHAEL HOFFMAN -- Thursday, 16 September 2010, at 3:37 p.m.
      • Res: Locking & Logging Issue
        Cesar Inacio Martins -- Friday, 17 September 2010, at 7:18 a.m.
        • Re: Res: Locking & Logging Issue
          MICHAEL HOFFMAN -- Friday, 17 September 2010, at 12:17 p.m.
  • Re: Locking & Logging Issue
    Alexandre Marini -- Thursday, 16 September 2010, at 12:49 p.m.
    • Re: Locking & Logging Issue
      Alexandre Marini -- Thursday, 16 September 2010, at 12:56 p.m.
      • Re: Locking & Logging Issue
        MICHAEL HOFFMAN -- Thursday, 16 September 2010, at 1:37 p.m.
    • Re: Locking & Logging Issue
      MICHAEL HOFFMAN -- Thursday, 16 September 2010, at 1:57 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.