Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Unanswered: table being locked instead of row

    I am running informix ver 7.

    i have two tables one of which is being locked only for that record,

    whereas another table is being locked to all records, when updating just one record

    can anyone offer some suggestions

  2. #2
    Join Date
    Oct 2002
    Posts
    52
    To change lock mode:
    alter table XXX lock mode (page|row);

    For row lock mode you need to define more locks in the onconfig - then you have less chances for waitings. The management of the locks costs some. If you have many users to the same table on the same time - think about row.

  3. #3
    Join Date
    Jan 2003
    Posts
    2

    locked table instead of row solved

    I have since found the solution since I posted this question.

    If you create a unique index on the table
    you will overcome the error
    eg
    create unique index ixtabname on tabname (field)

    Regards
    Mark

  4. #4
    Join Date
    Oct 2002
    Posts
    52
    Can you explain why it works? doesn't make sence to me.

  5. #5
    Join Date
    Mar 2002
    Posts
    112

    re

    Hahaha ...
    Create index always table lock like Alter table
    if you want to read you can 'set isolation to diry read ' for the reading session

  6. #6
    Join Date
    Apr 2004
    Posts
    10

    can i do a question

    can i do a question in this place of de dbforum? if don't , where can i do it?

  7. #7
    Join Date
    Apr 2004
    Posts
    10

    error 143

    can anybody give some solution on the following:

    how can i avoid within the application (4gl) the programm abort when it gives the error -244 associated to ISAM error -143.

    i have the table locked to row level

    i have a unique index on the table

    i have made a controll within the sqlca.sqlcode be different of zero

    but the programm abort even with these controlls.

  8. #8
    Join Date
    Jan 2004
    Posts
    71

    how to determine lock level?-

    thedafna,
    Is there a way to determine how lock level was set at table creation?

  9. #9
    Join Date
    Aug 2003
    Location
    India
    Posts
    262

    Re: how to determine lock level?-

    Originally posted by WiccaChic
    thedafna,
    Is there a way to determine how lock level was set at table creation?
    Hi,

    To determine the lock level, enter the foll. command at unix prompt

    dbschema -d <databasename> -t <tablename> -ss
    When you create a table, you can also specify the lock mode


    create table test
    (
    col1 char(1)
    )
    lock mode row


    Regards,

    Lloyd

  10. #10
    Join Date
    Nov 2003
    Location
    Mumbai, India
    Posts
    92

    Re: how to determine lock level?-

    Originally posted by WiccaChic
    thedafna,
    Is there a way to determine how lock level was set at table creation?
    Hi,

    One of the easiest way to get the infomation is the execute the following SQL via dbaccess or so:

    select tabname,locklevel from systables where tabid>99 ;

    Regards,
    Shriyan

  11. #11
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    Some extra info, on a lot of realted questions:

    - Default lock mode can be changed in the newer engines
    (IDS 9.3 and 9.4) with the following parameter:
    DEF_TABLE_LOCKMODE
    This parameter can be set to page or row (where page is the default)

    - You can avoid a 4GL prgram to crash at an error with the statement:
    WHENEVER ERROR CONTINUE

    - Why it works with an index and not without is also quite logical.
    Without an index IDS has to search/investigate all rows for satisfying
    conditions. Table is read sequentially, everything is read. By default (in
    a logged database) the isolation level is commited read. So every
    record that is in an open transaction may not be read. So Informix will
    say: Record locked.
    With an index you scan the tree (which is not locked) and only some
    rows, so lock contention is less likely. So this works.

    - When raising such an issue always take a look at:
    + Lock level (page/row)
    + ISOLATION LEVEL
    + LOCK MODE
    + Error capturing in your application
    These four things must be set correctly to get the desired goal.

    I hope this extra info was meaningfull.

    Rob Prop

Posting Permissions

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