Home » SQL & PL/SQL » SQL & PL/SQL » Parallel DDL statements in a same table with different partitions (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Parallel DDL statements in a same table with different partitions [message #674852] |
Tue, 19 February 2019 08:50 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
We have a LIST Partitioned table on the BRANCH Column.There is procedure bypassing branch as parameter which will truncates branch specific partition.When we are executing the same procedure with different branch parameter at the same time(parallel), some times(not always) we are getting the following exception.
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
But when I am trying to truncate the two different partition parallel (exactly at the same time) its working fine.
Unable to re product the problem manually.
DROP TABLE TEST;
CREATE TABLE TEST
(
SNO NUMBER(6), LAST_NAME VARCHAR2(30), SALARY NUMBER(6), BRANCH VARCHAR2(20)
)
PARTITION BY LIST (BRANCH)(PARTITION HYD VALUES ('HYD') , PARTITION NDL VALUES ('NDL'));
code to truncate the partitions
ALTER TABLE TEST TRUNCATE PARTITION HYD;
ALTER TABLE TEST TRUNCATE PARTITION NDL;
Here my question was when we truncate the specific partition,will it acquire the total table lock ? Partition Lock ?
Thanks
SaiPradyumn
|
|
|
|
|
|
Re: Parallel DDL statements in a same table with different partitions [message #674870 is a reply to message #674868] |
Wed, 20 February 2019 03:00 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You can do the same king of test.
First create a DDL trigger to have the time to see what happens:
SQL> create or replace trigger ddl_trigger
2 after ddl on michel.schema
3 begin
4 if ora_dict_obj_name = 'TEST' then
5 dbms_lock.sleep(60);
6 end if;
7 end;
8 /
Trigger created.
Then, in first session, truncate a partition:
SQL> ALTER TABLE TEST TRUNCATE PARTITION HYD;
During the minute it is waiting, query, in a second session, V$LOCK to get the locks the first session holds:
SQL> @lock
Sid Status User OS Pid LK Mod W Object
---------- -------- --------------- ---------- -- --- - ---------------------------------------------
145,9 ACTIVE MICHEL 5596 TM X MICHEL.TEST.HYD
TX X _SYSSMU4_3802565882$ tx: 0x0004.0016.0000AB79
TM RX MICHEL.TEST
As you can see, the session is getting an exclusive lock on the partition (, an exclusive lock on a rollback segment which protects the internal transaction modifying the SYS tables) and a row exclusive lock on the definition of the table.
Your second TRUNCATE will try to get the same latest lock so your error message, but this lock is hold very shortly (in this version) this is why you get the error only from time to time.
[Updated on: Wed, 20 February 2019 03:02] Report message to a moderator
|
|
|
Re: Parallel DDL statements in a same table with different partitions [message #674878 is a reply to message #674870] |
Wed, 20 February 2019 07:57 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Thanks Michel
for your detailed explanation .
I tried with the following example :
DROP TRIGGER DDL_TRIGGER;
DROP TABLE TEST1;
DROP TABLE JOB_SUBMIT_TEST;
DROP SEQUENCE JS_SEQ;
DROP INDEX TEST1_BRANCH;
CREATE TABLE TEST1(SNO NUMBER(6),LAST_NAME VARCHAR2(30),SALARY NUMBER(6),BRANCH VARCHAR2(20))
PARTITION BY LIST(BRANCH) (PARTITION HYD VALUES ('HYD'),PARTITION NDL VALUES ('NDL'),
PARTITION REV VALUES ('REV'),PARTITION SAR VALUES ('SAR'),
PARTITION ABC VALUES ('ABC'),PARTITION XYZ VALUES ('XYZ'),
PARTITION QAZ VALUES ('QAZ'),PARTITION WSX VALUES ('WSX'),
PARTITION EDC VALUES ('EDC'),PARTITION RFV VALUES ('RFV')
);
CREATE INDEX TEST_BRANCH ON TEST1(BRANCH) LOCAL;
CREATE TABLE JOB_SUBMIT_TEST(TIME_STAMP TIMESTAMP, MODULE VARCHAR2(1000), ID NUMBER );
CREATE SEQUENCE JS_SEQ START WITH 1 INCREMENT BY 1 ;
CREATE OR REPLACE PROCEDURE TEST_PROC(
PIN_BRANCH VARCHAR2 , VSTATUS OUT VARCHAR2)
IS
ERRMSG VARCHAR2(200);
QUERY_STRING VARCHAR2(200);
BEGIN
QUERY_STRING :='ALTER TABLE TEST1 TRUNCATE PARTITION '|| PIN_BRANCH;
INSERT
INTO JOB_SUBMIT_TEST VALUES
(
SYSTIMESTAMP ,
ERRMSG ||' SUCCESS :'||QUERY_STRING,
JS_SEQ.NEXTVAL
);
EXECUTE IMMEDIATE QUERY_STRING;
COMMIT;
VSTATUS:=0;
EXCEPTION
WHEN OTHERS THEN
ERRMSG :=' CODE :'||SQLCODE||'ERROR MSG :'||ERRMSG ;
VSTATUS := SQLCODE;
INSERT
INTO JOB_SUBMIT_TEST VALUES
(
SYSTIMESTAMP ,
ERRMSG ||' QUERY :'||QUERY_STRING,
JS_SEQ.NEXTVAL
);
COMMIT;
END;
/
CREATE OR REPLACE TRIGGER DDL_TRIGGER
AFTER DDL ON SCHEMA
BEGIN
IF ORA_DICT_OBJ_NAME = 'TEST1' THEN
DBMS_LOCK.SLEEP(60);
END IF;
END;
/
DECLARE
JOBNO NUMBER;
START_DT DATE :=SYSDATE + (1/(24 *60)) ;
PROC_TO_EXECUTED_HYD VARCHAR2(200) :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''HYD'',VSTATUS); END;';
PROC_TO_EXECUTED_NDL VARCHAR2(200) :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''NDL'',VSTATUS); END;';
PROC_TO_EXECUTED_REV VARCHAR2(200) :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''REV'',VSTATUS); END;';
PROC_TO_EXECUTED_SAR VARCHAR2(200) :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''SAR'',VSTATUS); END;';
PROC_TO_EXECUTED_ABC VARCHAR2(200) :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''ABC'',VSTATUS); END;';
PROC_TO_EXECUTED_XYZ VARCHAR2(200) :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''XYZ'',VSTATUS); END;';
PROC_TO_EXECUTED_QAZ VARCHAR2(200) :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''QAZ'',VSTATUS); END;';
PROC_TO_EXECUTED_WSX VARCHAR2(200) :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''WSX'',VSTATUS); END;';
PROC_TO_EXECUTED_EDC VARCHAR2(200) :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''EDC'',VSTATUS); END;';
PROC_TO_EXECUTED_RFV VARCHAR2(200) :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''RFV'',VSTATUS); END;';
BEGIN
DBMS_JOB.SUBMIT(JOB => JOBNO,WHAT => PROC_TO_EXECUTED_HYD ,NEXT_DATE => START_DT );
DBMS_JOB.SUBMIT(JOB => JOBNO,WHAT => PROC_TO_EXECUTED_NDL,NEXT_DATE => START_DT );
DBMS_JOB.SUBMIT(JOB => JOBNO,WHAT => PROC_TO_EXECUTED_REV ,NEXT_DATE => START_DT );
DBMS_JOB.SUBMIT(JOB => JOBNO,WHAT => PROC_TO_EXECUTED_SAR,NEXT_DATE => START_DT );
DBMS_JOB.SUBMIT(JOB => JOBNO,WHAT => PROC_TO_EXECUTED_ABC ,NEXT_DATE => START_DT );
DBMS_JOB.SUBMIT(JOB => JOBNO,WHAT => PROC_TO_EXECUTED_XYZ,NEXT_DATE => START_DT );
DBMS_JOB.SUBMIT(JOB => JOBNO,WHAT => PROC_TO_EXECUTED_QAZ ,NEXT_DATE => START_DT );
DBMS_JOB.SUBMIT(JOB => JOBNO,WHAT => PROC_TO_EXECUTED_WSX,NEXT_DATE => START_DT );
DBMS_JOB.SUBMIT(JOB => JOBNO,WHAT => PROC_TO_EXECUTED_EDC ,NEXT_DATE => START_DT );
DBMS_JOB.SUBMIT(JOB => JOBNO,WHAT => PROC_TO_EXECUTED_RFV,NEXT_DATE => START_DT );
COMMIT;
END;
/
SELECT Decode(v.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
v.locked_mode) locked_mode, D.* FROM GV$LOCKED_OBJECT V , SYS.DBA_OBJECTS D
WHERE D.OBJECT_ID = V.OBJECT_ID;
SELECT * FROM JOB_SUBMIT_TEST order by id;
SELECT * FROM SYS.DBA_JOBS WHERE LAST_DATE IS NULL;
But Still all partitions truncation are working fine :
Below is the result from few tables
Mode Owner Obejct Sub Obj Obj_id Sub_o_id Objecttype Created
Exclusive (X) QFXMAIN TEST1 WSX 217338 217338 TABLE PARTITION 20-FEB-19 01:29:28 PM
Exclusive (X) QFXMAIN TEST1 SAR 217334 217334 TABLE PARTITION 20-FEB-19 01:29:28 PM
Exclusive (X) QFXMAIN TEST1 NDL 217332 217332 TABLE PARTITION 20-FEB-19 01:29:28 PM
Row-X (SX) QFXMAIN TEST1 217330 TABLE 20-FEB-19 01:29:28 PM
Row-X (SX) QFXMAIN TEST1 217330 TABLE 20-FEB-19 01:29:28 PM
Row-X (SX) QFXMAIN TEST1 217330 TABLE 20-FEB-19 01:29:28 PM
JOB_SUBMIT_TEST
Time_stamp Module ID
20-FEB-19 01:30:49 PM SUCCESS :ALTER TABLE TEST1 TRUNCATE PARTITION ABC 1
20-FEB-19 01:30:49 PM SUCCESS :ALTER TABLE TEST1 TRUNCATE PARTITION REV 2
20-FEB-19 01:30:49 PM SUCCESS :ALTER TABLE TEST1 TRUNCATE PARTITION WSX 3
20-FEB-19 01:30:49 PM SUCCESS :ALTER TABLE TEST1 TRUNCATE PARTITION QAZ 4
20-FEB-19 01:30:49 PM SUCCESS :ALTER TABLE TEST1 TRUNCATE PARTITION SAR 5
20-FEB-19 01:30:49 PM SUCCESS :ALTER TABLE TEST1 TRUNCATE PARTITION EDC 6
20-FEB-19 01:30:49 PM SUCCESS :ALTER TABLE TEST1 TRUNCATE PARTITION XYZ 7
20-FEB-19 01:30:49 PM SUCCESS :ALTER TABLE TEST1 TRUNCATE PARTITION RFV 8
20-FEB-19 01:30:49 PM SUCCESS :ALTER TABLE TEST1 TRUNCATE PARTITION NDL 9
20-FEB-19 01:30:49 PM SUCCESS :ALTER TABLE TEST1 TRUNCATE PARTITION HYD 10
Still unable to catch the Exact time to replicate the issue.But as you explained able to see the Row Shared lock on the Object also
|
|
|
|
|
Re: Parallel DDL statements in a same table with different partitions [message #674912 is a reply to message #674905] |
Thu, 21 February 2019 10:21 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
As I said, the locks are held for a very short time.
I tried it, with the trigger, truncating 2 partitions and here what I saw (with a wait of 2-5 seconds between each return):
SQL> @lock
Sid Statut Utilisateur OS Pid LK Mod W Objet
---------- -------- --------------- ---------- -- --- - ------------------------------------------------------------
155,441 ACTIVE MICHEL 5464 TM X MICHEL.TEST.NDL
TX X _SYSSMU4_3802565882$ tx: 0x0004.001D.0000AB67
TM RX MICHEL.TEST
SQL> @lock
Sid Statut Utilisateur OS Pid LK Mod W Objet
---------- -------- --------------- ---------- -- --- - ------------------------------------------------------------
145,11 ACTIVE MICHEL 3532 TM X MICHEL.TEST.HYD
TX X _SYSSMU3_3408062297$ tx: 0x0003.001A.0000E466
TM RX MICHEL.TEST
SQL> @lock
Sid Statut Utilisateur OS Pid LK Mod W Objet
---------- -------- --------------- ---------- -- --- - ------------------------------------------------------------
145,11 ACTIVE MICHEL 3532 TM X MICHEL.TEST.HYD
TX X _SYSSMU3_3408062297$ tx: 0x0003.001A.0000E466
TM RX MICHEL.TEST
...
The 2 sessions (145 and 155) can truncate the partitions.
What I suggest you is to trap the error and reexecute the aborted command, most likely it will pass the second time.
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Jul 02 20:51:47 CDT 2024
|