6 Replies Latest reply on Nov 6, 2012 5:46 AM by 961895

    row lock contention error to resolve

    961895
      hi,
      i m facing sever issue with row lock contention error for the statement and causing concurrency and application usage more making database vulnerable

      UPDATE RULE_DATA SET RULE_DATA = :B3 , UPDATED_BY = :B2 , UPDATED_DATE = SYSDATE WHERE RULE_DATA_SEQ_ID = :B1
      RETURNING PAT_GEN_DETAIL_SEQ_ID INTO :O0

      in this query RULE_DATA_SEQ_ID is primary key and and having index too, how can i over come row lock contention error ,

      if i try like this will it work

      UPDATE RULE_DATA SET RULE_DATA = :B3 , UPDATED_BY = :B2 , UPDATED_DATE = SYSDATE WHERE RULE_DATA_SEQ_ID = :B1
      RETURNING PAT_GEN_DETAIL_SEQ_ID INTO :O0
      log errors into temp_log (sysdate||:O0 ) REJECT LIMIT UNLIMITED;
      commit;

      please help me;
        • 1. Re: row lock contention error to resolve
          BluShadow
          user13134817 wrote:
          hi,
          i m facing sever issue with row lock contention error for the statement and causing concurrency and application usage more making database vulnerable

          UPDATE RULE_DATA SET RULE_DATA = :B3 , UPDATED_BY = :B2 , UPDATED_DATE = SYSDATE WHERE RULE_DATA_SEQ_ID = :B1
          RETURNING PAT_GEN_DETAIL_SEQ_ID INTO :O0

          in this query RULE_DATA_SEQ_ID is primary key and and having index too, how can i over come row lock contention error ,

          if i try like this will it work

          UPDATE RULE_DATA SET RULE_DATA = :B3 , UPDATED_BY = :B2 , UPDATED_DATE = SYSDATE WHERE RULE_DATA_SEQ_ID = :B1
          RETURNING PAT_GEN_DETAIL_SEQ_ID INTO :O0
          log errors into temp_log (sysdate||:O0 ) REJECT LIMIT UNLIMITED;
          commit;
          Why do you think logging the errors will help prevent some lock contention issue?

          You need to provide more information to demonstrate how this lock contention is happening and how you are determining there is a locking issue, because a simple update statement in itself will not cause lock contention unless one session is trying to update the same row of data as another and the session is not committing or rolling back. That's basic transaction processing.

          Please provide sufficient information as well as your database version as described in the FAQ: {message:id=9360002}
          • 2. Re: row lock contention error to resolve
            961895
            select sid,  sql_text from v$session s, v$sql q
            where sid in (select sid from v$session
            where state in ('WAITING')
            and wait_class != 'Idle'
            and event='enq: TX - row lock contention'
            and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));

            from the above query if found the sid and sql_text,
            actually three procedures been called at once as a batch from java and given auto commit at once for the batch;

            and in one of the procedure it is called by multiple times with different seq_id's

            what i thought is by logging error can we skip the update statement from locking , this what happening

            where v_tariff_detail_seq_id will be 25,26,28,29,30 like records

            begin

            IF v_tariff_detail_seq_id = 0 THEN
            INSERT INTO pat_tariff_details (
            tariff_detail_seq_id,
            pat_gen_detail_seq_id,
            ward_type_id ,
            room_type_id ,
            days_of_stay ,
            requested_amount,
            approved_amount,
            maximum_allowed_amount,
            notes,
            added_by,
            added_date )
            VALUES (
            pat_tariff_details_seq.NEXTVAL ,
            v_pat_gen_detail_seq_id,
            v_ward_type_id ,
            v_room_type_id ,
            v_days_of_stay ,
            v_requested_amount ,
            v_approved_amount,
            v_maximum_allowed_amount,
            v_notes,
            v_added_by,
            SYSDATE );

            ELSE
            UPDATE pat_tariff_details SET
            room_type_id = v_room_type_id,
            days_of_stay = v_days_of_stay,
            requested_amount = v_requested_amount,
            approved_amount = v_approved_amount,
            maximum_allowed_amount = v_maximum_allowed_amount,
            notes = v_notes,
            updated_by = v_added_by,
            updated_date = SYSDATE
            WHERE tariff_detail_seq_id = v_tariff_detail_seq_id;
            END IF;

            end;

            version is Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

            Edited by: user13134817 on Nov 2, 2012 2:39 AM
            • 3. Re: row lock contention error to resolve
              Billy~Verreynne
              user13134817 wrote:

              i m facing sever issue with row lock contention error for the statement and causing concurrency and application usage more making database vulnerable
              Wrong. This is not a a server issue. It is an application issue. Or a data model issue.

              Oracle does not escalate locks. So the locks created, are created as per application instruction - for making changes to the database.

              Lock contention means that application code is attempting to make changes that impacts other code attempting to change the same data.

              Locking is a Good Thing. It protects data and provides a means to enforce the integrity and consistency of data. When locking seems to be a problem, the real problem is how locking is used by the application - it is not a server issue.

              You need to determine what rows/data multiple processes are trying to change at the same time - and then determine why. The problem needs to be identified first. Only once identified, can it be analysed and resolved.
              • 4. Re: row lock contention error to resolve
                Toon_Koppelaars-Oracle
                Just a wild guess here.

                Any bitmap indexes on that table?
                • 6. Re: row lock contention error to resolve
                  961895
                  got the answer ,
                  anlaysed the index, and collected the wasted space in a table then applied the steps

                  1. rebuild the index
                  2. reorganize the table