Forum Stats

  • 3,838,561 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

LOCK Table from DML operations

User_BU3NG
User_BU3NG Member Posts: 116 Blue Ribbon

Hi Experts,

I have a requrement, want my table to lock until some operations with in table creations are done. (Ex : altering fields, data types etc.. thru an automation). During the table creation in case of any failures occurs, table should be in locked from all DML operation for all users.

Thanks

Tagged:

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,454 Gold Trophy

    So, you mean that a table has to be locked, but, in the mean time, there must be going on some transactions, including commits and rollbacks.

    The only way I can think of is something that goes along the lines below:

    execute immediate 'lock table x_table in exclusive mode';

    --- call procedures that run as autonomous transactions (having pragma autonomous_transaction) so that they may commit without having the lock on that table released even for a fraction of a second

    commit;

  • User_3ABCE
    User_3ABCE Member Posts: 51 Blue Ribbon

    @User_BU3NG

    table should be in locked from all DML operation for all users

    alter table tbl read only;
    ....
    alter table tbl read write;
    

    To make some "alter", you may need DML. For example, when adding a not null column without default value.

    Use EBR to make changes temporary invisible to the application.

  • Arun Kumar Gupta
    Arun Kumar Gupta Member Posts: 1,029 Gold Badge
    edited Jul 8, 2022 7:44AM

    lock_table is session specific and once session is closed lock will also be released and other users will be able to perform DMLs.

    A better option would be to make table read only before ALTER table ( DDL Commands) and if DDLs are successful make it read-write else it remains read only (even session is closed)


    BEGIN
    execute immediate 'alter table table_name read only';
    
    --Perform Your DDL Commands here.
    
    execute immediate 'alter table table_name read write';
    
    END;
    /
    


    Note that this does not stop users from selecting data from table.

    Regards

    Arun

  • User_3ABCE
    User_3ABCE Member Posts: 51 Blue Ribbon

    @BEDE

    execute immediate 'lock table x_table in exclusive mode';

    The locked table cannot be altered "Ex : altering fields, data types etc.."

    Why "execute immediate"?

  • Arun Kumar Gupta
    Arun Kumar Gupta Member Posts: 1,029 Gold Badge

    In case you dont want to make your DDLs dynamic (Dont want to use EXECUTE IMMEDIATE)

    use below in sql script.

    WHENEVER SQLERROR THEN EXIT

    Regards

    Arun

  • BEDE
    BEDE Oracle Developer Member Posts: 2,454 Gold Trophy
    edited Jul 8, 2022 8:53AM

    So, any DDL on a table locks the whole table while it is executed and it also commits at the end. Kind of you can't both have your cake and eat it at the same time. Once the DDL finished nothing would prevent other sessions from performing some DML (insert/update/delete) on that table.

    I thought you wanted to have a table locked while you perform some DDL on other table(s).

    Or, another way to implement some kind of locking mechanism in order to prevent process X to start while process Y is running is to use the facilities of the dbms_lock package (having the release_on_commit false allows commits and rollbacks in the session which acquired the lock, unlike lock table or select for update, which releases the lock on commit or update). But dbms_lock is far from being something that may prevent any session from performing some actions and may be used only for very specific purposes.

    I think your issue is that the schemas, users, objects and grants are not well designed for what you actually need and that leads you to put the problem the way you do.

    So, the classic way for handling such issues is to have the tables for which you wish the operations to be restricted under some conditions placed in a schema, and in that schema, have some packages that provide stored procedures which actually include the DML on those tables, and all the other users would not have grants on the tables themselves, but on those packages. So you may enforce complex business logic and required processing flows via PL/SQL packages.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,132 Red Diamond

    Depending on what you want to actually do (in detail) you could also use the renaming of tables... i.e. table is renamed to another name (effectively table no longer exists to users), amendments are done on the renamed table and then, once complete the table is renamed back to the original name.

    Of course that can have complications if there are referential constraints and suchlike, but without knowing the detail or how complex a task is actually required it's hard to say.

    Usually though, if updates are required that include preventing user access, then you have scheduled 'downtime' on the system, where users are prevented from accessing the entire database (through whatever means is most suitable) and then only brought back 'online' for users when the updates are complete.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond
    edited Jul 11, 2022 3:25PM

    I have a requrement, want my table to lock until some operations with in table creations are done. (Ex : altering fields, data types etc.. thru an automation). During the table creation in case of any failures occurs, table should be in locked from all DML operation for all users.

    Something doesn't add up. If any failurte occurs while creating table then table is not created and nobody can issue DML/DDL against it. In fact nobody can ssue DML/DDL against it even while table is being created. Session 1:

    drop table test purge
    /
    create table test(id number)
    /
    create or replace
      trigger after_alter
      after alter
      on database
      begin
          if ora_dict_obj_owner = 'SCOTT'
            then
              dbms_lock.sleep(60);
          end if;
    end;
    /
    alter table test
      add name varchar2(10)
    /
    

    So we wait 60 seconds before completing ALTER to mimc situation where DML is issued while we are altering table. And while we alter the table Session 2:

    insert
     into test
     values(1)
    /
      into test
           *
    ERROR at line 2:
    ORA-00947: not enough values
    
    Elapsed: 00:00:50.31
    SQL>
    

    As you can see, session 2 waited till ALTER is done and then failed since now tabke has 2 columns.

    Anyway, you need to explain in detail what are you trying to do.

    SY.