IDS Forum

Re: Locking & Logging Issue

Posted By: MICHAEL HOFFMAN
Date: Thursday, 16 September 2010, at 3:37 p.m.

In Response To: Re: Locking & Logging Issue (John Miller iii)

John,
Thanks. We think we nailed the logging issue now, but don't know *why* it was happening.

Our searches utilize the Excalibur Data Blade functions: etx_contains and etx_GetHilite.

Removing etx_GetHilite has stopped the searches from running inside transactions, and those stopped the excessive logging!

(We still have the Locking issue when using etx_contains)

Any familiarity with the Excalibur Data Blade? Any idea why the etx_GetHilite function would cause a transaction to be run?

Thanks,
Michael Hoffman

On Thu, Sep 16, 2010 at 10:49 AM, John Miller iii <miller3@us.ibm.com> wrote:

To see what is filling the logs, I would suggest looking at onlog.

If you can reproduce this with a single user it is usually easy to

move to a new log (onmode -l), run the test, (onmode -l), then

run onlog -n on the previous log id.

Maybe you are creating temp tables and these temp tables are

no being placed in a tempdbs, Just a thought.

John F. Miller III

STSM, Embedability Architect

miller3@us.ibm.com

503-578-5645

IBM Informix Dynamic Server (IDS)

ids-bounces@iiug.org wrote on 09/16/2010 08:38:57 AM:

> From:

>

> "MICHAEL HOFFMAN" <mrh@panix.com>

>

> To:

>

> ids@iiug.org

>

> Date:

>

> 09/16/2010 08:39 AM

>

> Subject:

>

> Locking & Logging Issue [21317]

>

> Sent by:

>

> ids-bounces@iiug.org

>

> 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 cannotuse

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.