We often encounter this situation in the database: a main table A and a subtable B. Table B contains the primary key of table A as a foreign key. When we want to insert data, we will first insert into table A, then obtain the Identity of table A, and then insert into table B. If you want to perform a delete operation, delete subtable B first, and then delete main table A. In programming, the operations on the two tables are completed in one transaction.
When the system is used frequently, insertion and deletion operations may occur at the same time. At this time, the insert transaction will first place an exclusive lock on the main table A, and then access the sub-table B. At the same time, the delete transaction will place an exclusive lock on the sub-table B, and then access the main table A. The insert transaction will always monopolize table A, waiting to access table B, and the delete transaction will also monopolize table B, waiting to access table A. Therefore, the two transactions monopolize a table for each other, waiting for the other party to release resources, which causes a deadlock.
I have heard of three ways to do this:
1. Cancel the foreign key relationship between the two tables AB, so that when deleting data, you can first delete the main table A, and then delete the sub-table B, so that the The transaction access sequence of these two table operations is consistent.
2 Before deleting the data in table A, first use a transaction to point the relevant 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 row of data will never be accessed. Perform a delete operation), thus eliminating the relationship between the data to be deleted in the two tables AB. Then you can use a delete transaction to first delete the data in table A, and then delete the data in table B to achieve consistent table access with the insertion transaction and avoid deadlock.
3. In the foreign key relationship, set the "Delete Rule" to "Cascading", so that the deletion transaction only needs to directly delete the main table A, without operating the sub-table B. Because after the deletion rule is set to cascade, the data in the main table is deleted, and all the data associated with the foreign keys in the sub-table are also deleted at the same time.
The above three solutions are all suggestions given by colleagues, and I don’t know which method should be used.
I wonder if there are any other good ways to prevent deadlock in this situation?
http://www.cnblogs.com/studyzy/archive/2007/06/11/779811.html