Night-time quiz

Since I do not have anything I think would be interesting for you guys, I’ve created a little quiz for you.





You have a following table definition and data:

CREATE TABLE my_table
(
 id       NUMBER(3),
 item_cnt NUMBER(3),
 log_time DATE
)
/
CREATE UNIQUE INDEX my_table_ix1 ON my_table(id)
/
ALTER TABLE my_table ADD CONSTRAINT my_table_pk PRIMARY KEY (id)
/

INSERT INTO my_table VALUES (1, NULL, NULL)
/
INSERT INTO my_table VALUES (2, NULL, NULL)
/
INSERT INTO my_table VALUES (3, NULL, NULL)
/
COMMIT
/

Now we have following anonymous PL/SQL block:

DECLARE
 v_id my_table.id%TYPE := 1;
 --
 PROCEDURE ins (p_id my_table.id%TYPE)
 IS
 BEGIN
  INSERT INTO my_table VALUES (p_id, NULL, NULL);
END;
 --
BEGIN
 DELETE my_table WHERE id = v_id;
 ins (v_id);
END;
/

The question is … With what change to the code would you force a dead lock using same id?

Looking forward to your ideas …

UPDATE:

Thank you all for your comments :) He is the solotion:

DECLARE
 v_id my_table.id%TYPE := 1;
 --
 PROCEDURE ins (p_id my_table.id%TYPE)
 IS PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
  INSERT INTO my_table VALUES (p_id, NULL, NULL);
END;
 --
BEGIN
 DELETE my_table WHERE id = v_id;
 ins (v_id);
END;
/

00060. 00000 - "deadlock detected while waiting for resource"
*Cause: Transactions deadlocked one another while waiting for resources.
*Action: Look at the trace file to see the transactions and resources
involved. Retry if necessary.