Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    7

    Unanswered: store procedure ==> race condition?

    Hi

    I have encountered a problem and it's assumed that there is a race condition happened.

    Someone proposed, while my method is running, someone else executed some query and altered the table my stored procedure is supposed to modifiy and causes some trouble.

    My questions: Is it even possible that the stored procedure be interrupt in the middle of an execution? If so, how would I solve this problem?

    thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    In My Opinion, the problem statement is ambiguous & the answer is "it depends".

    >someone else executed some query and altered the table
    "altered" as in DDL
    or DML
    > my stored procedure is supposed to modifiy
    DDL or DML?
    >and causes some trouble.
    What specific Oracle error code & messages are you seeing?

    Oracle ALWAYS presents a read consistent view of the data as it exists at the start of a transaction.
    If/when it can not maintain the read consistent view, it throws ORA-01555
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2007
    Posts
    7
    Thanks for the reply

    I should have been clearer

    My code goes something like this

    Code:
    proc
    
    look up student id, sid
    look up teacher id, tid
    
    perform some logic, and for each applicable pairs, do
        insert into student_teacher (studentId, teacherId) values (sid, tid)
    
    end proc

    but, say someone else can also insert into student_teacher table through, UI for example. There is a chance they can insert a (sid, tid) pair my procedure will eventually get to, but not yet gone over. The key constraint will be violated when my table get to this (sid, tid) but I dont want that to happen

    I want to do a select statement to check if this (sid, tid) pair exists right before inserting, but this doesnt really solve the race condition isn't it.

    People can still insert the data right between my checking and inserting


    How can I solve this?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can I solve this?
    What should happen if the record already exists?
    Last edited by anacedent; 10-29-07 at 13:52.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2007
    Posts
    7
    Thanks, but I am not sure if you understand my question

    I have read the Read Consistency part of the article

    I am not sure I completed understand the reason why there is no problem to solve.

    First of all, I am not sure how "statement-level read consistency". Is the data consistent during the whole procedure? Is the whole procedure a statement? Or any query in the stored procedure count as one statement?

    If the later is true I have a problem:

    I do not want the statement "update student_teacher ..." to cause any exception, but if I do

    if (select count (*) from student_teacher ... <= 0 ) then
    insert into student_teacher ...

    It wouldn't work perfectly because they are two statements and (will not?) guarantee consistency
    Last edited by xusword; 10-29-07 at 14:51.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    In a well designed application, you'd never have to worry about any race condition.
    In a suboptimal application, you'll have to use (extra) PL/SQL code to "properly" handle the race condition.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    If it is just to avoid a unique key violation, then you may do this :
    Code:
    DECLARE
    
        Unique_Violation EXCEPTION;
        PRAGMA EXCEPTION_INIT(Unique_Violation, -1);
    
    BEGIN
    
        BEGIN
            INSERT INTO student_teacher (studentId, teacherId) VALUES (sid, tid);
        EXCEPTION
            WHEN Unique_Violation THEN
                -- Process when unique key is violated
                -- NULL if you want to do nothing
                NULL;
        END;
    
    END;
    Else, you can do a SELECT FOR UPDATE on the rows you want untouched by others during your pre-processing, this lock being released when you COMMIT or ROLLBACK your transaction.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  8. #8
    Join Date
    Oct 2007
    Posts
    7
    Quote Originally Posted by RBARAER
    If it is just to avoid a unique key violation, then you may do this :
    Code:
    DECLARE
    
        Unique_Violation EXCEPTION;
        PRAGMA EXCEPTION_INIT(Unique_Violation, -1);
    
    BEGIN
    
        BEGIN
            INSERT INTO student_teacher (studentId, teacherId) VALUES (sid, tid);
        EXCEPTION
            WHEN Unique_Violation THEN
                -- Process when unique key is violated
                -- NULL if you want to do nothing
                NULL;
        END;
    
    END;
    Else, you can do a SELECT FOR UPDATE on the rows you want untouched by others during your pre-processing, this lock being released when you COMMIT or ROLLBACK your transaction.

    Regards,

    rbaraer
    Thanks
    this is what I need

    It is frustrating to maintain an application when I am not the one that build it... plus, I don't know enough of pl/sql

Posting Permissions

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