Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Unanswered: concurency in oracle

    hello Oracle developers...
    plz..help me out in solving this problem..
    when the procedure is called with some parameters..inside the procedure
    1)a query extracts some data using given parameters.
    2) and this data is inserted into a table say TEMP.
    3)another query is executed with same parameters as in step 1.
    4)and data is inserted into table TEMP.
    commit and end of procedure.
    5) Existing data in TEMP table is deleted .. every time this procedure is called..

    and the TEMP table is querid to generate a report using a program in cfml (coldfusion markup language)
    this works fine for a single user..but when multiple users
    are calling the procedure ..
    it gives me improper data..may be becoz.. of asyncronous nature..
    plz.. help me..
    thanks in advance..

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Is this a place for global temporay tables where the data remains for a session

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: concurency in oracle

    You posted this same question earlier:

    http://www.dbforums.com/t982250.html

  4. #4
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Re: concurency in oracle

    Originally posted by andrewst
    You posted this same question earlier:

    http://www.dbforums.com/t982250.html
    yes..andrews..but the thing is it is still unsolved ..that is why..I was trying to be more clear..
    u mean to say that I have to use Global Temporary Table..
    please help me out..

  5. #5
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Hi Shridhar,

    (1) As you said, You have a procedure that perform the INSERT on the table and this procedure is used by many users. If this is the case, then there is the transaction queue in the data dictionay cache that take care of the concurrency eventhough the same procedure is executed at the same time because, in the queue, two processes cannot be kept at the same place at the same time.

    (2) You said that this procedure first deletes the data from the table. So there is a RX (Row exclusive) lock on the table, meaning shared lock on the table and exclusive lock on the rows which are deleted (here all the rows). So if another user try to delete the same rows at the same time, it is not possible (in case of the scenerio you have mentioned)

    The reason you are getting duplicate values may be because of the absence of primary key (Or unique constraint).
    another workout is that , you can exclusively lock the table by providing
    LOCK TABLE <name> IN EXCLUSIVE MODE; in the begginging of the procedure.

    HTH
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  6. #6
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    thanks

    thanks u verymuch..hings..

Posting Permissions

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