GitHub 1.8K Star Java engineers become god’s way, not to know?
GitHub 1.8K Star Java engineers become god’s way, really not to know?
GitHub 1.8K Star Java engineers become god’s way, really sure not to check out?
The phenomenon of
One night, when my colleague was publishing, there were a large number of online alarms, many of which were about database deadlocks, and the warning information was as follows:
{"errorCode":"SYSTEM_ERROR","errorMsg":"nested exception is org.apache.ibatis.exceptions.PersistenceException:
Error updating database. Cause: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL]
Deadlock found when trying to get lock;
The error occurred while setting parameters\n### SQL:
update fund_transfer_stream set gmt_modified=now(),state = ? where fund_transfer_order_no = ? and seller_id = ? and state = 'NEW'
Copy the codeThrough the alarm, we can basically locate the database and database table of the birth and death lock. Let’s start with the database information involved in this case.
Background to the case
Our database is Mysql 5.7, the engine is InnoDB, and the transaction isolation level is read-commited.
Database version query method:
SELECT version();
Copy the codeEngine query method:
show create table fund_transfer_stream;
Copy the codeThe table builder will display storage ENGINE information, such as ENGINE=InnoDB
Transaction isolation level query method:
select @@tx_isolation;
Copy the codeTransaction isolation level setting method (only for current Session) :
set session transaction isolation level read committed;
Copy the codePS: Note that if the database is separate, the above SQL statements need to be executed in a single library, not in a logical library.
Table structure and index condition with deadlock (some irrelevant fields and indexes are hidden) :
CREATE TABLE 'fund_transfer_stream' (' id 'bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', 'gmt_create' datetime NOT NULL COMMENT 'createtime ',' gmt_modified 'datetime NOT NULL COMMENT' createtime ', 'pay_scene_name' varchar(256) NOT NULL COMMENT '表 示 ', 'pay_scene_version' varchar(256) DEFAULT NULL COMMENT 'Payment scenario version ',' identifier 'varchar(256) NOT NULL COMMENT' unique identifier ', 'seller_id' varchar(64) NOT NULL COMMENT 'varchar ',' state 'varchar(64) DEFAULT NULL COMMENT' status ', 'fund_transfer_order_no' varchar(256) DEFAULT NULL COMMENT 'return status ', PRIMARY KEY (`id`),UNIQUE KEY `uk_scene_identifier` (KEY `idx_seller` (`seller_id`), KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' ';Copy the codeThe database has three indexes, one clustered index (primary key index) and two non-clustered index (non-primary key index).
Cluster index:
PRIMARY KEY (`id`)
Copy the codeNon-clustered index:
KEY `idx_seller` (`seller_id`),
KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))
Copy the codeIn fact, idx_seller_transNo overwrites IDx_Seller. Due to historical reasons, the table is divided by seller_ID, so there is idx_SELLER first and then idx_seller_transNo
Deadlock log
When a database deadlock occurs, you can run the following command to obtain deadlock logs:
show engine innodb status
Copy the codeIf a deadlock occurs, check the deadlock log immediately and obtain the deadlock log content as follows:
Transactions DEADLOCK detected, Detailed information. 2019-03-19T21:44:23.516263+08:00 5877341 [Note] InnoDB: *** (1) TRANSACTION: TRANSACTION 173268495, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 304 lock struct(s), heap size 41168, 6 row lock(s), undo log entries 1 MySQL thread id 5877358, OS thread handle 47356539049728, Query ID 557970181 11.183.244.150 fin_instant_app updating 'fund_transfer_stream' set 'gmt_modified' = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857'), THE 2019-03-19 T21: arouse yourself. 516321 + 5877341 [Note] 08:00 InnoDB: * * * (1) doesn THE LOCK (S) : RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap Record lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; Info bits 0 2019-03-19T21:44:23.516565+08:00 5877341 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap waiting Record lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; Info bits 0 2019-03-19T21:44:23.517793+08:00 5877341 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 173268500, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 81 mysql tables in use 1, locked 1 302 lock struct(s), heap size 41168, 2 row lock(s), undo log entries 1 MySQL thread id 5877341, OS thread handle 47362313119488, Query ID 557970189 11.131.81.107 fin_instant_app updating 'fund_transfer_stream_0056' set 'gmt_modified' = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256'), THE 2019-03-19 T21: arouse yourself. 517855 + 5877341 [Note] 08:00 InnoDB: * * * (2) doesn THE LOCK (S) : RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gap Record lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; Info bits 0 2019-03-19T21:44:23.519053+08:00 5877341 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gap waiting Record lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; Info bits 0 2019-03-19T21:44:23.519297+08:00 5877341 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)Copy the codeA brief interpretation of the deadlock log yields the following information:
SQL > create deadlock; SQL > create deadlock;
update `fund_transfer_stream_0056`
set `gmt_modified` = NOW(), `state` = 'PROCESSING'
where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256'))
Copy the codeand
update `fund_transfer_stream_0056`
set `gmt_modified` = NOW(), `state` = 'PROCESSING'
where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857'))
Copy the codeSQL > alter table idx_seller_transNo; SQL > alter table idx_seller_transNo;
Transaction 2, holding PRIMARY lock, waiting to acquire idx_seller_transNo lock.
A deadlock occurred due to a circular wait between transaction 1 and transaction 2.
At the same time, the cloud DBA can also see the resource holding and waiting situation when a deadlock occurs as shown in the following figure, which is basically consistent with the information obtained in the deadlock log.
The current locks held by transaction 1 and transaction 2 are lock_mode X locks rec but not gap
The two transactions add X lock to the record, No Gap lock, that is, lock the corresponding row record, and add Gap lock.
X lock: Exclusive lock, also known as write lock. If transaction T locks data object A with X, transaction T can either read A or modify A, and no other transaction can lock A again until T releases the lock on A. This ensures that no other transaction can read or modify A until T releases the lock on A.
Corresponding to this is S lock: shared lock, also known as read lock. If transaction T locks data object A with S lock, transaction T can read A but cannot modify A. Other transactions can only add S lock to A, but cannot add X lock, until T releases S lock on A. This guarantees that other transactions can read A, but cannot make any changes to A until T releases the S lock on A.
Gap Lock: A Gap Lock that locks a range but excludes the record itself. The purpose of the GAP lock is to prevent the illusion of two current reads of the same transaction.
Next-key Lock: 1+2 locks a range and locks the record itself. For the query of rows, this method is used, the main purpose is to solve the illusion problem.
See: www.cnblogs.com/zhoujinyi/p… , dev.mysql.com/doc/refman/…
Troubleshoot problems
Based on what we know about the database and deadlock logs, we can make some simple decisions.
First of all, this deadlock must not be related to Gap Lock and next-key Lock. Because our database isolation level is RC (read-commited), this isolation level does not add Gap locks. This is also mentioned in the previous deadlock log.
Then, it’s time to look at the code and see how transactions actually work in our code. The core code and SQL are as follows:
@Transactional(rollbackFor = Exception.class)
public int doProcessing(String sellerId, Long id, String fundTransferOrderNo) {
fundTreansferStreamDAO.updateFundStreamId(sellerId, id, fundTransferOrderNo);
return fundTreansferStreamDAO.updateStatus(sellerId, fundTransferOrderNo, FundTransferStreamState.PROCESSING.name());
}
Copy the codeThe purpose of this code is to modify two different fields of the same record successively, updateFundStreamId SQL:
update fund_transfer_stream
set gmt_modified=now(),fund_transfer_order_no = #{fundTransferOrderNo}
where id = #{id} and seller_id = #{sellerId}
Copy the codeUpdateStatus SQL:
update fund_transfer_stream
set gmt_modified=now(),state = #{state}
where fund_transfer_order_no = #{fundTransferOrderNo} and seller_id = #{sellerId}
and state = 'NEW'
Copy the codeSQL > Update (); Update (); Update ();
UpdateFundStreamId is executed using the PRIMARY index.
UpdateStatus is executed using the idx_seller_transNo index.
By executing the plan, we found that updateStatus actually had two indexes available, and that the idx_seller_transNo index was actually used for the execution. This is because the MySQL query optimizer is cost-based. Therefore, in the query process, the most important part is to calculate the cost of the query according to the SQL statement of the query, according to a variety of indexes, so as to select the optimal index method to generate the query plan.
Our query execution plan is made after the deadlock occurs, and the execution plan of the query after the deadlock occurs is not necessarily the same as the index usage at the time of the deadlock. However, with the deadlock log, we can also locate the index used in the execution of the above two SQL statements. The PRIMARY index was used for updateFundStreamId and the idx_seller_transNo index was used for updateStatus.
With this information in hand, we can begin to investigate the causes of deadlocks and the principles behind them. By analyzing the deadlock log and combining it with our code and database construction statements, we found that the main problem was with our idx_seller_transNo index:
KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))
Copy the codeIn the index creation statement, prefix indexes are used. To save index space and improve index efficiency, only the first 20 digits of the fund_transfer_ORDER_NO field are selected as the index values.
Because fund_transfer_ORDER_NO is a normal index, not a unique index. Because in one particular case, two fund_transfer_ORDER_NO’s of the same user have the same first 20 bits, this results in two different records having the same index value (because seller_id and fund_transfer_ORDER_NO (20) are the same).
As the example in this article, two records of deadlock of fund_transfer_order_no field value: 99010015000805619031958363857 and 99010015000805619031957477256 the two is the top 20 of the same.
So why would the same first 20 digits of fund_transfer_ORDER_no cause a deadlock?
Lock principle
Let’s take a look at the principle of MySql database lock, deadlock behind this article and what happened.
We simulate a deadlock scenario on the database in the following order:
As we know, row-level locking in MySQL does not directly lock records, but locks indexes. There are two types of indexes: primary key index and non-primary key index. If a SQL statement operates on a primary key index, MySQL locks the primary key index. If a statement operates on a non-primary key index, MySQL locks the non-primary key index and then the related primary key index.
Leaf nodes indexed by primary keys hold entire rows of data. In InnoDB, primary key indexes are also called clustered indexes.
The contents of leaf nodes of non-primary key indexes are the values of primary keys. In InnoDB, non-primary key indexes are also called non-clustered indexes.
Therefore, the index structure involved in this article’s example (the index is a B+ tree, simplified to a table) is shown below:
Deadlock does not depend on the number of SQL statements in the transaction. Deadlock occurs when two (or more) sessions are locked in an inconsistent order. ** Now let’s look at the sequence in which the two transactions are locked:
The following is a breakdown of the lock condition for each SQL execution:
Transaction 1 (update1) holds lock PRIMARY = 1 — > transaction 2 (update1) holds lock PRIMARY = 2 Lock idx_seller_transNo = (3111095611,99010015000805619031); Transaction 2 attempted to hold the lock idx_seller_transNo = (3111095611,99010015000805619031) failed (deadlock);
SQL > alter table Update where index (s); SQL > alter table Update where index (s); SQL > Update where index (s);
The solution
At this point, we have analyzed the fundamental principles leading to deadlocks and the principles behind them. Then this problem is not difficult to solve.
You can start by changing the index and changing the code (including SQL statements).
Change the index: we only need to change the prefix length of fund_transfer_ORDER_NO in idx_seller_transNo. Let’s say I change it to 50. Deadlock can be avoided.
However, by changing the idx_seller_transNo prefix length, deadlocks can be resolved only if the fund_transfer_ORDER_NO index is used when the UPDATE statement is actually executed. If the MySQL query optimizer, after cost analysis, decides to use the index KEY IDx_Seller (seller_ID), there will still be deadlock issues. The principle is similar to this article.
So, the fundamental solution is to change the code:
* All updates are performed by primary key ID. * Avoid multiple UPDATE statements modifying the same record in the same transaction.Copy the codeSummary and Reflection
During the week after the deadlock occurred, I took time to study it almost every day. The problem was identified early and the modification plan was made, but the mechanism was still unclear.
Have done many kinds of inferences and assumptions, and have been overturned by their own time and again. In the end, you have to test your ideas with practice. So I installed the database in the local, the actual combat to do some testing, and real-time view database lock situation. show engine innodb status ; You can view locks. I finally figured out how.
A few quick thoughts:
Don’t guess when you have a problem!! Reproduce the problem by hand, and then analyze it.
2. Don’t ignore context!! I initially focused only on the deadlock log, ignoring that the transaction in the code actually executed another SQL statement (updateFundStreamId).
3, theoretical knowledge is sufficient, the critical moment may not think up!!
The pits are buried by themselves!!
Dao no art, art can become; Art without tao, stop in tao; Welcome to pay attention to “The Way of Java” public account, together with the way bending, with the way knowledge;
MySQL lock processing analysis
Innodb transaction isolation level
MySql tutorial 45
MySQL row level lock, table level lock, page level lock