Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    3

    Unanswered: Exclusive Lock on sysdefaults table

    Hi all!!

    I'm having problems with executing dbload command while I'm creating indexes at a same time. The CREATE INDEX sentence generates an exclusive lock on table sysdefaults, and, the dbload command dies waiting for this lock to be releases.

    I have to execute both sentences simultaneously.

    ¿Any idea? ¿Does dbload have any option to define the isolation level?

    Thank you in advance.
    Luis.

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Your problem is with CREATE INDEX. The create index claim for a exclusive table lock.

    If your version is 10 or greater you can use the option ONLINE with the CREATE INDEX statment...

    I suppose by using "dbload" , you maybe are loading a large amount of data, this is right?
    If yes, the best thing you do is execute the dbload first, when they finish, start the create index.

    If you are loading a large amount of data and use create index with online option, the performance will not be good and a lot of resource will be taken...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Oct 2007
    Posts
    3
    Thank you for your answer César.

    In fact, we're using IDS 9.40, so I haven't ONLINE option (...yet )

    About creating the index before, my problem is not creating index and loading data in the same table. The situation is the following:
    I have "A" and "B" tables. I'm creating an index in "A" table; meanwhile I'm loading data with dbload into "B" table. And then, "B" table loading process fails as it finds a lock in ¿¿¿sysdefaults table??? First of all, I don't understand why "CREATE INDEX" sentence place some exclusive locks on sysdefaults table. Actually, trying to do more tests, I've got the same situation with two "CREATE INDEX" sentences: one finishes successfully, but the other fails with the sysdefaults lock error.
    I've tried to repeat the situation creating indexes and monitoring locks, but I've not been able to know in what cases "CREATE INDEX" command is locking sysdefaults table (sometimes it does, sometimes id doesn't...)

    I would be very grateful if someone could help me with this matter.
    Thank you in advance.
    Luis.

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Hum... this is weird...

    You are sure don't have anothers users trying access table "B" ?

    You already tried the option "-r" on dbload?

    If possible copy here the error showed in dbload and the locks (onstat -k)
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - 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
  •