How to avoid mysql deadlock

Methods to avoid mysql deadlock: 1. Access tables and rows in a fixed order; 2. Large transactions are more prone to deadlock. If the business allows, split large transactions into smaller ones; 3. In the same transaction , try to lock all the resources required at once to reduce the probability of deadlock; 4. Reduce the isolation level; 5. Add reasonable indexes to the table.

How to avoid deadlocks as much as possible:

1. Access tables and rows in a fixed order. For example, for two transactions that update data, transaction A updates data in the order 1, 2; transaction B updates data in the order 2, 1. This is more likely to cause deadlock.

2. Break down big things into small pieces. Large transactions are more prone to deadlock. If the business allows, split the large transactions into smaller ones.

3. In the same transaction, try to lock all the resources required at once to reduce the probability of deadlock.

4. Lower the isolation level. If the business permits, it is also a good choice to lower the isolation level. For example, adjusting the isolation level from RR to RC can avoid many deadlocks caused by gap locks.

5. Add reasonable indexes to the table. It can be seen that if the index is not used, a lock will be added to each row of the table, and the probability of deadlock will be greatly increased.

The above is the detailed content of How to avoid mysql deadlock. For more information, please follow other related articles on the PHP Chinese website!