Forum OpenACS Q&A: Oracle hangs with db_transaction

Collapse
1: Oracle hangs with db_transaction
Posted by Kolja Lehmann on 06/17/03 01:08 PM
I have got a block of code which runs normally without, but hangs with db_transaction and I cannot imagine why. I try to get values from one table and stuff them into another table. So this is the code (sql code substituted from .xql file):
   db_transaction {
        db_exec_plsql create_question {
        declare
        v integer;
            begin
                v := survey_question.new (
                    question_id => :question_id,
                    section_id => :section_id,
                    sort_order => :sort_order,
                    question_text => empty_clob(),
                    abstract_data_type => :abstract_data_type,
                    presentation_type => :presentation_type,
                    presentation_alignment => :presentation_alignment,
                    presentation_options => :presentation_options,
                    active_p => :active_p,
                    required_p => :required_p,
                    context_id => :section_id,
                    creation_user => :user_id,
                    question_html_p => :question_html_p,
                    summary_type => :summary_type,
                    answer_description => :answer_description,
                    predefined_question_id => :predefined_question_id
                );
            end;

}

        db_dml add_question_text {
            update survey_questions
            set question_text = :question_text
            where question_id = :question_id}


        # For questions where the user is selecting a canned response, insert
        # the canned responses into survey_question_choices by using the predefined
        # choices

        if { $presentation_type == "checkbox" || $presentation_type == "radio" || $presentation_type == "select" } {
            if { $abstract_data_type == "choice" } {
                ns_log notice "test0"
                db_foreach get_choices "      select choice_id,label,numeric_value,sort_order,presentation_alignment,more_info_type from survey_predef_question_choices where question_id=:predefined_question_id" {
                    ns_log notice "test1"
                    set new_choice_id [db_nextval survey_choice_id_sequence]
                    db_dml add_new_choice "
      insert into
      survey_question_choices(choice_id,predef_choice_id,question_id,label,numeric_value,sort_order,presentation_alignment,more_info_type)
      values (:new_choice_id,:choice_id,:question_id,:label,:numeric_value,:sort_order,:presentation_alignment,:more_info_type)"
                    ns_log notice "test2"
                }
            }
        }
} on_error {
ns_log notice "testtest : $errmsg"
            db_release_unused_handles
            ad_return_error "Database Error" "
$errmsg
"
            ad_script_abort

}
This one should create a question from a predefined question. This works fine for normal questions but not for multiple choice questions, which use another table (question_choices). In that case the db creates the question and the question text, selects the predefined choices and hangs after the first execution of add_new_choice, see following snippet from error log:
            update survey_questions
            set question_text = :question_text
            where question_id = :question_id


[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: bind variable 'question_text' = 'Which Continent are you from?'
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: bind variable 'question_id' = '19005'
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: test0
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: SQL():
      select choice_id,label,numeric_value,sort_order,presentation_alignment,more_info_type from survey_predef_question_choi\
ces where question_id=:predefined_question_id

[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: bind variable 'predefined_question_id' = '18070'
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: test1
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: SQL():  select survey_choice_id_sequence.nextval from dual
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: SQL():
      insert into
      survey_question_choices(choice_id,predef_choice_id,question_id,label,numeric_value,sort_order,presentation_alignment,m\
ore_info_type)
      values (:new_choice_id,:choice_id,:question_id,:label,:numeric_value,:sort_order,:presentation_alignment,:more_info_ty\
pe)

[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: bind variable 'new_choice_id' = '67'
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: bind variable 'choice_id' = '33'
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: bind variable 'question_id' = '19005'
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: bind variable 'label' = 'Europe'
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: bind variable 'numeric_value' = ''
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: bind variable 'sort_order' = '0'
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: bind variable 'presentation_alignment' = 'right'
[17/Jun/2003:12:50:38][7811.688153][-conn4-] Notice: bind variable 'more_info_type' = ''
After executing this, the next time it hangs even when creating the question. Notice that "test2" is not written to the log. After this, the whole server blocks and needs to be restarted.
Without the transaction it works, and that is really strange I think. Anyone any ideas? Thanks in advance
Collapse
2: Re: Oracle hangs with db_transaction (response to 1)
Posted by Sebastiano Pilla on 06/18/03 10:10 AM
Perhaps I'm on the wrong track, but... You seem to be updating CLOBs, so try doing something like: 
select question_id from survey_questions
where question_id = :question_id
for update of question_text
Note that question_text above is not a bind variable, you're just telling Oracle that you're about to update a CLOB column. You then proceed with your update: 
update survey_questions
            set question_text = :question_text
            where question_id = :question_id
I'd bet that your unmodified code works like a charm with varchar2(4000), but LOBs are different... Let us know.
Collapse
3: Re: Oracle hangs with db_transaction (response to 1)
Posted by Kolja Lehmann on 06/20/03 03:23 PM
I've tried it, but updating the CLOB was not the statement that was causing the trouble. Again it hung after inserting the first row (of five) into survey_question_choices.
Collapse
4: Re: Oracle hangs with db_transaction (response to 1)
Posted by Jesse Kipp on 06/20/03 08:17 PM
I suspect the problem is that in what you have written, the insert into the question_choices table implicitly opens a second database handle, but because the first database handle has a lock on the row it just created, the second database handle can't verify the referential integrity constraint on question_id. Try writing the insert into survey_question_choices as a single sql statement using "insert into... select."

If that doesn't help: if you start the page running, and log in to sqlplus on the server as the dba, you can look at the dynamic performance views to see what statements are holding locks down. For example, the following will give you a list of locked objects, the usernames that have them locked, and the active sql statement (if any) for that session. It is a place to start, keeping in mind that a table name listed does not mean that the entire table is locked, only certain rows. 

select
  a.object_name,
  s.username,
  s.sid,
  q.sql_text
from
  all_objects a,
  v$locked_object lo,
  v$session s,
  v$sql q
where
  lo.object_id = a.object_id
and
  lo.session_id = s.sid
and
  s.sql_address = q.address (+)
and
  s.sql_hash_value = q.hash_value (+)
Collapse
5: Re: Oracle hangs with db_transaction (response to 1)
Posted by Kolja Lehmann on 06/25/03 03:58 PM
Thanks a lot! Creating the rows in one statement did the trick. I used insert...select and voilà! It works!