Results 1 to 5 of 5

Thread: Locks question

  1. #1
    Join Date
    May 2008
    Posts
    2

    Unanswered: Locks question

    Hello to everyone

    I'm completely new in Informix but had a lot of experience with DB2

    Here is my problem:

    Assume that my TABLE has an integer ZZZ column. Further assume that I explicitly create an index on ZZZ.

    Table was created lock mode ROW and ZZZ as primary key

    Now consider this "start of transaction":

    set isolation to repeatable read;
    begin;
    select * from TABLE where ZZZ=5;

    I expected that it should place a lock ONLY ON those records for which the value of ZZZ=5.

    In my case ZZZ is a primary key , so there is only one record where ZZZ=5

    Now ...
    running onstat -k, I see that the WHOLE TABLE IS LOCKED (rowid=0).

    Since part of my design relies on the expected behavior of repeatable read, what am I missing?


    Thanks in advance for any help !

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Not an Informix guy, but a search for informix and "row-level locking" suggests that you need to turn it on for each table that needs it. Source. Apparently you can do it at CREATE time or via ALTER TABLE.

    Caveat lector: that source isn't the Informix docs. I'd suggest you look there, maybe for the phrase "LOCK MODE".

  3. #3
    Join Date
    May 2008
    Posts
    2
    Quote Originally Posted by sco08y
    Not an Informix guy, but a search for informix and "row-level locking" suggests that you need to turn it on for each table that needs it. Source. Apparently you can do it at CREATE time or via ALTER TABLE.

    Caveat lector: that source isn't the Informix docs. I'd suggest you look there, maybe for the phrase "LOCK MODE".

    My table was created with row-level locking as I wrote in my first post
    Thanks !

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by yevgeni
    My table was created with row-level locking as I wrote in my first post
    Thanks !
    My bad, I completely missed that. Well, I'm stumped.

  5. #5
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    If the database was created as logging-type ANSI, every row processed will have a shared lock placed on it.

    I found that info at http://www.ibm.com/developerworks/db...ber/index.html

Posting Permissions

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