1 2 3 Previous Next 33 Replies Latest reply on Aug 31, 2018 8:46 AM by Sven W.

    Lock Table but only for external Sessions

    HansMueller69

      Hello

       

      I need to execute many DML-Operations on a table but I want to lock the table for external sessions during this interaction.

      Problem: I want to execute COMMITs too.

       

      How can I do this?

       

      1.) LOCK TABLE or set it READ ONLY BUT only for other sessions

      2.) UPDATE table -> COMMIT and more UPDATE/INSERT etc. COMMIT

      3.) UNLOCK TABLE or set it back to READ WRITE

       

      Problem:

      If I use the LOCK TABLE IN EXCLUSIVE MODE or other Modes a COMMIT releases the lock

      If I use the ALTER TABLE ... SET READ ONLY  -> then I can not update the rows any more in my own session.

       

      How can I prevent other DMLs from other sessions on my table during DML processings?

       

      Thank you all for your help,

      Regards, Hans

        • 1. Re: Lock Table but only for external Sessions
          BluShadow

          What's wrong with locking the table in Exclusive mode and just not committing until you're actually finished.

          The point of committing is to say the overall business transaction is complete and you're happy to release it, so committing before that time indicates a lack of commit logic.

          • 2. Re: Lock Table but only for external Sessions
            Paul  Horth

            HansMueller69 wrote:

             

            Hello

             

            I need to execute many DML-Operations on a table but I want to lock the table for external sessions during this interaction.

            Problem: I want to execute COMMITs too.

             

            How can I do this?

             

            1.) LOCK TABLE or set it READ ONLY BUT only for other sessions

            2.) UPDATE table -> COMMIT and more UPDATE/INSERT etc. COMMIT

            3.) UNLOCK TABLE or set it back to READ WRITE

             

            Problem:

            If I use the LOCK TABLE IN EXCLUSIVE MODE or other Modes a COMMIT releases the lock

            If I use the ALTER TABLE ... SET READ ONLY -> then I can not update the rows any more in my own session.

             

            How can I prevent other DMLs from other sessions on my table during DML processings?

             

            Thank you all for your help,

            Regards, Hans

            Why? Oracle is quite capable of handling multiple readers and writers with no problems.

            What specific reason is there for locking out other users?

            • 3. Re: Lock Table but only for external Sessions
              HansMueller69

              The table has more than 3'000'000'000 Rows in the Table.

              The Table is partitioned.

               

              I want to update columns but pro partition and commit. -> In this case we do not need very large undo tablespace.

               

              And of course If I am on the last partition I do not want that other transactions execute updates on the first partition...

               

              I want to execute all dml operations and after then release the lock..

               

              Thank you all in advance.

              • 4. Re: Lock Table but only for external Sessions
                BEDE

                Another way to lock "something" - not necessarily a table - and also allow you to commit several times while maintaining the lock is to use the facilities of dbms_lock.

                For different procedures that should not run at the very same time, you will use dbms_lock.allocate_unique and then dbms_lock.request. After a procedure has done all that needs that lock use dbms_lock.release.

                 

                https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lock.htm#ARPLS021

                • 5. Re: Lock Table but only for external Sessions
                  HansMueller69

                  Hi Bede

                   

                  Very good your idea!

                   

                  Could you please help me how I should complete my code:

                   

                  DECLARE
                     v_stmt             VARCHAR2(12000);
                  BEGIN
                       -- Here should I start the lock:
                      DBMS_LOCK. .... ?
                  
                      --******* Insert Into Table OMG.OMG_BUSINESS_PLAN *******
                      FOR rec_par IN (SELECT table_owner, table_name, partition_name, partition_position
                                        FROM DBA_TAB_PARTITIONS
                                       WHERE table_owner = 'OMG'
                                         AND table_name  = 'OMG_BUSINESS_PLAN'
                                       ORDER BY partition_position
                                     )
                      LOOP
                          v_stmt :=    'UPDATE /*+ PARALLEL(16) ENABLE_PARALLEL_DML */'                                 || CHR(10)
                                    || '       OMG.OMG_BUSINESS_PLAN PARTITION (' || rec_par.partition_name || ') PKL'  || CHR(10)
                                    || '   SET x_transaktion = TO_CHAR(n_transaktion)'                                  || CHR(10)
                                   ;
                  
                          IF rec_par.partition_position = 1
                           THEN
                               GLOB_PA_UTIL.PRINT(v_stmt || ';', p_break_at => 400, p_del_start_spaces => FALSE);
                          END IF;
                  
                          EXECUTE IMMEDIATE v_stmt;
                  
                          GLOB_PA_UTIL.PRINT_TS(  'There were ' || SQL%ROWCOUNT || '-Rows in the table OMG.OMG_BUSINESS_PLAN Partition: '  || rec_par.partition_name || ' updated.'
                                                , p_break_at => 400, p_del_start_spaces => FALSE);
                  
                          COMMIT;
                  
                      END LOOP;
                  
                    DBMS_LOCK.RELEASE .... ? 
                  
                     EXCEPTION WHEN OTHERS
                        THEN DBMS_OUTPUT.PUT_LINE('Error at Updating!' || CHR(10) || SQLERRM);
                             RAISE;
                  END;
                  /
                  

                   

                  Thank You and Regards,

                  David

                  • 6. Re: Lock Table but only for external Sessions
                    BEDE

                    HansMueller69 wrote:

                     

                    Hi Bede

                     

                    Very good your idea!

                     

                    Could you please help me how I should complete my code:

                     

                    1. DECLARE
                    2. v_stmtVARCHAR2(12000);
                    3. v_lkh varchar2(30);
                    4. v_lock_name varchar2(30):='OMG_BUSINESS_PLAN';
                    5. v_ret number;
                    6. BEGIN
                    7. --HereshouldIstartthelock:
                    8. dbms_lock.allocate_unique(v_lock_name,v_lkh);
                    9. v_ret:=dbms_lock.request(v_lkh,dbms_lock.x_mode,3,false);
                    10. if v_ret!=0 then
                    11.   raise_application_error(-20012,'unable to acquire lock '||v_lock_name);
                    12. end if;
                    13. --*******InsertIntoTableOMG.OMG_BUSINESS_PLAN*******
                    14. FORrec_parIN(SELECTtable_owner,table_name,partition_name,partition_position
                    15. FROMDBA_TAB_PARTITIONS
                    16. WHEREtable_owner='OMG'
                    17. ANDtable_name='OMG_BUSINESS_PLAN'
                    18. ORDERBYpartition_position
                    19. )
                    20. LOOP
                    21. v_stmt:='UPDATE/*+PARALLEL(16)ENABLE_PARALLEL_DML*/'||CHR(10)
                    22. ||'OMG.OMG_BUSINESS_PLANPARTITION('||rec_par.partition_name||')PKL'||CHR(10)
                    23. ||'SETx_transaktion=TO_CHAR(n_transaktion)'||CHR(10)
                    24. ;
                    25. IFrec_par.partition_position=1
                    26. THEN
                    27. GLOB_PA_UTIL.PRINT(v_stmt||';',p_break_at=>400,p_del_start_spaces=>FALSE);
                    28. ENDIF;
                    29. EXECUTEIMMEDIATEv_stmt;
                    30. GLOB_PA_UTIL.PRINT_TS('Therewere'||SQL%ROWCOUNT||'-RowsinthetableOMG.OMG_BUSINESS_PLANPartition:'||rec_par.partition_name||'updated.'
                    31. ,p_break_at=>400,p_del_start_spaces=>FALSE);
                    32. COMMIT;
                    33. ENDLOOP;
                    34. v_ret:=dbms_lock.release(v_lkh);
                    35. EXCEPTIONWHENOTHERS
                    36. THENDBMS_OUTPUT.PUT_LINE('ErroratUpdating!'||CHR(10)||SQLERRM);
                    37. RAISE;
                    38. END;
                    39. /

                     

                    Thank You and Regards,

                    David

                     

                    It would be something more or less like above. Only any other session that would try to perform some DML on OMG_BUSINESS_PLAN would have to try to acquire the same lock in exclusive mode. And I think that may be a problem in case know not all the context in which DML is performed on OMG_BUSINESS_PLAN.

                     

                    As a POC on how dbms_lock works, try to run at almost the same time the following piece of code:

                    declare

                      v_lkh varchar2(30);

                      v_lock_name varchar2(30):='XL';

                      v_ret number;

                    begin

                      dbms_lock.allocate_unique(v_lock_name,v_lkh);

                      v_ret:=dbms_lock.request(v_lkh,dbms_lock.x_mode,3,false);

                      if v_ret!=0 then

                        raise_application_error(-20012,'unable to acquire lock '||v_lock_name);

                      end if;

                      dbms_lock.sleep(10);

                      v_ret:=dbms_lock.release(v_lkh);

                    end;

                     

                    You will se that one of those raises an exception.

                    Still, I don't seem to understand what could go wrong if you simply rely on the Oracle native mechanism of locking.

                     

                     

                    One more thing I have noticed:

                     

                    1. EXCEPTION WHEN OTHERS
                    2. THEN DBMS_OUTPUT.PUT_LINE('ErroratUpdating!'||CHR(10)||SQLERRM);
                    3. RAISE;
                    4. END;

                     

                    That doesn't make much sense. Just omit the exception altogether and the behavior will not be much different. raise in handling when others does the very same thing as if there was no exception handler and that dbms_output.put_line that you gave doesn't look like it will help much.

                    • 7. Re: Lock Table but only for external Sessions
                      BrunoVroman

                      Hello Hans,

                       

                      I assume that "external sessions" do not connect with the account owning the table but with an account that has received privileges on the table.

                      (I assume also that there are no sessions that would like to modify the user table data but connect with an account that has things like "DBA" or "UPDATE ANY TABLE"...)

                       

                      Then an option is to revoke the INSERT/UPDATE/DELETE privileges on the table during your operation, and to perform your operation either with the account owning the table or with a special account that you might create "now" and that has the necessary privileges.

                      Example: (in my situation we work with: tables belong to AAA_ADMIN, privileges are given to roles AAA_USER_ROLE (select insert delete update) and AAA_READER_ROLE (select) , and users receive the relevant role)

                          REVOKE insert, update, delete ON aaa_admin.this_table FROM aaa_user_role;

                      => as aaa_admin you can work, but people with aaa_user_role cannot modify the data. Once finished:

                           GRANT insert, update, delete ON aaa_admin.this_table TO aaa_user_role;

                       

                      If you have granted the privileges directly to users, you have to work at this level... "a)" identify all users having insert, delete or update on aaa_admin.this_table; "b)" revoke those privileges; "c)" perform your operation; "d)" reset the privileges as they were before.

                       

                      Remark: if aaa_admin owns procedures that modify the data of the table and if they are using "authid definer", you might also have to revoke "execute" on these procedures during your work.

                       

                      Best regards,

                       

                      Bruno Vroman.

                      • 8. Re: Lock Table but only for external Sessions
                        HansMueller69

                        Hi Bede

                         

                        Sorry, but your solution is not a solution for this.

                         

                        I want to lock the TABLE from incoming INSERT, UPDATE, DELETE from other sessions! I do not want to create a unique LOCK in my Procedure!

                         

                        Maybe you misunderstood what I need.

                         

                        THank you for your Answer.

                        • 9. Re: Lock Table but only for external Sessions
                          HansMueller69

                          Thank you for your effort to try me to help.

                           

                          But, your solution is not really the best. THe Schema-User has the privilege to update his own table.

                          The Application calls packages /procedures/functions under the same User.So, these have the privilege to execute the update.

                          I know, there is a Definer/Caller Right..

                           

                          "Remark: if aaa_admin owns procedures that modify the data of the table and if they are using "authid definer", you might also have to revoke "execute" on these procedures during your work."

                           

                          Yes.. But it is very "heikel".. hart to solve it.. because we change the privileges of users and packages and if the script comes to error... we should save these privileges in table etc.. that we can reproduce these in an error case..

                           

                          So it is relatively complex.

                          • 10. Re: Lock Table but only for external Sessions
                            Saubhik

                            HansMueller69 wrote:

                             

                            Hi Bede

                             

                            Sorry, but your solution is not a solution for this.

                             

                            I want to lock the TABLE from incoming INSERT, UPDATE, DELETE from other sessions! I do not want to create a unique LOCK in my Procedure!

                             

                            Maybe you misunderstood what I need.

                             

                            THank you for your Answer.

                            May be you read the documentation and make clear conception about "lock", "transactions", "readers" and "writers" and and "dbms_lock" specially release on commit parameter.

                            • 11. Re: Lock Table but only for external Sessions
                              HansMueller69

                              The question is not answered yet..

                               

                              Is it true that we can not realise so a simple thing?

                               

                              I want to execute many DML-Operations on my table AND during these Operations (with commits) I want that nobody can change the content of my table... is there no solution for it?!

                              Is it a joke?

                               

                              As in my example above If I make changes on a very big table partition by partition then how can you ensure that nobody changes the records on an already processed partition?

                               

                              I need a very simple thing:

                               

                              LOCK TABLE;

                               

                              UPDATE TABLE ... ;

                               

                              COMMIT;

                               

                              <--- Trying to execute an Update from an other Session -> Waiting or error message no matter

                               

                              UPDATE TABLE.. ;

                               

                              COMMIT;

                               

                              RELEASE TABLE;

                               

                              Does NOT Oracle offer a solution for it?!

                              • 12. Re: Lock Table but only for external Sessions
                                I want to lock the TABLE from incoming INSERT, UPDATE, DELETE from other sessions! I do not want to create a unique LOCK in my Procedure!

                                I'm not sure you understand how locks and privileges work.

                                 

                                1. locks lock an object - has NOTHING to do with a session or user

                                2. privileges - allow access to an object has EVERYTHING to do with a user but NOTHING to do with a session

                                 

                                If you don't want a particular USER to perform DML on an object then REVOKE the privileges that user has and perform the DML using another user that still has privileges.

                                 

                                For your use case that means you would NOT use the user owning the object if others can create new sessions and connect as that user.

                                • 13. Re: Lock Table but only for external Sessions
                                  HansMueller69

                                  Hi rp0428

                                   

                                  My word "Lock" is of course maybe not the best technical word here.

                                   

                                  I wanted to say with that that the Table is for the other sessions in READ-ONLY Mode.

                                   

                                  I do not want a particular user to be able to do a dml but every user and packages etc.. I want to save my table during the migration.

                                   

                                  There are many users who have the privileges to modify the table content. I can not eliminate these rights. because of this the whole application can not be stoped.

                                   

                                  So in this case your answer is that we can not solve it.. ?

                                  • 14. Re: Lock Table but only for external Sessions
                                    HansMueller69

                                    We have a central ADMINISTRATION -User and other Application users who have the privileges for example UPDATE ANY TABLE

                                    .These packages/functions under the Administration User or under these Applications user can be executeed during the migration.

                                    I can not revoke these privilege (UPDATE ANY TABLE) from the administration user...

                                     

                                    There should be a solution for it.. that I can protect my table from other DMLs during a migration! I can not believe it!

                                    1 2 3 Previous Next