How to prevent database deadlocks caused by inserting a deleted table

Source: Internet
Author: User
Tags insert key

This is often the case in a database: a primary table A, a child table B,b table containing a primary key of table A as a foreign key. When inserting the data, we insert a table first, then we get the identity of table A and then insert Table B. If you want to delete, delete the child table B before deleting the primary table A. In programming, the operation of two tables is done in one transaction.

When the system is used frequently, insert and delete operations occur concurrently. This time the insert transaction places the primary table A in an exclusive lock and then accesses the child table B, while deleting the transaction places an exclusive lock on the table B and then accesses the primary table A. Insert a transaction will always exclusive of a table, waiting to access the B table, delete transactions have been exclusive of B table waiting to access a table, so two transactions each other to the exclusive table, waiting for each other to release resources, which caused the deadlock.

In this case, I've heard three different approaches:

1 cancels the foreign key relationship between the AB two tables so that when you delete the data, you can delete the primary table A and then delete the child table B so that the transaction access order for the two table operations is the same.

2 before deleting a table data, use a transaction to point the related foreign key in table B to another data in table A (for example, to create a single row of data in Table a). The primary key is set to 0 and will never delete the row of data, eliminating the relationship between the data to be deleted and the AB two tables. You can then use the delete transaction to delete the data from table A, and then delete the data from table B to achieve and insert Transaction table access consistent to avoid deadlock.

3 in a foreign key relationship, set delete rule to cascade so that deleting a transaction simply deletes the primary table a without having to do the child table B. Since the delete rule is set to cascade, the data in the primary table is deleted, and the data associated with all foreign keys in the child table is also deleted.

All three solutions are suggestions from colleagues, and I don't know what to do with them.

I don't know if there are any other good ways to prevent deadlocks in this situation.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.