Kali ini kita akan coba melakukan Simulasi Delete Cascade Menyebabkan Deadlock di Oracle. Artikel Ini kami harap dapat membantu kita semua dalam membuat foreign key untuk lebih berhati-hati saat menambahkan on delete cascade. Artikel ini masih berhubungan dengan artikel Simulasi Deadlock pada Database Oracle.
Sebelumnya mari kita baca dulu mengenai On Delete Cascade dibawah ini:
Whenever rows in the master (referenced) table are deleted (or updated), the respective rows of the child (referencing) table with a matching foreign key column will be deleted (or updated) as well. This is called a cascade delete (or update).
sumber: wiki
Berikut langkah-langkahnya Simulasi Delete Cascade Menyebabkan Deadlock di Oracle:
- Buka cmd (cmd1)
- Login ke sqlplus (cmd1)
- Create 4 table dengan foreign key on delete cascade, seperti script dibawah ini:
1234567891011121314151617181920212223242526272829DROP TABLE TBL_CHILD_1ST;DROP TABLE TBL_CHILD_2ND;DROP TABLE TBL_PARENT_1ST;DROP TABLE TBL_PARENT_2ND;CREATE TABLE "TBL_PARENT_1ST"( "KODE" NVARCHAR2(20) NOT NULL ENABLE,"NAMA" NVARCHAR2(20),"KETERANGAN" NVARCHAR2(100),CONSTRAINT "PK_TBL_PARENT_1ST" PRIMARY KEY ("KODE"));CREATE TABLE "TBL_PARENT_2ND"( "KODE" NVARCHAR2(20) NOT NULL ENABLE,"NAMA" NVARCHAR2(20),"KETERANGAN" NVARCHAR2(100),CONSTRAINT "PK_TBL_PARENT_2ND" PRIMARY KEY ("KODE"));CREATE TABLE "TBL_CHILD_1ST"( "KODE" NVARCHAR2(20) NOT NULL ENABLE,"NAMA" NVARCHAR2(20),"KETERANGAN" NVARCHAR2(100),"KODE_PARENT" NVARCHAR2(20),CONSTRAINT "PK_TBL_CHILD_1ST" PRIMARY KEY ("KODE"),CONSTRAINT "FK_TBL_CHILD_1ST_1" FOREIGN KEY ("KODE_PARENT") REFERENCES "TBL_PARENT_1ST" ("KODE") ON DELETE CASCADE);CREATE TABLE "TBL_CHILD_2ND"( "KODE" NVARCHAR2(20) NOT NULL ENABLE,"NAMA" NVARCHAR2(20),"KETERANGAN" NVARCHAR2(100),"KODE_PARENT" NVARCHAR2(20),CONSTRAINT "PK_TBL_CHILD_2ND" PRIMARY KEY ("KODE"),CONSTRAINT "FK_TBL_CHILD_2ND_1" FOREIGN KEY ("KODE_PARENT") REFERENCES "TBL_PARENT_2ND" ("KODE") ON DELETE CASCADE); - Lakukan script delete seperti dibawah ini: *jangan lakukan commit/rollback
1DELETE TBL_PARENT_1ST WHERE ROWNUM = 1; - Buka cmd baru (cmd2)
- Login ke sqlplus (cmd2)
- Lakukan script delete seperti dibawah ini: *jangan lakukan commit/rollback
12DELETE TBL_PARENT_2ND WHERE ROWNUM = 2;DELETE TBL_PARENT_1ST WHERE ROWNUM = 2; - Kembalik ke cmd1
- Lakukan script delete seperti dibawah ini: *jangan lakukan commit/rollback
1DELETE TBL_PARENT_2ND WHERE ROWNUM = 1; - Pada cmd2 akan muncul error deadlock
deadlock detected while waiting for resource
Untuk mengatasi kondisi deadlock ini bisa dilakukan dengan 2 cara:
- Lepas Action On Delete CASCADE, berikut script create table tanpa on delete cascade
1234567891011121314151617181920212223242526272829DROP TABLE TBL_CHILD_1ST;DROP TABLE TBL_CHILD_2ND;DROP TABLE TBL_PARENT_1ST;DROP TABLE TBL_PARENT_2ND;CREATE TABLE "TBL_PARENT_1ST"( "KODE" NVARCHAR2(20) NOT NULL ENABLE,"NAMA" NVARCHAR2(20),"KETERANGAN" NVARCHAR2(100),CONSTRAINT "PK_TBL_PARENT_1ST" PRIMARY KEY ("KODE"));CREATE TABLE "TBL_PARENT_2ND"( "KODE" NVARCHAR2(20) NOT NULL ENABLE,"NAMA" NVARCHAR2(20),"KETERANGAN" NVARCHAR2(100),CONSTRAINT "PK_TBL_PARENT_2ND" PRIMARY KEY ("KODE"));CREATE TABLE "TBL_CHILD_1ST"( "KODE" NVARCHAR2(20) NOT NULL ENABLE,"NAMA" NVARCHAR2(20),"KETERANGAN" NVARCHAR2(100),"KODE_PARENT" NVARCHAR2(20),CONSTRAINT "PK_TBL_CHILD_1ST" PRIMARY KEY ("KODE"),CONSTRAINT "FK_TBL_CHILD_1ST_1" FOREIGN KEY ("KODE_PARENT") REFERENCES "TBL_PARENT_1ST" ("KODE"));CREATE TABLE "TBL_CHILD_2ND"( "KODE" NVARCHAR2(20) NOT NULL ENABLE,"NAMA" NVARCHAR2(20),"KETERANGAN" NVARCHAR2(100),"KODE_PARENT" NVARCHAR2(20),CONSTRAINT "PK_TBL_CHILD_2ND" PRIMARY KEY ("KODE"),CONSTRAINT "FK_TBL_CHILD_2ND_1" FOREIGN KEY ("KODE_PARENT") REFERENCES "TBL_PARENT_2ND" ("KODE"));
atau
