|
In the database system, the deadlock seems an inevitable issue, either because of mutual occupation of resources, or from lock upgrades in the system (especially rife in InnoDB). In particular, poor SQL design is usually the culprit behind deadlocks. In the MySQL InnoDB engine, the deadlock detection is achieved through deep traversing. Every row lock request in the waiting state should be checked whether a deadlock has been formed.
For information about InnoDB transaction locks, you can refer to one of my previous blogs as the topic won't be discussed here: MySQL engine features - about InnoDB transaction locks Deadlock detection is an indispensable feature for a mature database system, but... If the SQL statements for our applications have been reasonably designed and fully validated, so that the vast majority of deadlocks can be eliminated, can we attempt to avoid this overhead? A typical scenario is the flash sales during which a huge amount of updates land on the same row of a record. At this time, the exclusive row locks of the huge amount of requests for the same record will lead to a long waiting queue, and the deadlock detection will query the entire queue. During the whole process, some global resources (such as lock_sys mutex) will be held. In order to avoid spending too much time on an in-depth detection, InnoDB sets a default maximum detection depth of 200. When this limit is exceeded, the deadlock information will be printed out and the deadlock detection will be stopped. In Alibaba, the flash sales are everywhere. In fact, before the Double 11 carnival in 2012, the first patch we used to modify MySQL was to shut down deadlock detection. The amount of code is small, only a few lines of code, but the effect turned out to be pretty good. (Of course, this is only the first step of our optimization to high-concurrency and high-load scenarios such as the flash sales, and it is far from meeting the business demand in recent years. We later adopted a series of optimizing measures to improve MySQL performance to meet the requirements. The measures have been mentioned by my colleagues on various occasions and I will not go into details here.) MySQL 5.7.15 and MySQL 8.0.0 finally have this feature available, and the innodb_deadlock_detect switch is provided to disable deadlock detection. Here we can run a simple test in MySQL 8.0.0 (I overwrote my MySQL 5.7 testing environment when MySQL 8.0.0 was just released and don't feel like re-installing it again). Perform single-row updates with sysbench and autocommit. Key configurations: innodb_thread_concurrency = 32 sync_binlog = 1000 innodb_flush_log_at_trx_commit = 2 Testing data is TPS (RT ms): 
From the test we can see that under the low concurrency, the performance improvement is not obvious. But in the high-concurrency scenario, the TPS and RT performances are improved significantly. In the test, when there are 1,024 concurrent requests, the instance is totally unusable. But with the deadlock detection disabled, the instance can provide a TPS of 3,000. But please pay attention that you must use this feature carefully. You’d better not try this unless the several conditions are met: 1. Your services have few deadlocks 2. Your services have the requirement to disable the deadlock detection. The improving effect has been fully tested and confirmed. 3. If you have to enable this feature, only requests with lock wait timeouts will be rolled back when a deadlock occurs. So please remember to adjust innodb_lock_wait_timeout to a smaller value. |
|
|
