Forum OpenACS Q&A: Possible deadlock in transaction

Posted by Adam Batkin on
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 "
    cal_insert_recurrences(:parent_id, :user_id,
to_date(:repeat_check), 0);

    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!
Posted by Sebastiano Pilla on
Can you post the log entries for the error?
Posted by Adam Batkin on
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.


Posted by Sebastiano Pilla on
Then I don't think that's a deadlock in Oracle: when Oracle detects a deadlock, it issues the error ORA-00060 deadlock detected while waiting for resource. Since you don't get such an error, there should be something else going on.

What happens if you run the problematic query in SQL*Plus?
Posted by Don Baccus on
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"
Posted by Rich Graves on
Adam, one way to see if and where Oracle is deadlocked is to open a second sqlplus session while the first is hung and see if you can select/update the relevant table(s) from there. If certain rows are locked you won't be able to manipulate those, etc.

...but I think Don's solution will work.