Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Posts
    8

    Unanswered: DB locking and data corruption problem

    Hello,

    I have a sql script where triggers, procedures and functions are written. The triggers are causing db outages and causing problems in the application as well.

    Please guide me to troubleshoot this critical problem as this issue is causing the whole site to go down. I am trying my best but with my low experience and expertise, am not able to make good progress.

    Scenario: Course enrollments are inserted, deleted and updated in course_main and course_users table. This is done in gui as well as in background snapshot scheduler in a cron process. Course_main table contains all course enrollments and course_users table has crsmain_pk1 as foreign key.

    I am posting the problem script file, please help me. Its quite a big file and am not sure what should i paste here so am uploading the file in txt. Request a helping hand.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    That is far too few information to be able to help you

    What exactly is "db outages", and which "problems" are caused in the application?
    And what "data corruption" problem do you have?

    How To Ask Questions The Smart Way
    The Tom Kyte Blog: How to ask questions

  3. #3
    Join Date
    Oct 2010
    Posts
    8
    Sorry if i provided far too few information. Db outages means database locks and the database going down totally. For some people who installed this program containing this sql script, it caused their site menu to be broken and made their site sluggish also. Sometimes, when they try to remove this program from their GUI, the database has again crashed.

    The tool is about extracting all enrollments from parent courses into a target course. These courses have their row_status and availability status. Users enrolled in the courses can be made disabled or removed or inserted or updated through the GUI as well as with a cron that runs frequently.

    Please let me know if i need to explain any part that is not clear.

    Thanks a lot for taking out your valuable time for trying to help.

  4. #4
    Join Date
    Oct 2010
    Posts
    8
    Code:
    --                                 TABLES                                    --
    --------------------------------------------------------------------------
    -- TABLE [bbgs_cc_course_users_cache]
    --
    --   This table contains a copy of the newly inserted or update record to the
    --   course_users table.  This record is used in the after insert-update --statement level trigger to update the target course enrollments appropriately.
    --
    DECLARE objectCount int;
    BEGIN
      SELECT count(*) INTO objectCount FROM user_objects WHERE LOWER(object_type) = 'table' AND LOWER(object_name) = 'bbgs_cc_course_users_cache';
      IF (objectCount > 0 ) THEN
        dbms_output.put_line('CACHE TABLE [bbgs_cc_course_users_cache] EXISTS, DROPPING.');
        EXECUTE IMMEDIATE('DROP TABLE bbgs_cc_course_users_cache');
      END IF;
    
      EXECUTE IMMEDIATE('CREATE TABLE bbgs_cc_course_users_cache AS (select pk1, crsmain_pk1, users_pk1, row_status, available_ind, role, data_src_pk1, cartridge_ind, crsmain_sos_id_pk2, users_sos_id_pk2, sos_id_pk2 FROM course_users WHERE rownum < 1 )');
      dbms_output.put_line('TABLE [bbgs_cc_course_users_cache] CREATED');
    END;
    /
    --
    -- TABLE [bbgs_cc_course_users_cache_rm]
    --
    --   This table contains a copy of the deleted course_users.  This table is
    --   populated via the bbgs_cc_course_users_bd_rows trigger, then immediately
    --   read, processed, and cleaned by the bbgs_cc_course_users_ad_stmt trigger.
    --
    DECLARE objectCount int;
    BEGIN
      SELECT count(*) INTO objectCount FROM user_objects WHERE LOWER(object_type) = 'table' AND LOWER(object_name) = 'bbgs_cc_course_users_cache_rm';
      IF (objectCount > 0 ) THEN
        dbms_output.put_line('CACHE TABLE [bbgs_cc_course_users_cache_rm] EXISTS, DROPPING.');
        EXECUTE IMMEDIATE('DROP TABLE bbgs_cc_course_users_cache_rm');
      END IF;
    
      EXECUTE IMMEDIATE('CREATE TABLE bbgs_cc_course_users_cache_rm AS (SELECT pk1, crsmain_pk1, users_pk1 FROM course_users WHERE rownum < 1)');
      dbms_output.put_line('TABLE [bbgs_cc_course_users_cache_rm] CREATED');
    END;
    /
    --
    -- TABLE [bbgs_cc_course_main_cache_rm]
    --
    --   This table contains a copy of the deleted courses.  This table is
    --   populated via the bbgs_cc_course_main_bd_rows trigger, then immediately
    --   read, processed, and cleaned by the bbgs_cc_course_main_ad_stmt trigger.
    --
    DECLARE objectCount int;
    BEGIN
      SELECT count(*) INTO objectCount FROM user_objects WHERE LOWER(object_type) = 'table' AND LOWER(object_name) = 'bbgs_cc_course_main_cache_rm';
      IF (objectCount > 0 ) THEN
        dbms_output.put_line('CACHE TABLE [bbgs_cc_course_main_cache_rm] EXISTS, DROPPING.');
        EXECUTE IMMEDIATE('DROP TABLE bbgs_cc_course_main_cache_rm');
      END IF;
    
      EXECUTE IMMEDIATE('CREATE TABLE bbgs_cc_course_main_cache_rm AS (SELECT pk1 FROM course_main WHERE rownum < 1)');
      dbms_output.put_line('TABLE [bbgs_cc_course_main_cache_rm] CREATED');
    END;
    /
    --------------------------------------------------------------------------
    --                                 FUNCTIONS                                 --
    --------------------------------------------------------------------------
    
    -- FUNCTION [bbgs_cc_getStmtStatus]
    --
    --   returns: The value of the provided statement status, or the default value
    --            if the registry entry was not defined.
    --
    CREATE OR REPLACE FUNCTION bbgs_cc_getStmtStatus(
       registryKey   bbgs_cc_stmt_status.registry_key%TYPE
      ,defaultValue  bbgs_cc_stmt_status.registry_value%TYPE
    )
    RETURN bbgs_cc_stmt_status.registry_value%TYPE IS
    BEGIN
      -- check the registry for the provide course
      FOR v_reg IN ( SELECT * FROM bbgs_cc_stmt_status WHERE registry_key = registryKey ) LOOP
        IF( v_reg.registry_value IS NOT NULL AND length(trim(v_reg.registry_value)) > 0 ) THEN
          RETURN trim(v_reg.registry_value);
        END IF;
      END LOOP;
      -- return the default value
      RETURN defaultValue;
    END;
    /
    --
    -- FUNCTION [bbgs_cc_getStmtStatusAsInt]
    --
    --   returns: The value of the provided statement status as an INT, or a default
    --            value of 0 if the registry entry was not defined.
    --
    CREATE OR REPLACE FUNCTION bbgs_cc_getStmtStatusAsInt(
       registryKey system_registry.registry_key%TYPE
    )
    RETURN INTEGER IS
    BEGIN
      RETURN to_number( bbgs_cc_getStmtStatus(registryKey, '0') );
    END;
    /
    -------------------------------------------------------------------------------
    --                                PROCEDURES                                 --
    -------------------------------------------------------------------------------
    --
    -- PROCEDURE [bbgs_cc_setStmtStatus]
    --
    CREATE OR REPLACE PROCEDURE bbgs_cc_setStmtStatus(
       registryKey   system_registry.registry_key%TYPE
      ,registryVal   system_registry.registry_value%TYPE
      ,registryDesc  system_registry.description%TYPE
      ,overwrite     boolean
    ) AS
      record_count NUMBER;
    BEGIN
      SELECT count(*) INTO record_count FROM bbgs_cc_stmt_status WHERE registry_key = registryKey;
    
      IF (record_count > 0 ) THEN
        IF( overwrite ) THEN
          UPDATE bbgs_cc_stmt_status SET
            registry_value = registryVal,
            description    = registryDesc,
            dtmodified     = sysdate
          WHERE registry_key = registryKey;
        END IF;
      ELSE
        INSERT INTO bbgs_cc_stmt_status
          ( registry_key,  registry_value,  description  ) VALUES
          (  registryKey,     registryVal, registryDesc  );
      END IF;
      bbgs_cc_log(500, 'bbgs_cc_setStmtStatus', 'Saved registry key [' || registryKey || '] as status [' || registryVal || '].');
    END;
    /

  5. #5
    Join Date
    Oct 2010
    Posts
    8
    Code:
    --                                 TRIGGERS                                  --
    -------------------------------------------------------------------------------
    --
    -- TRIGGER [bbgs_cc_course_main_bu_rows]
    --
    --   This trigger performs operations on courses based on the "type" (source,
    --   target, none).
    --     - For course "association option", this trigger modifies the row_status
    --       and availability of the course, based on the configuration.
    --
    CREATE OR REPLACE TRIGGER bbgs_cc_course_main_bu_rows BEFORE UPDATE ON course_main FOR EACH ROW
    DECLARE
      v_source_avail_ind   course_users.available_ind%TYPE := null;
      v_source_row_status  course_users.row_status%TYPE    := null;
    BEGIN
      bbgs_cc_log(400, 'bbgs_cc_course_main_bu_rows', 'Invoked for Course['||:new.pk1||'].');
    
      -- Course Association Options: If the course is a source course
      IF( :new.pk1 > 0 AND bbgs_cc_isSourceCourse(:new.pk1) ) THEN
        bbgs_cc_log(400, 'bbgs_cc_course_main_bu_rows', 'Course ['||:new.pk1||'] is a source course.');
        -- Course Association: Availability
        IF( 'Y' = bbgs_cc_getCourseSetting('bbgs.cmt.cao.source-course.available_ind.persist', :new.pk1, bbgs_cc_getSysCrsPk1InSysReg(), 'N') ) THEN
          -- load the persistent value, with a default value of "empty"
          v_source_avail_ind:= bbgs_cc_getCourseSetting('bbgs.cmt.cao.source-course.available_ind', :new.pk1, bbgs_cc_getSysCrsPk1InSysReg(), '');
          IF ( v_source_avail_ind = 'Y' OR v_source_avail_ind = 'N' ) THEN
            IF( :new.available_ind <> v_source_avail_ind ) THEN
              -- set the row to the persistent value
              :new.available_ind:= v_source_avail_ind;
            END IF; -- the "row" is not set to the persistent value
          END IF; -- if the persistent value in the registry is valid
        END IF; -- the availability of a course is set to persistent
    
        -- Course Association: Row Status
        IF( 'Y' = bbgs_cc_getCourseSetting('bbgs.cmt.cao.source-course.row_status.persist', :new.pk1, bbgs_cc_getSysCrsPk1InSysReg(), 'N') ) THEN
          -- load the persistent value, with a default value of "empty"
          v_source_row_status:= bbgs_cc_getCourseSetting('bbgs.cmt.cao.source-course.row_status', :new.pk1, bbgs_cc_getSysCrsPk1InSysReg(), '');
          IF ( v_source_row_status = '0' OR v_source_row_status = '2' ) THEN
            IF( :new.row_status <> to_number(v_source_row_status) ) THEN
              -- set the row to the persistent value
              :new.row_status:= to_number(v_source_row_status);
            END IF; -- the "row" is not set to the persistent value
          END IF; -- if the persistent value in the registry is valid
        END IF; -- the row_status of a course is set to persistent
    
      END IF; -- the course is a source course
    END;
    /
    --
    -- TRIGGER [bbgs_cc_course_main_bd_rows]
    --
    --   This trigger performs operations on courses based on the "type" (source,
    --   target, none).  If the course to be deleted is a source or target course,
    --   this trigger disassociates the course from all associated courses IF the
    --   CMT configuration is set to do so.
    --
    CREATE OR REPLACE TRIGGER bbgs_cc_course_main_bd_rows BEFORE DELETE ON course_main FOR EACH ROW
    BEGIN
      bbgs_cc_log(400, 'bbgs_cc_course_main_bd_rows', 'Invoked for Course['||:old.pk1||']');
      INSERT INTO bbgs_cc_course_main_cache_rm(pk1) VALUES (:old.pk1);
    END;
    /
    CREATE OR REPLACE TRIGGER bbgs_cc_course_main_ad_stmt AFTER DELETE ON course_main
    BEGIN
      bbgs_cc_log(400, 'bbgs_cc_course_main_ad_stmt', 'Invoked.');
      FOR v_course IN ( SELECT * FROM bbgs_cc_course_main_cache_rm ) LOOP
       DELETE FROM bbgs_cc_course_main_cache_rm WHERE pk1 = v_course.pk1;
      -- If the course to be deleted is a source course, then the source course must
      -- be disassociated from all target courses if the CMT is configured to do so.
      IF( v_course.pk1 > 0 AND bbgs_cc_isSourceCourse(v_course.pk1) ) THEN
          bbgs_cc_log(400, 'bbgs_cc_course_main_ad_stmt', 'Course ['||v_course.pk1||'] is a source course.');
    --    IF( 'Y' = bbgs_cc_getCourseSetting('bbgs.cmt.source-course.on-delete.disassociate', :old.pk1, bbgs_cc_getSysCrsPk1InSysReg(), 'N') ) THEN
          -- load all of the target courses that are associated with this source course.
          FOR v_course_assc IN ( SELECT * FROM bbgs_courses_merged WHERE source_course_main_pk1 = v_course.pk1 ) LOOP
            bbgsCourseDisassociate( v_course.pk1, v_course_assc.target_course_main_pk1 );
          END LOOP;
    --    END IF;
      END IF;
    
      -- If the course to be deleted is a target course, then the target course must
      -- be disassociated from all source courses if the CMT is configured to do so.
      IF( v_course.pk1 > 0 AND bbgs_cc_isTargetCourse(v_course.pk1) ) THEN
          bbgs_cc_log(400, 'bbgs_cc_course_main_ad_stmt', 'Course ['||v_course.pk1||'] is a target course.');
    --    IF( 'Y' = bbgs_cc_getCourseSetting('bbgs.cmt.target-course.on-delete.disassociate', :old.pk1, bbgs_cc_getSysCrsPk1InSysReg(), 'N') ) THEN
          -- load all of the source courses that are associated with this target course.
          FOR v_course_assc IN ( SELECT * FROM bbgs_courses_merged WHERE target_course_main_pk1 = v_course.pk1 ) LOOP
            bbgsCourseDisassociate( v_course_assc.source_course_main_pk1, v_course.pk1 );
            -- delete the merged_courses record for this target, and update the status of the sources
            --bbgs_cc_disassociateCourse( v_course_assc.source_course_main_pk1, v_course.pk1 );
            -- delete all the records in the stat table
            --DELETE FROM bbgs_cc_course_users_stat WHERE crsmain_pk1 = v_course.pk1;
          END LOOP;
    --    END IF;
      END IF;
    
      END LOOP;
    END;
    /
    --
    -- TRIGGER [bbgs_cc_course_users_biu_rows]
    --
    --   This trigger performs 2 operations based on the "type" of course associated
    --   with the incoming course_users insert/update.
    --     - If the associated course is a "source" course, then the record is cached
    --       in a custom table to be evaluated in the after insert/update statement
    --     - If the associated course is a "target" course, then the record is
    --       mutated before insert/update with the appropriate values as calculated
    --       by the after insert/update statement and stored in the custom table
    --       bbgs_cc_course_users_stat.
    --
    CREATE OR REPLACE TRIGGER bbgs_cc_course_users_biu_rows BEFORE INSERT OR UPDATE ON course_users FOR EACH ROW
    DECLARE
      v_target_row_count   INTEGER;
      v_target_avail_ind   course_users.available_ind%TYPE := null;
      v_target_row_status  course_users.row_status%TYPE    := null;
    BEGIN
      bbgs_cc_log(400, 'bbgs_cc_course_users_biu_rows', 'Invoked for Course['||:new.crsmain_pk1||'] User['||:new.users_pk1||'] Pk1['||:new.pk1||'].');
    
      IF( bbgs_cc_isSourceCourse(:new.crsmain_pk1) ) THEN
        bbgs_cc_log(500, 'bbgs_cc_course_users_biu_rows', 'The Course['||:new.crsmain_pk1||'] is a source course, saving.');
        -- add the record to the cache, to be read in the after insert/update statement trigger
        INSERT INTO bbgs_cc_course_users_cache
               (     pk1,      row_status,      available_ind,      role,      crsmain_pk1,      users_pk1,      data_src_pk1,      cartridge_ind,      crsmain_sos_id_pk2,      users_sos_id_pk2,      sos_id_pk2)
        VALUES (:new.pk1, :new.row_status, :new.available_ind, :new.role, :new.crsmain_pk1, :new.users_pk1, :new.data_src_pk1, :new.cartridge_ind, :new.crsmain_sos_id_pk2, :new.users_sos_id_pk2, :new.sos_id_pk2);
      -- end is source course
      ELSE -- the course could be a target course, check it
    
      IF( bbgs_cc_isTargetCourse(:new.crsmain_pk1) ) THEN
        bbgs_cc_log(500, 'bbgs_cc_course_users_biu_rows', 'The Course['||:new.crsmain_pk1||'] is a target course.  Checking attributes.');
        -- determine the row_status and available indicator from the calculated cache
        SELECT count(*) INTO v_target_row_count FROM bbgs_cc_course_users_stat WHERE crsmain_pk1 = :new.crsmain_pk1 AND users_pk1 = :new.users_pk1;
        IF( v_target_row_count = 1 ) THEN
          SELECT row_status, available_ind INTO v_target_row_status, v_target_avail_ind FROM bbgs_cc_course_users_stat WHERE crsmain_pk1 = :new.crsmain_pk1 AND users_pk1 = :new.users_pk1;
        END IF;
        -- if the incoming row status is enabled, then we don't care about the
        -- value in the cache.  This enforces the rule that a target membership
        -- can always be updated to enabled (via snapshot) directly regardless
        -- of the state of any associated source memberships.
        IF( :new.row_status <> 0 AND v_target_row_status IS NOT NULL ) THEN
          -- if the new row status is not enabled and the calculated value
          -- is not null, then we use the calculate value for the member.
          bbgs_cc_log(500, 'bbgs_cc_course_users_biu_rows', 'The Course['||:new.crsmain_pk1||'] User ['||:new.users_pk1||']
    	  row_status ['||:new.row_status||']->['||v_target_row_status||'].');
          :new.row_status:= v_target_row_status;
        END IF;
        -- the same condition applies for availability where 'Y' has the
        -- same meaning as a row_status of enabled.
        IF( :new.available_ind <> 'Y' AND v_target_avail_ind IS NOT NULL ) THEN
          bbgs_cc_log(500, 'bbgs_cc_course_users_biu_rows', 'The Course['||:new.crsmain_pk1||'] User ['||:new.users_pk1||']
    	  available_ind ['||:new.available_ind||']->['||v_target_avail_ind||'].');
          :new.available_ind:= v_target_avail_ind;
        END IF;
      END IF; -- end is target course
    
      END IF; -- end if-else (is source course)
    END;
    /
    --
    
    /

  6. #6
    Join Date
    Oct 2010
    Posts
    8
    Code:
    -- TRIGGER [bbgs_cc_course_users_aiu_stmt]
    --
    --  This trigger performs the bulk of the "merging" associated with this application.
    --  for each source course membership that is modified by an insert/update statement
    --  to course users, this trigger calculates the attributes that should be sent to
    --  the target course membership and stores them in the custom attribute table
    --  bbgs_cc_course_users_stat.  It then issues an update to the target course membership
    --  so the the before insert/update trigger can modify the target course membership
    --  before inserting/updating the record in the course_users table.
    --
    CREATE OR REPLACE TRIGGER bbgs_cc_course_users_aiu_stmt AFTER INSERT OR UPDATE ON course_users
    DECLARE
      v_target_avail_ind   course_users.available_ind%TYPE;
      v_target_row_status  course_users.row_status%TYPE;
    BEGIN
      bbgs_cc_log(400, 'bbgs_cc_course_users_aiu_stmt', 'Invoked.');
    
      IF( bbgs_cc_getStmtStatusAsInt('course_users_update') > 0 ) THEN
        -- If the statement is already executing, then we leave the statement early
        -- to prevent recursive calls to do the same body of work.  This addresses
        -- the issue where enrollment disabled that occur at the end of snapshot were
        -- recursing more than 50 times and failing.
        bbgs_cc_log(400, 'bbgs_cc_course_users_aiu_stmt',  'Trigger already in progress, returning.');
        RETURN; -- execution in progress, exit method
      END IF;
      -- set the count = 1 as we are in course execution
      bbgs_cc_setStmtStatus('course_users_update', '1', 'Processing...', true);
    
      BEGIN -- The "work" block
    
      FOR v_cu_cache_row IN ( SELECT * FROM bbgs_cc_course_users_cache ) LOOP
        DELETE FROM bbgs_cc_course_users_cache WHERE pk1 = v_cu_cache_row.pk1;
        -- initialize the target row status and available inicator to the current values
        v_target_avail_ind  := v_cu_cache_row.available_ind;
        v_target_row_status := v_cu_cache_row.row_status;
    
        -- If the course membership that is being updated belongs to a source course
        IF( bbgs_cc_isSourceCourse(v_cu_cache_row.crsmain_pk1) ) THEN
          -- For each target course that is fed from this source course
          FOR v_target_course IN ( SELECT pk1 FROM course_main where pk1 IN (
                                     SELECT target_course_main_pk1 FROM bbgs_courses_merged WHERE source_course_main_pk1 = v_cu_cache_row.crsmain_pk1 ) 
                                 )
          LOOP
            -- TODO
            -- Make sure that the operation is enabled for this role in the target course
            -- bbgs_cc_isEnabledForCourseRole
    
            FOR source_membership IN ( SELECT * FROM course_users WHERE users_pk1 = v_cu_cache_row.users_pk1 AND pk1 <> v_cu_cache_row.pk1 AND crsmain_pk1 IN (
                                         SELECT source_course_main_pk1 FROM bbgs_courses_merged WHERE target_course_main_pk1 = v_target_course.pk1 )
                                     )
            LOOP
              -- Loop through the source course memberships for this target course and set
              -- the rows_status, availability, etc., based on the state of the source enrollments
              IF( v_target_row_status <> 0 AND source_membership.row_status = 0 ) THEN -- inherit a row status of enabled
                v_target_row_status := 0;
              END IF;
              IF( v_target_avail_ind <> 'Y' AND source_membership.available_ind = 'Y' ) THEN -- inherit availability of 'Y'
                v_target_avail_ind := 'Y';
              END IF;
            END LOOP; -- end for source_membership
    
            -- insert the calculated record so the before insert trigger can update accordingly
            bbgs_cc_course_users_stat_cu(v_target_course.pk1, v_cu_cache_row.users_pk1, v_target_row_status, v_target_avail_ind);
    
            -- if the target course membership exists
            IF( bbgs_cc_courseUserExists( v_target_course.pk1, v_cu_cache_row.users_pk1 ) ) THEN
              -- If a membership for the inserted course user already exists in the target
              -- course(s) we need to update the destination enrollment if the current values
              -- of the cache do not match the expected values.
              UPDATE course_users SET
                   row_status         = v_target_row_status
                  ,available_ind      = v_target_avail_ind
                  ,role               = v_cu_cache_row.role
    --              ,data_src_pk1       = v_cu_cache_row.data_src_pk1
    --              ,crsmain_sos_id_pk2 = v_cu_cache_row.crsmain_sos_id_pk2
    --              ,users_sos_id_pk2   = v_cu_cache_row.users_sos_id_pk2
    --              ,sos_id_pk2         = v_cu_cache_row.sos_id_pk2
              WHERE crsmain_pk1 = v_target_course.pk1 AND users_pk1 = v_cu_cache_row.users_pk1;
            ELSE
              -- It does not exist, so we will insert a new record.  Note that the calculation
              -- of row_status, availability, data_source, etc. is handled by this trigger for
              -- insert below when it hits this trigger again under the condition "is target course"
              INSERT INTO course_users
                       (                      pk1,          row_status,      available_ind,                role,         crsmain_pk1,                users_pk1,                data_src_pk1,                cartridge_ind, enrollment_date,                 crsmain_sos_id_pk2,                users_sos_id_pk2,                sos_id_pk2)
                VALUES ( course_users_seq.nextval, v_target_row_status, v_target_avail_ind, v_cu_cache_row.role, v_target_course.pk1, v_cu_cache_row.users_pk1, v_cu_cache_row.data_src_pk1, v_cu_cache_row.cartridge_ind,         sysdate,  v_cu_cache_row.crsmain_sos_id_pk2, v_cu_cache_row.users_sos_id_pk2, v_cu_cache_row.sos_id_pk2);
              INSERT INTO tasks_users
                       (                     pk1, tasks_pk1,                users_pk1,   priority,   status )
               SELECT    tasks_users_seq.nextval,     t.pk1, v_cu_cache_row.users_pk1, t.priority,      'N'
                 FROM tasks t WHERE t.crsmain_pk1 = v_target_course.pk1;
            END IF;
            -- Create bbgs_enrollments_grafted record
            -- finally, if the record does not exist in the enrollments merged table, we insert it here
            bbgs_enrollments_grafted_cu(v_cu_cache_row.crsmain_pk1, v_target_course.pk1, v_cu_cache_row.users_pk1);
          END LOOP; -- END FOR
        END IF; -- is source course
      END LOOP; -- cached rows
    
      EXCEPTION -- For for "work" block
        WHEN OTHERS THEN
          bbgs_cc_setStmtStatus('course_users_update', '0', 'Not currently processing.', true);
          RAISE;
      END; -- "work" block
    
      -- The statement is done, we set the execution count back to 0.
      bbgs_cc_setStmtStatus('course_users_update', '0', 'Not currently processing.', true);
    
    --  EXCEPTION
    --   WHEN OTHERS THEN
    --     -- In case an exception occurs, we still need to set this back to 0.
    --     bbgs_cc_setSysRegValue('bbgs.cmt.recurse_count.bbgs_cc_course_users_aiu_stmt', '0', 'Not currently processing.', true);
    --     RAISE; -- The same exception that was thrown.
    END;
    /
    --
    -- TRIGGER [bbgs_cc_course_users_bd_rows]
    --
    --  This trigger caches the course_users record that is about do be deleted so
    --  that the after-delete statement-trigger below can perform calcualtions on
    --  the deleted records.
    --
    CREATE OR REPLACE TRIGGER bbgs_cc_course_users_bd_rows BEFORE DELETE ON course_users FOR EACH ROW
    DECLARE
      v_sys_course_pk1 course_main.pk1%TYPE;
      v_row_status     course_registry.registry_value%TYPE;
    BEGIN
      bbgs_cc_log(400, 'bbgs_cc_course_users_bd_rows', 'Invoked for Course['||:old.crsmain_pk1||'] User['||:old.users_pk1||'] Pk1['||:old.pk1||'].');
    
      -- simply copy the row to the cache table
      INSERT INTO bbgs_cc_course_users_cache_rm
               (      pk1,      crsmain_pk1,      users_pk1 )
        VALUES ( :old.pk1, :old.crsmain_pk1, :old.users_pk1 );
    END;
    /
    --
    -- TRIGGER [bbgs_cc_course_users_ad_stmt]
    --
    --  This trigger is responsible for cleaning up any enrollment-scoped information
    --  and recalculating target course membership status (availability and row_status)
    --  when an enrollment in a source course is deleted.
    --
    --  The temporary table that is read by this trigger is populated by the before-
    --  delete-rows trigger above.
    --
    CREATE OR REPLACE TRIGGER bbgs_cc_course_users_ad_stmt AFTER DELETE ON course_users
    BEGIN
      bbgs_cc_log(400, 'bbgs_cc_course_users_ad_stmt', 'Invoked.');
    
      FOR v_member IN ( SELECT * FROM  bbgs_cc_course_users_cache_rm ) LOOP
        -- immediately delete the cache entry
        DELETE FROM bbgs_cc_course_users_cache_rm WHERE pk1 = v_member.pk1;
    
        IF( v_member.crsmain_pk1 > 0 AND bbgs_cc_isSourceCourse(v_member.crsmain_pk1) ) THEN
          -- load all of the target courses that are fed by this source course
          FOR v_merged IN ( SELECT * FROM bbgs_courses_merged WHERE source_course_main_pk1 = v_member.crsmain_pk1 ) LOOP
            -- and disasociate the membership from the source and target course
            bbgs_cc_disassociateMember( v_merged.source_course_main_pk1, v_merged.target_course_main_pk1, v_member.users_pk1 );
          END LOOP;
        END IF;
    
        -- If the course is a target course, we don't have to take any special
        -- action.  We just let it get deleted.  More than likely, the target
        -- membership will get recreated when one of the source enrollments is
        -- updated.  Under normal circumstances, administrators should not be
        -- removing memberships from target courses.  The only real use-case
        -- is when target courses are being purged as part of a maintenance plan.
    
      END LOOP;
    END;
    /
    BEGIN
      -- Delete old semaphores from system registry.
      DELETE FROM system_registry where registry_key = 'bbgs.cmt.recurse_count.bbgs_cc_course_users_aiu_stmt';
      DELETE FROM system_registry where registry_key = 'bbgs.cmt.recurse_count.bbgs_cc_course_main_update';
    END;

  7. #7
    Join Date
    Jan 2012
    Posts
    84
    Your code calls several routines you didn't show us:
    bbgs_cc_setSysCrsPk1InSysReg
    bbgs_cc_log
    bbgs_cc_isSourceCourse
    bbgs_cc_getCourseSetting
    bbgsCourseDisassociate
    bbgs_cc_course_users_stat_cu
    bbgs_cc_isTargetCourse
    bbgs_cc_courseUserExists
    bbgs_enrollments_grafted_cu
    bbgs_cc_disassociateMember


    Anyway, a big performance/scalability issue is in the procedure bbgs_cc_setStmtStatus.
    It is called twice at the beginning and at the end of this trigger:
    Code:
    CREATE OR REPLACE TRIGGER bbgs_cc_course_users_aiu_stmt AFTER INSERT OR UPDATE ON course_users
       ...
       bbgs_cc_setStmtStatus('course_users_update', '1', 'Processing...', true);
       .... quite a lot processing is done here
       bbgs_cc_setStmtStatus('course_users_update', '0', 'Not currently processing.', true);

    Code:
    CREATE OR REPLACE PROCEDURE bbgs_cc_setStmtStatus(
       registryKey   system_registry.registry_key%TYPE
      ,registryVal   system_registry.registry_value%TYPE
      ,registryDesc  system_registry.description%TYPE
      ,overwrite     boolean
    ) AS
      record_count NUMBER;
    BEGIN
      SELECT count(*) INTO record_count FROM bbgs_cc_stmt_status WHERE registry_key = registryKey;
      IF (record_count > 0 ) THEN
        IF( overwrite ) THEN
          UPDATE bbgs_cc_stmt_status SET
            registry_value = registryVal,
            description    = registryDesc,
            dtmodified     = sysdate
          WHERE registry_key = registryKey;
        END IF;
      ELSE
        INSERT INTO bbgs_cc_stmt_status
          ( registry_key,  registry_value,  description  ) VALUES
          (  registryKey,     registryVal, registryDesc  );
      END IF;
    The procedure always updates the same record (registryKey='course_users_update').
    A lock is created on this record and it is being held until a transaction that caused the trigger to fire (insert or update)
    commits or rollbacks in this session.
    Other sessions that try to insert/update records in the same table must wait until this session (GUI or the scheduler) commits the transaction.
    Then the next session holds this record and again and again - other sessions are still waiting.
    Last edited by kordirko; 02-25-12 at 21:42.

  8. #8
    Join Date
    Oct 2010
    Posts
    8
    Kordiko : Thanks a lot for your kind help.

    Yes, i could not provide them as it would have meant too verbose and big posts which people would not read. If its required, I can provide the sql scripts for all these functions, procedures and triggers.

    The part about the procedure you have mentioned is absolutely correct. The function getStmtStatus() did not function properly as a result and caused the entire database to be locked leading to the database crashing.

    Please can you help me to rectify this as its critical for our clients and a lot is upon me to get this rectified. Do tell me where all i would need to make changes in the script. I was told to remove all the triggers [ as they were doing bulk of the DML where heavy data insert/update/delete is done] and make the application free of such problems and move to an alternate solution. I am clueless.

  9. #9
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by sid2986 View Post
    Kordiko
    Please can you help me to rectify this as its critical for our clients and a lot is upon me to get this rectified. Do tell me where all i would need to make changes in the script.
    I am sorry, I can't help. Ask the author of the script what is purpose of this procedure and whether disabling this procedure is safe and won't brake the application.

    Quote Originally Posted by sid2986 View Post
    I was told to remove all the triggers [ as they were doing bulk of the DML where heavy data insert/update/delete is done] and make the application free of such problems and move to an alternate solution.
    This is the best option. Rewrite the application without using triggers.
    Use plain packages and procedures instead. Code the application logic in procedures, give your clients (GUIs, jobs) access only to these procedures (like add_user, enroll_user, delete_user etc. etc), and hide your data, don't allow them to directly modify tables with insert, update or delete.

Posting Permissions

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