Lock Table but only for external Sessions

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.

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?

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

HansMueller69 wrote:

 

Hi Bede

 

Very good your idea!

 

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

 

 

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.

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.

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.

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.