How to Prevent database deadlocks caused by table insertion and Deletion

In databases, we often encounter the following situation: A primary table A, A subtable B, and table B contain the primary key of Table A as the foreign key. When you want to insert data, we will first insert Table A, then obtain the Identity of Table A, and then insert Table B. If you want to delete table B, delete table B first, and then delete table. In program design, the operations on two tables are completed in one transaction.

When the system is frequently used, insertion and deletion operations may occur simultaneously. At this time, the insert transaction will first place the primary table A with an exclusive lock, and then access the sub-Table B. At the same time, deleting the transaction will place an exclusive lock on the sub-Table B, and then access the primary table. The insert transaction will always exclusively occupy Table A, waiting for access to table B, and deleting the transaction will always exclusively occupy table B and waiting for access to Table A. Therefore, the two transactions will exclusively occupy one table and wait for the other party to release resources, this causes a deadlock.

In this case, I have heard of three methods:

1. Cancel the foreign key relationship between two tables of AB, so that you can delete primary table A and subtable B When deleting data, make the transaction access sequence for the two tables consistent.

2. before deleting data in Table A, use A transaction to direct the foreign key in Table B to another data in Table A (for example, create A row of data in Table A and set the primary key to 0, this eliminates the relationship between the data to be deleted in the AB table. Then you can use the delete transaction to delete the data in Table A first, and then delete the data in Table B, so as to be consistent with the access to the inserted transaction table and avoid deadlocks.

3. In the foreign key relationship, set the "Delete rule" to "cascade". In this way, you only need to delete the primary table A directly, instead of the child table B. After the deletion rule is set to cascade, data in the master table is deleted, and all data associated with foreign keys in the child table is also deleted.

The above three solutions are suggestions from colleagues, and I don't know what method to use.

I don't know. Is there any other good way to prevent deadlocks in this case?