8 Replies Latest reply: Jan 18, 2013 4:14 PM by rcc50886 RSS

    Re: Table Partition Busy

    rcc50886
      one application running the stored procedure in parallel and the stored procedure inserts the data into a table which was list partitioned by username. Only the user who runs will insert data into that particular user partition.

      two different users ran the procedure at the same time and getting following error:
      ORA-00054: resource busy and acquire with NOWAIT specified
      I traced the two session and the second session was failed while trying to truncate the table partition.
      LOCK TABLE "GPR"."TEST_TABLE" PARTITION ("TEST_TABLE_P1")  IN EXCLUSIVE MODE  NOWAIT
      
      alter table GPR.TEST_TABLE truncate  partition TEST_TABLE_P1
      NOTE: first user is only inserting data into his own partition and is not interfering with second user partition.


      Can any one seen this behavior and what causes this issue ??

      I know if we inserted the records and not yet commited and trying to truncate that partition from different session causes this problem, bur here we don't have that situation.


      -Thanks,
        • 1. Re: Table Partition Busy
          rp0428
          You've posted enough by now to know you need to provide your 4 digit Oracle version.
          >
          I know if we inserted the records and not yet commited and trying to truncate that partition from different session causes this problem, bur here we don't have that situation.
          >
          Well then please clarify what you said before
          >
          two different users ran the procedure at the same time and getting following error:

          ORA-00054: resource busy and acquire with NOWAIT specified
          . . .
          I traced the two session and the second session was failed while trying to truncate the table partition.
          >
          That sounds like two sessions with one trying to truncate a partition to me. Are there global indexes on the table?

          Please explain in more detail.

          Edited by: rp0428 on Sep 13, 2012 4:21 PM
          • 2. Re: Table Partition Busy
            rcc50886
            We are using 11.2.0.2 version

            There are no global indexes but we do have a local primary key index.

            Also we do have a AFTER UPDATE of row trigger on that table i.e after update of rows it will insert old values into historic table.

            Edited by: rcc50886 on Sep 13, 2012 5:37 PM
            • 3. Re: Table Partition Busy
              rp0428
              >
              We are using 11.2.0.2 version

              There are no global indexes but we do have a local primary key index.

              Also we do have a AFTER UPDATE of row trigger on that table i.e after update of rows it will insert old values into historic table.
              >
              The only way I can reproduce the problem is to try to truncate or lock a partition that another session has inserted into but not committed.

              Is it at all possible that the insert is inserting a wrong user value and inadvertently putting it in the wrong partition?

              Can you reproduce the problem at will? If so when the truncate hangs, open yet another session and query the records from the hung partition to a new table (CTAS). Then commit the insert and compare the records in the hung partition to what you just queried out to see if the number of records is the same.
              • 4. Re: Table Partition Busy
                rcc50886
                The only way I can reproduce the problem is to try to truncate or lock a partition that another session has inserted into but not committed.
                yes, we also reproduced the issue doing same.
                Is it at all possible that the insert is inserting a wrong user value and inadvertently putting it in the wrong partition?
                at first we thought of same and did a test on it. i.e we dropped all the partitions except one user and ran the procedure , it populate all the data in that partition only.
                so no data issues here.
                Can you reproduce the problem at will? If so when the truncate hangs, open yet another session and query the records from the hung partition to a new table (CTAS). Then commit the insert and compare the records in the hung partition to what you just queried out to see if the number of records is the same.
                truncate partition never hangs but simply throws error immediately exists.

                -Thanks
                • 5. Re: Table Partition Busy
                  rp0428
                  >
                  truncate partition never hangs but simply throws error immediately exists.
                  >
                  Please explain that. If you insert into a partition and then try to do either of the following
                  LOCK TABLE "GPR"."TEST_TABLE" PARTITION ("TEST_TABLE_P1")  IN EXCLUSIVE MODE  NOWAIT
                   
                  alter table GPR.TEST_TABLE truncate  partition TEST_TABLE_P1
                  Either one of those will give resource busy. Are you doing both of those?
                  • 6. Re: Table Partition Busy
                    rcc50886
                    LOCK TABLE "GPR"."TEST_TABLE" PARTITION ("TEST_TABLE_P1")  IN EXCLUSIVE MODE  NOWAIT
                     
                    alter table GPR.TEST_TABLE truncate  partition TEST_TABLE_P1
                    we are only doing "alter table GPR.TEST_TABLE truncate partition TEST_TABLE_P1"

                    the "LOCK TABLE......." statement is from the session trace file. My procedure only contains the ALTER TABLE....statement only.
                    • 7. Re: Table Partition Busy
                      985827
                      Hi, How did you solve this issue?
                      • 8. Re: Table Partition Busy
                        rcc50886
                        We created a new procedure for truncate tables and we defined table busy exception on it, so if we didn't get the exclusive lock then it just sleeps and wakeup in loop.