Python wechat ordering applet course video
https://edu.csdn.net/course/detail/36074
Python practical quantitative transaction financial management system
https://edu.csdn.net/course/detail/35475
How to troubleshoot and solve the MySQL deadlock problem in the production environment? This paper will simulate the real deadlock scenario for troubleshooting, and finally summarize how to avoid the deadlock in the actual development.
1, Prepare relevant data and environment
The current data version is 8.0.22
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.22 | +-----------+ 1 row in set (0.00 sec)
Database isolation level (default isolation level)
mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ 1 row in set (0.00 sec)
Auto submit close
mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec)
Table structure
This age is a non unique index, which is very important for the whole case below.
-- id Is a self incrementing primary key, age Is a non unique index, name General field CREATE TABLE `user` ( `id` int NOT NULL AUTO_INCREMENT COMMENT 'Primary key', `age` int DEFAULT NULL COMMENT 'Age', `name` varchar(255) DEFAULT NULL COMMENT 'full name', PRIMARY KEY (`id`), KEY `idx\_age` (`age`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='User information table';
Insert two pieces of data into the table temporarily
Two, simulate real deadlock cases.
Start two terminals to simulate transaction concurrency. The execution sequence and experimental phenomena are as follows:
1) Transaction A executes the update operation, and the update is successful
mysql> update user set name = 'wangwu' where age= 20; Query OK, 1 row affected (0.00 sec)
- Transaction B executes the update operation, and the update is successful
mysql> update user set name = 'zhaoliu' where age= 10; Query OK, 1 row affected (0.00 sec)
3) Transaction A performs an insert operation and is blocked~
mysql> insert into user values (null, 15, "tianqi");
4) Transaction B executes the insertion operation, and the insertion succeeds. At the same time, the insertion of transaction A changes from blocking to deadlock error.
insert into user values (null, 30, "wangba"); Query OK, 1 row affected (0.00 sec)
The insert operation of transaction A becomes an error.
After the above four steps, we commit transaction A and transaction B respectively.
mysql> commit; Query OK, 0 rows affected (0.00 sec)
Let's look at the data of tables in the database.
We found that all operations of transaction B were successful in the end, while the operations of transaction A were rolled back because of an error. Therefore, all operations of transaction A failed.
Since it is A deadlock, why rollback transaction A instead of transaction B is random or there is A mechanism?
We can understand that deadlock is a database transaction protection mechanism. Once a deadlock occurs, MySQL will choose a relatively small transaction (with less undo) for rollback.
3, View and analyze deadlock log
You can use show engine innodb status to view the latest deadlock log. After execution, the deadlock log is as follows (only some logs are displayed):
LATEST DETECTED DEADLOCK ------------------------ 2021-12-24 06:02:52 0x7ff7074f8700 *** (1) TRANSACTION: TRANSACTION 2554368, ACTIVE 22 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 INSERT INTO user VALUES (NULL, 15, "tianqi") *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall\_goods`.`user` trx id 2554368 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall\_goods`.`user` trx id 2554368 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) TRANSACTION: TRANSACTION 2554369, ACTIVE 14 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 INSERT INTO user VALUES (NULL, 30, "wangba") *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall\_goods`.`user` trx id 2554369 lock_mode X locks gap before rec Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall\_goods`.`user` trx id 2554369 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** WE ROLL BACK TRANSACTION (1)
1. Transaction A related logs
1) Find keyword TRANSACTION, TRANSACTION 2554368
2) View the executing sql of transaction 1
insert into user values (null, 15, "tianqi")
- View locks held by the current transaction and locks waiting to be released by other transactions
2. Transaction B related logs
1) Keyword TRANSACTION found, TRANSACTION 2554369
2) View the executing sql of transaction 2
insert into user values (null, 30, "wangba")
- View locks held by the current transaction and locks waiting to be released by other transactions
3. Summary
Here are some key log screenshots
Let's draw this picture in another way
1) It is obvious from the figure that both transaction 1 and transaction 2 are waiting for each other's lock to be released, resulting in deadlock. And finally transaction 1 rolled back.
2) This log provides more important information, that is, we can see which two sql have been waiting for the lock release of other transactions to cause deadlock, know which index caused the deadlock, and know which transaction is the final one
Was rolled back.
3) If the above information can't help you locate and solve the problem, you can ask the database DB for a detailed binlog log to analyze all the sql executed by the two transactions during this period.
4, Summarize and analyze the causes of deadlock in the case
This analysis requires an understanding of various locking mechanisms in MySQL. If you are not clear, you can see the two articles I wrote before. After reading them, you will know what I wrote below.
- This article explains the locking mechanism of MySQL in detail
- Small case demonstration of MySQL record lock, gap lock and temporary key lock
1. What locks are generated by the SQL of transaction A
1) What locks are generated by the update statement of transaction A
Let's look at it first
update user set name = 'wangwu' where age= 20;
Record lock
Because it is an equivalent query, a record lock will be requested for all data satisfying age=20.
Clearance lock
Because this is an equivalent query with a non unique index, gap locks will also be generated (if it is an equivalent query with a unique index, there will be no gap locks, only record locks), because there are only two records here
Therefore, the left side is (10,20). Because there is no record on the right side, the range of requesting gap lock is (20, + ∞), and the sum is (10,20) + (20, + ∞).
Next key lock
Next key lock = record lock + gap lock, so the Update statement has a (10, + ∞) next key lock
What locks are generated by the install statement of transaction A
INSERT INTO user VALUES (NULL, 15, "tianqi");
Clearance lock
Because age 15 (between 10 and 20), a gap lock of (10,20) needs to be requested
Insert intent
Insert intent lock is A gap lock set before inserting A row of records. This lock releases the signal of an insertion mode, that is, transaction A needs to insert intent lock (10,20). This insert lock is used to improve the insertion efficiency. In the analysis
In case of deadlock, we don't need to care about it, just care about the gap lock on it.
2. What locks are generated by the SQL of transaction B
What locks are generated by the update statement of transaction B
Let's look at it first
update user set name = 'zhaoliu' where age= 10
Record lock
Because it is an equivalent query, a record lock will be requested for all data satisfying age=10.
Clearance lock
Because there is no record on the left and there is a record with age=20 on the right, the range of gap lock is (- ∞, 10), and the right is (10,20). Together, it is (- ∞, 10) + (10,20).
Next key lock
Next key lock = record lock + gap lock, so the Update statement has a (- ∞, 20) next key lock
What locks are generated by the install statement of transaction A
INSERT INTO user VALUES (NULL, 30, "wangba")
Clearance lock
- Because age 30(20 on the left and no value on the right), it is necessary to request a gap lock with (20, + ∞)
Insert intent
- (20,+∞)
After analyzing the locks, let's look at what causes the deadlock?
In this way, the cause of the whole deadlock is clear, but two more points are added here
1) Although MySQL's gap lock has the principle of opening on the left and closing on the right, this is not completely correct, because it may be closed on the left and open on the right, or open on the left and open on the right. It will be related to the position where you insert the primary key value. For details, see what I wrote earlier
There are complete examples in an article Small case demonstration of MySQL record lock, gap lock and temporary key lock . Therefore, the gap lock here is written in the range of left opening and right opening. The critical point may be a little fuzzy, but it does not affect the analysis of the deadlock problem of this case.
2) Through the update statements of transaction A and transaction B, we can find that they both hold the range of gap lock (10,20), indicating that the range of gap lock can be compatible with each other, which means that as long as your 10 is not in my (10, + ∞) gap lock
Within the range, a partially coincident gap lock can be generated, that is, here (10,20).
5, How to avoid deadlock in practical development
Generally speaking, deadlock rarely occurs in actual development, especially when the amount of business concurrency is not very large. In the case of large concurrency, there may be occasional deadlocks.
However, in my actual development, I have encountered the situation that a request for an interface has a 100% probability of deadlock.
The scene was actually very simple. In a business code, Dubbo calls other interface services. There are two transactions. As a result, when each transaction is submitted, it needs to wait for the other party's lock to be released, resulting in deadlock timeout every time.
This is actually a deadlock caused by non-standard code. Here is also a summary of how to avoid deadlock as much as possible.
1) When different applications access the same group of tables, they should try to agree to access each table in the same order. For a table, try to access the rows in a fixed order. This is really important. It can significantly reduce the occurrence of deadlocks.
For example, if there are two tables a and b, transaction 1 starts a and then b, and transaction 2 starts b and then a, there may be mutual waiting and deadlock. If both transaction 1 and transaction 2 start with a and then b, transaction 1 will get the lock of a first, and transaction 2 will get the lock of a again. If
In case of lock conflict, it will wait for transaction 1 to release the lock. Naturally, transaction 2 will not get the lock of b, so it will not block transaction 1 from getting the lock of b, so as to avoid deadlock.
2) When updating the primary key equivalent, try to query the database to see if there is any data that meets the conditions. If it does not exist, it does not need to be updated, and only if it exists. Why do you do this? Because if you update a piece of data that does not exist in the database,
It will also produce clearance lock.
For example, if there are only data with id=1 and id=5 in the table, if you update the sql with id=3, because this record does not exist in the table, a (1,5) gap lock will be generated, but in fact, this lock is redundant because you update one
Data that does not exist has no meaning.
3) Try to use the primary key to update the data, because the primary key is the only index. When the data can be found in the equivalent query, only row locks will be generated, and no gap locks will be generated. In this way, the probability of deadlock will be reduced. Of course, if it is a range query,
It will also produce clearance lock.
4) Avoid long transactions, and the probability of sending lock conflicts for small transactions is also small. This should be well understood.
5) When unreal reading and non repeatability are allowed, the isolation level of RC shall be used as much as possible to avoid deadlock caused by gap lock, because deadlock is often related to gap lock, and the existence of gap lock itself is also based on RR isolation level
A measure to solve illusory reading.
thank
This article provides a good idea for myself, and this article is basically written according to this idea
Teach you how to analyze MySQL deadlock
Statement: if the official account needs to be reprinted, the head of the article must be informed that it is transferred to the official account: the backend meta universe. At the same time, you can also ask me for markdown originals and original pictures. Reprint is prohibited under other circumstances!