-
1. Re: Lock Table but only for external Sessions
BluShadow Aug 29, 2018 9:49 AM (in response to HansMueller69)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 Aug 29, 2018 9:50 AM (in response to HansMueller69)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 Aug 29, 2018 9:58 AM (in response to BluShadow)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 Aug 29, 2018 10:12 AM (in response to HansMueller69)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 Aug 29, 2018 10:51 AM (in response to BEDE)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 Aug 29, 2018 11:20 AM (in response to HansMueller69)HansMueller69 wrote:
Hi Bede
Very good your idea!
Could you please help me how I should complete my code:
- DECLARE
- v_stmtVARCHAR2(12000);
- v_lkh varchar2(30);
- v_lock_name varchar2(30):='OMG_BUSINESS_PLAN';
- v_ret number;
- BEGIN
- --HereshouldIstartthelock:
- 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;
- --*******InsertIntoTableOMG.OMG_BUSINESS_PLAN*******
- FORrec_parIN(SELECTtable_owner,table_name,partition_name,partition_position
- FROMDBA_TAB_PARTITIONS
- WHEREtable_owner='OMG'
- ANDtable_name='OMG_BUSINESS_PLAN'
- ORDERBYpartition_position
- )
- LOOP
- v_stmt:='UPDATE/*+PARALLEL(16)ENABLE_PARALLEL_DML*/'||CHR(10)
- ||'OMG.OMG_BUSINESS_PLANPARTITION('||rec_par.partition_name||')PKL'||CHR(10)
- ||'SETx_transaktion=TO_CHAR(n_transaktion)'||CHR(10)
- ;
- IFrec_par.partition_position=1
- THEN
- GLOB_PA_UTIL.PRINT(v_stmt||';',p_break_at=>400,p_del_start_spaces=>FALSE);
- ENDIF;
- EXECUTEIMMEDIATEv_stmt;
- GLOB_PA_UTIL.PRINT_TS('Therewere'||SQL%ROWCOUNT||'-RowsinthetableOMG.OMG_BUSINESS_PLANPartition:'||rec_par.partition_name||'updated.'
- ,p_break_at=>400,p_del_start_spaces=>FALSE);
- COMMIT;
- ENDLOOP;
- v_ret:=dbms_lock.release(v_lkh);
- EXCEPTIONWHENOTHERS
- THENDBMS_OUTPUT.PUT_LINE('ErroratUpdating!'||CHR(10)||SQLERRM);
- RAISE;
- END;
- /
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:
- EXCEPTION WHEN OTHERS
- THEN DBMS_OUTPUT.PUT_LINE('ErroratUpdating!'||CHR(10)||SQLERRM);
- RAISE;
- 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 Aug 29, 2018 3:39 PM (in response to HansMueller69)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 Aug 29, 2018 3:29 PM (in response to BEDE)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 Aug 29, 2018 3:36 PM (in response to BrunoVroman)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 Aug 29, 2018 3:36 PM (in response to HansMueller69)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 Aug 29, 2018 3:45 PM (in response to 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
rp0428 Aug 29, 2018 3:54 PM (in response to HansMueller69)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 Aug 29, 2018 3:59 PM (in response to rp0428)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 Aug 29, 2018 4:06 PM (in response to 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!