Shulou(Shulou.com)06/01 Report--

If the insert operation on the child table is not committed after execution, it will block the subsequent update, delete, insert operations on the primary table (for operations that contain primary key columns),

The child tables are all TM locks, and the blocking problem is eliminated after the foreign key is indexed.

Create table test_pk1 (id number,pcol varchar2 (1)) tablespace users

Alter table test_pk1 add constraint pk_test_pk1_id primary key (id) using index tablespace users

Insert into test_pk1 values (1)

Insert into test_pk1 values (2)

Insert into test_pk1 values (3 recorder C')

Insert into test_pk1 values (4)

Insert into test_pk1 values (5 minutes E')

Commit

Create table test_fk1 (id number,fcol varchar2 (1)) tablespace users

Alter table test_fk1 add constraint fk_test_fk1_id foreign key (id) references test_pk1 (id)

Insert into test_fk1 values (1)

Insert into test_fk1 values (2)

Commit

Scenario 1: the child table is not committed after the insert operation is performed, and another transaction performs update, delete and insert on the main table

-- subtable

Insert into test_fk1 values (5)

-- Master table

Update test_pk1 set id=55 where id='4'

Delete test_pk1 where id='3'

Insert into test_pk1 values (6)

Conclusion: if the insert operation on the child table is not committed after execution, it will block the subsequent update, delete, insert operations on the main table (for operations that contain primary key columns)

The main table and child table are all TM locks.

-- after indexing the foreign key, perform update, delete and insert operations on the main table to see if it will have an impact

Create index idx_id on test_fk1 (id)

Conclusion: the blocking problem is eliminated after the foreign key is indexed.

Scenario 2: the child table is not committed after the update operation is performed, and another transaction performs update, delete and insert on the main table

-- subtable

Update test_fk1 set fcol='b' where id='2'

-- Master table

Update test_pk1 set id=55 where id='4'

Delete test_pk1 where id='3'

Insert into test_pk1 values (6)

Conclusion: the update operation on the child table is not submitted after it is executed, and it has no effect on the update, delete and insert operations on the main table.

Scenario 3: the child table is not committed after the delete operation is performed, and another transaction performs update, delete and insert on the main table

-- subtable

Delete test_fk1 where id='2'

-- Master table

Update test_pk1 set id=55 where id='4'

Delete test_pk1 where id='3'

Insert into test_pk1 values (6)

Conclusion: the delete operation on the child table is not submitted after it is executed, and it has no effect on the update, delete and insert operations on the main table.

Scenario 4: the master does not commit after performing the delete operation, and another transaction performs update, delete and insert on the child table

-- Master table

Delete test_pk1 where id='3'

-- subtable

Update test_fk1 set fcol='A' where id='1'

Insert into test_fk1 values (4)

Delete test_fk1 where id='2'

Conclusion: the delete operation on the main table is not submitted after it is executed, and it has no effect on the update, delete and insert operations on the child table.

Scenario 5: the master does not commit after performing the update operation, and another transaction performs update, delete, and insert on the child table

-- Master table

Update test_pk1 set pcol='F' where id='5'

-- subtable

Update test_fk1 set fcol='A' where id='1'

Insert into test_fk1 values (4)

Delete test_fk1 where id='2'

Conclusion: the update operation on the main table is not submitted after it is executed, and it has no effect on the update, delete and insert operations on the child table.

Scenario 5: the master does not commit after performing the insert operation, and another transaction performs update, delete, and insert on the child table

-- Master table

Insert into test_pk1 values (6)

-- subtable

Update test_fk1 set fcol='A' where id='1'

Insert into test_fk1 values (4)

Delete test_fk1 where id='2'

Conclusion: the delete operation on the main table is not submitted after it is executed, and it has no effect on the update, delete and insert operations on the child table.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.