Forum OpenACS Q&A: Possible deadlock in transaction

We are running ACS 3.4.10 on Oracle. The following code is deadlocking, and I think Oracle should be smarter than that (i.e. not deadlock at all, since I am not doing anything that should cause this...or at the least, detect the deadlock and fail/error). It is all within a transaction.

    set club_ids [list]
    db_foreach get_item_clubs "select club_id from cal_item_club_map
where item_id = :item_id" { if { $club_id != "" } { lappend club_ids
$club_id } }

    db_dml cal_insert_recurrences "
    BEGIN
    cal_insert_recurrences(:parent_id, :user_id,
to_date(:repeat_check), 0);
    END;
    "

    set old_id $item_id
    db_foreach get_cal_items_for_maps "select item_id from cal_items
where parent_id = :parent_id and item_id <> :old_id" {
        ns_log Error "Starting Loop"
        foreach club $club_ids { db_dml cal_club_map_inserts "insert
into cal_item_club_map (item_id, club_id) values (:item_id, :club)" }
        ns_log Error "Clubs"
    }

It "stops" inside the db_dml cal_club_map_inserts...
If I break the last db_foreach loop into its own transaction, it all works fine. Also, if the list $club_ids is empty, the outder db_foreach will happily loop once for each item_id found, doing nothing inside (exactly what it should do). So the problem is definitely with the inner foreach insert.
Hope someone can help!

See, that's the thing, it doesn't actually detect or do anything about the deadlock, something which I thought Oracle was capable of (maybe it isn't a deadlock? why else would it just "hang" there?)

So the last things in the log are an error saying "Starting Loop" and the database informing me that the cal_club_map_inserts is being run and what the two bind variables are. Then it just stops. No one else can connect to the server until it is restarted.

Thanks.

It looks like a lock's been taken (not explicitly, though, not by cal_insert_recurrences) but why not rewrite the insert loop as:

foreach club $club_ids {
    db_dml cal_club_map_inserts "
        insert into cal_item_club_map (item_id, club_id)
        select item_id, :club
        from cal_items
        where parent_id = :parent_id
          and item_id <> :item_id"
}