preface

This article first introduces all kinds of MySQL lock types and lock mechanism, then through a case with you to understand how to analyze deadlock problems. Finally, there are several ways to prevent deadlocks.

The article is long, so IT is suggested to mark first and read it later. In addition, this article is highly practical. Students who do not have a database can obtain the test database on the official account [Play code] and conduct experiments by themselves.

Here is the table structure of the sample table

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL COMMENT 'name',
  `age` int NOT NULL COMMENT 'age',
  `score_rank` int NOT NULL COMMENT '排名'.PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_idx_score_rank` (`score_rank`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Copy the code

Table data:

The storage engine for analysis is InnoDB and the transaction isolation level is repeatable by default. The database version is 8.0.25.

Introduction to various locks

Shared and exclusive locks

A shared lock is also called a read lock and is referred to as an S lock in a database. Multiple transactions can add a shared lock to the same data, that is, shared locks are not mutually exclusive.

Exclusive lock, also known as write lock, is represented as X lock in the database. Only one transaction can add an exclusive lock to one data at a time. That is, exclusive locks are mutually exclusive with all other X and S locks. For data to which an exclusive lock has been added, only the transaction holding the lock can read and write the lock. At the same time, before a transaction can modify data, exclusive locks must be added.

It is important to note that it is not the case that after an exclusive lock is placed on data, other transactions cannot read it. Database query data is classified into snapshot read data and current read data.

Before we move on, we need to know two things:

  • When a transaction is started, a snapshot is taken of the current transaction status of the database, which can be simply defined as up_limit_id, and the “list of all transaction ids currently executing” – uncommit_list is saved.

  • For each row in the database, multiple data versions are recorded through the undo log implementation, and each version has a transaction ID corresponding to the change.

Snapshot reads are unlocked queries that can only read the committed version of the data when the transaction starts. During the query, the system obtains the specified data from the index tree and then checks whether the current version of the data is visible. If not, the system obtains the previous version of the data and then checks the visibility.

You can see that while data is being modified by a transaction with an exclusive lock, other transactions can still read it without locking. At the same time, after the transaction in session2 is committed, statement 4 in session1 cannot read the latest version of data using snapshot reads.

If you want to query the latest version of data, use current read. Read lock (select… Lock in share mode), also can add write lock (select… For update).

Tx1 statement 3, the use of the current read way query, to obtain tx2 updated the latest version of the data, because statement 3 is read lock, so TX3 can also add read lock read the latest data.

select … Lock in share mode and select… For update the type of the lock is different, and sometimes the range of the lock is different:

  • Select id from T_user where age = 20 lock in share mode: select id from t_user where age = 20 lock in share mode: select id from t_user where age = 20 lock in share mode: select id from t_user where age = 20 lock in share mode. So only the normal index IDx_age is locked.

  • Select id from T_user where age = 20 for update; select id from t_user where age = 20 for update; select id from t_user where age = 20 for update;

Table level locks and row level locks

According to the granularity of locking, it can be divided into table level locking and row level locking:

  • Table lock Locks the entire table. The locking granularity is large, lock conflicts are easy to occur, and concurrency performance is low
  • Row lock locks a row of data. The granularity of row lock is small and the probability of lock conflict is small. High concurrency performance.

Table locks and row locks can be divided into table shared locks/table mutexes and row shared locks/row mutexes. See the shared and Exclusive locks section for compatibility.

Intent locks

Innodb supports multi-granularity locking as described in the previous section. Shared and exclusive locks are mutually exclusive. When a transaction wants to add a table-level shared lock to a table, it needs to ensure that:

  • No other transaction currently holds an exclusive lock on the table.
  • No other transaction currently holds an exclusive lock for any row in the table.

To satisfy the second requirement, it would be inefficient to iterate over the lock status of each row in the check table. So mysql introduced intent locks.

First, intent locks are table level locks. Intent locks also have shared intent locks (IS) and exclusive intent locks (IX). In particular, shared intent lock (IS) and exclusive intent lock (IX) are compatible with each other rather than mutually exclusive.

At the same time, intent locks are compatible with row-level locks.

Intent locks are mainly used on table locks.

Intent locks are maintained by InnoDB itself and cannot be manually manipulated by users. When a transaction requires a shared (exclusive) lock for a row, a shared (exclusive) intent lock is automatically added to the table.

When a transaction needs to add a table lock, such as a table-level shared lock, the check condition is changed to:

  • Check that no other transaction currently holds the exclusive lock on the table.
  • Check that no other transaction currently holds the table for intentional exclusive locks.

If other transactions hold intentional exclusive locks, then some rows of the table are locked without traversing each row of the check table. Efficiency has been significantly improved.

Gap Locks

Consider the result of the following sequence of operations:

Let’s review select… For update: lock all rows that meet the condition, and then query all rows that meet the condition for the latest version.

Tx2 insert into tx2; tx1 insert into tx2;

  1. [{id: 1, name: ‘jay’, age: 20, score_rank: 1}, {id:4, name: ‘ning’, age: 20, score_rank: 4}] Why is the current reading different from the previous one? Can give a person a kind of locked feeling, this is called unreal reading.

  2. Return [{id: 1, name: ‘jay’, age: 20, score_rank: 1}], which is the same as statement 1. But it violates the semantics of the current read: reading the latest version of the current data.

So statement 2 is not allowed to insert. But what lock should be added to statement 1 to block statement 2? Insert age=20 (age=20); insert age=20 (age=20) Locking the entire table directly reduces the concurrency of the database.

For this reason, mysql introduced gap lock. As the name implies, the lock object of gap lock is the gap between data. T_user table data, for example, data on the primary key index of clearance (- up, 1), (1, 2), (2, 3), (3, + up); Interval on the normal index idx_age (- up, 15), (15, 20), (20, 30), and (30 + up); The only index uniq_idx_score_rank have clearance (- up, 1), (1, 2), (2, 3), (3, + up).

The gap lock and the row lock on the right side of the interval form a next-key lock. Namely, (-∞, 1], (1, 2], (2, 3], (3, +∞]. In most cases, the basic unit of locking is next-key lock.

In rare cases, next-key locks degenerate into row locks or gap locks. For details, see section next-key locking rules.

Gap locks are non-conflicting, that is, multiple transactions can add a gap lock to the same gap at the same time. This is because multiple transactions add a gap lock to protect the gap from inserting new values.

Combined with gap locking, we analyze the above operation results. Statement 1 adds a gap lock (15, 20) and a gap lock (20, 30) in addition to the row lock (age=20).

Why these two locks are added can be seen in the next key locking rule section.

Tx2 insert statement 2, found that the place to insert is protected by a gap lock, resulting in insert block.

Next-key Specifies the lock rule

Before explaining the locking rules, let’s learn how to check the locking status of the database. The table information_schema. INNODB_LOCKS can be used to query the Mysql database. 5.8 The value can be viewed in table Performance_schema. datA_LOCKS. The fields in both tables are roughly the same, and the following analysis is performed using performance_schema.datA_LOCKS, version 5.8.

Performance_schema. datA_locks main columns:

  • ENGINE_LOCK_ID: specifies the ID of the lock to be held or requested
  • ENGINE_TRANSACTION_ID: specifies the ID of the transaction requesting the lock
  • THREAD_ID: transaction thread ID, associatedperformance_schema.threadsYou can get thread details
  • OBJECT_SCHEMA: specifies the schema name of the lock table
  • OBJECT_NAME: specifies the table name for the lock
  • INDEX_NAME: indicates the name of the index corresponding to the lock
  • LOCK_TYPE: the corresponding lock type. For InnoDB, it can be TABLE or RECORD.
  • LOCK_MODE: lock mode, key!
    • S: LOCK_TYPE=TABLE: indicates a table-level shared lock. LOCK_TYPE=RECORD: next-key lock consisting of row-level shared lock and gap lock
    • S, GAP: GAP lock
    • S, REC_NOT_GAP: row-level shared lock
    • IS: Intended shared lock
    • X: LOCK_TYPE=TABLE: indicates the table-level exclusive lock. LOCK_TYPE=RECORD: next-key lock consisting of row level exclusive lock and gap lock
    • X, GAP: GAP lock
    • X, REC_NOT_GAP: row level exclusive lock
    • X,GAP,INSERT_INTENTION: [# insert intention lock]
    • IX: Intent exclusive lock
  • LOCK_STATUS: lock status, GRANTED: obtained. WAITING: WAITING to be obtained
  • LOCK_DATA: data corresponding to the lock. If the lock is a primary key index, the value is the primary key value of the lock. The locked non-primary key index is [index value, corresponding to primary key value].

The interval, index entries, and data records accessed during the query are locked

I’m sure you remember select… Lock in share mode and select… For UPDATE difference in lock range due to overwriting index. Similarly, if select… Lock in share mode Does not need to go back to the table to check the primary key index, and does not add a gap lock on the primary key.

A key case is when a full table scan is required to lock all gaps and all rows on the primary key index if the query does not hit any indexes. Add table level lock in disguise. Concurrency is severely affected.

Performance_schema. Data_locks lock (-∞, 1], (1, 2], (2, 3], (3, +∞]); update (performance_schema.

Equivalent query, common index query lock analysis

Select * from T_user where age = 20 for update

As you can see, we start with a normal index, iterate from age = 20, and continue to the right until we find the first row that does not meet the criteria. A next-key lock is added to a row that meets the condition, that is, (15, 20]; For the last traversed line that does not meet the condition, it degenerates into a gap lock (20, 30).

If an index that meets the condition is found on a common index, the system queries the upper table of the primary key index. Because the primary key to be queried is determined, a gap lock is not required on the primary key, but the corresponding row lock is added.

Why do I need to add a gap lock to the right neighbor on a normal index?

<15, 3> -> <20, 1> -> <30, 2> if, now insert a row {id: 4, name: ‘zhangsan’, age: 20, score_rank: 5}, idx_age index order will be <15, 3> -> <20, 1> -> <20, 4> -> <30, 2>, that is, the new index is inserted between the original gap (20, 30).

Equivalent query, unique index query lock analysis

Select * from T_user where score_rank = 1 for update; Is locked.

Uniq_idx_name = uniq_idx_name = uniq_IDx_name = uniq_IDx_name The reason is that unique indexes are unique, and there will be no indexes with the same value. Therefore, equivalent queries do not need to worry about phantom reads.

Range query, common index lock analysis

Select * from T_user where age > 13 and age <= 20 for update

(-∞, 15], (15, 20], (20,30]); (20,30]; (20,30);

Age > 13 and… , but since there is no gap (13, 15) on the index at this time, the gap (-∞,15) will be directly locked, which will block other transaction inserts age <= 13 records.

Range query, unique index lock analysis

Select * from T_user where score_rank > 1 and score_rank < 3; Locking condition.

Analysis: Omitted.

Next-key lock is the result of a gap lock and a row lock combined

Next-key lock Two steps, first add gap lock, then add row lock. Let’s use an example to illustrate this conclusion.

Analyze the execution of the above two transactions (just look at the plain indexidx_ageIs locked) :

  1. Tx1 statement 1 adds next-key lock (15, 20) and gap lock (20, 30).

  2. Next key lock (15, 20) and gap lock (20, 30); Next key lock is added in two steps: first, add gap lock (15, 20). Because the row X lock with age=20 is already held by TX1, tx2 will block when acquiring it.

  3. Statement 3 of TX1 can be inserted successfully because TX2 blocks at the step of acquiring the X lock with age=20 and does not hold a gap lock (20, 30).

  4. Insert statement 4: TX2 holds a gap lock (15, 20). At this point, TX1 waits for TX2 to release the gap lock (15, 20); Tx2 waits for tx1 to release the row lock whose age=20. Deadlock detection detects the deadlock and rolls back TX2 directly, so tx1 statement 4 is successfully executed.

The innodb_lock_WAIT_TIMEOUT parameter determines the wait time (default: 50s) when a transaction needs to wait to acquire a lock. If a deadlock occurs, the related transactions are stuck waiting until one of them times out and the other transactions continue. Requests are blocked 50s, which is clearly unacceptable for online services.

Mysql provides deadlock detection for deadlocks, which is enabled by default (innodb_deadlock_detect = on). When a deadlock is detected, the database proactively rolls back one transaction to allow other transactions to continue.

Insert intent lock

Transaction inserts a new row of data. When detecting that there is no gap lock in the inserted target interval, insert intent lock is first added to the interval. Insert intention lock is essentially a gap lock. The object of the lock is also a data/index gap.

The purpose of inserting an intent lock is to indicate the intention to insert new data into an interval. Contrary to the purpose of the gap lock to protect the gap from inserting new values, the insert intent lock and the gap lock are mutually exclusive.

To improve insert concurrency, insert intent locks on the same interval are not mutually exclusive. For example, two transactions can concurrently insert new data into the (20, 30) interval of the IDX_age index without blocking each other.

Why do I need to insert an intent lock instead of checking that there is no gap lock in the target interval? This problem didn’t find the answer at the official documentation, nor read the source code, just say the personal view: “check for clearance lock” and “insert” not atomicity, in order to avoid any gaps in a check and insert lock lock with new clearance between data to add, add insert intent locks shows that are on the verge of a new data into that range, block other transactions to acquire new clearance lock.

MDL lock

The lock objects described above are data itself or data gaps. MDL(Meta Data Lock) is also a type of lock added to metadata. MDL also has read and write locks.

MDL does not require display use and is added automatically during table operations. When the table to add, delete, change and check, will automatically add MDL read lock; MDL write locks are automatically added when adding or subtracting columns to a table.

Read locks are not mutually exclusive, meaning that multiple threads can add, delete, modify, and check a table at the same time. Write lock exclusiveness. The MDL write lock can be acquired only after all other MDL locks are released. Once a write lock is acquired, no other thread can acquire the MDL read lock or write lock until the write lock is released. In other words, changing the structure of a table blocks operations on the table by other threads.

MDL is for data consistency. Mysql > ALTER TABLE T DELETE COLUMN ‘col_1’; mysql > ALTER TABLE T DELETE COLUMN ‘col_1’; mysql > ALTER TABLE T DELETE COLUMN ‘col_1’; mysql > ALTER TABLE T DELETE COLUMN ‘col_1’;

Deadlock field investigation and analysis

When a deadlock occurs, troubleshooting can be done in two steps: analyze the deadlock log -> verify the guess.

Analyze deadlock logs

The LATEST DETECTED DEADLOCK section of the output is analyzed primarily with the show Engine Innodb status command, which records the relevant transaction blocking statements and the wait/hold for the lock in the last DEADLOCK of the database.

------------------------ LATEST DETECTED DEADLOCK ------------------------ 1. 2021-06-18 09:13:45 140135015667456 2. ***  (1) TRANSACTION: 3. TRANSACTION 4965, ACTIVE 135 sec starting index read 4. mysql tables in use 1, locked 1 5. LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s) 6. MySQL thread id 2428, OS thread handle 140134872839936, Update T_user set score_rank = 10 where ID = 2 8. *** (1) HOLDS THE LOCK(S): 9. RECORD LOCKS space id 11 page no 5 n bits 72 index idx_age of table `test`.`t_user` trx id 4965 lock mode S 10. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 11: 0: len 8; hex 73757072656d756d; asc supremum;; 12. Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 13. 0: len 4; hex 8000001e; asc ;; 14. 1: len 4; hex 80000002; asc ;; 15. *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 16. RECORD LOCKS space id 11 page no 4 n bits 80 index PRIMARY of table `test`.`t_user` trx id 4965 lock_mode X locks rec but not gap waiting 17. Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 128 18. 0: len 4; hex 80000002; asc ;; 19. 1: len 6; hex 000000001361; asc a;; 20. 2: len 7; hex 020000023202a0; asc 2 ;; 21. 3: len 4; hex 746f6e79; asc tony;; 22. 4: len 4; hex 8000001e; asc ;; 23. 5: len 4; hex 80000002; asc ;; 24. *** (2) TRANSACTION: 25. TRANSACTION 4966, ACTIVE 177 sec inserting 26. mysql tables in use 1, locked 1 27. LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 28. MySQL thread id 2427, OS thread handle 140134885521152, Query ID 24617 111.206.145.52 root update 29. Insert into T_user (' name ', 'age ',' score_rank ') values ('zhangsan', 25, 4) 30. *** (2) HOLDS THE LOCK(S): 31. RECORD LOCKS space id 11 page no 4 n bits 80 index PRIMARY of table `test`.`t_user` trx id 4966 lock mode S locks rec but not gap 32. Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 128 33. 0: len 4; hex 80000002; asc ;; 34. 1: len 6; hex 000000001361; asc a;; 35. 2: len 7; hex 020000023202a0; asc 2 ;; 36. 3: len 4; hex 746f6e79; asc tony;; 37. 4: len 4; hex 8000001e; asc ;; 38. 5: len 4; hex 80000002; asc ;; 39. *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 40. RECORD LOCKS space id 11 page no 5 n bits 72 index idx_age of table `test`.`t_user` trx id 4966 lock_mode X locks gap before rec insert intention waiting 41. Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 42. 0: len 4; hex 8000001e; asc ;; 43. 1: len 4; hex 80000002; asc ;; 44. *** WE ROLL BACK TRANSACTION (2)Copy the code

1: records the deadlock occurrence time. This timestamp can be matched with the project error log.

3&25: Record the transaction ID and transaction duration

5&27: N row lock(s) indicates the number of locks held by the transaction (note not only row locks but also table level intent locks), and undo log entries N indicates the number of Undo logs in the transaction, which can be interpreted as the number of insert/delete/update statements executed by the transaction. The complexity of the transaction can be inferred by the number of undo logs, which helps us locate the transaction code in the project.

6 & 28: Performance_schema. processList (processList stores a list of current unbroken connections, So you might not find the thread_ID of a deadlock transaction); 111.206.145.52 and root are the client IP address and user name of the thread respectively. If each project uses a different user name, you can quickly locate the services involved in deadlocks.

7 & 29: statements where the transaction is blocked.

8-23: Lock information held and awaited by transaction 1.

  • 9: index idx_age of table test.t_user: holds the lock of index IDx_age on table test.t_user. Lock mode S Indicates that the next-key lock is held and the row lock is shared.

  • 10-11: next-key lock for the first idx_age index held.

  • 12-14: Next-key lock for the second IDx_age index held.

  • Table test.t_user (index PRIMARY of table test.t_user); Lock_mode X locks REc but not GAP indicates waiting for row-level exclusive locks.

  • 17-23: waiting row lock information.

30-43: abbreviated, same as 8-23

44: This deadlock, the database rollback is the transaction (2).

The format and content of data representation of 11 & 13-14 & 18-23 & 33-38 & 42-43 locks are described below.

Each line consists of three parts. N: len m Records the length information. Hex XXXXXXX records data in hexadecimal format. When the lock field type is numeric, omit the highest digit and convert it to “30”, for example, “HEX 8000001E”. Remove the highest digit “8” and convert it to “10”. The last section asC…. , the string value is displayed only when the field type of the lock is a string, or “ASC supremum” is displayed when the next-key lock/ gap lock is the rightmost interval.

Locks with primary and non-primary indexes display different content.

Primary key index 18-23 is used as an example:

  • (line 18) Field 0 represents the primary key ID value, and “HEX 80000002” converts to “2” in base 10.

  • (line 19) Field 1 indicates the transaction ID of the row that was last modified. “HEX 000000001361” converts to “4961” in base 10.

  • (line 20) Field 2 is the rollback pointer.

  • (lines 21-23) starting with field 3 are the other columns of the row.

Non-primary key indexes display the same content as LOCK_DATA in Performance_schema.datA_LOCKS. Take 13-14 as an example:

  • (line 13)field 0Represents the index value. “HEX 8000001E” is converted to “30” in base 10 without the highest digit.
  • (14)field 1Indicates the primary key index ID value corresponding to the index. “HEX 80000002” is converted to “2” in base 10 without the highest digit.

Try to get another statement of the transaction

In general, the connection IP, user name used, transaction complexity, lock hold/wait, blocking statements, and service error logs are all that are needed to locate the code location. The complete transaction statement is obtained. Of course, there are other ways we can try to get other statements of the transaction.

performance_schema.events_statements_historyGets the most recently executed statement for the connection

Table Performance_schema. events_statements_history records the latest N statements that are still connected to the database. You can query performance_schema.events_statements_history for the last N connected statements based on the transaction thread ID obtained in show Engine Innodb status. There are two points to note:

  • events_statements_historyIn the tablethread_idIs notperformance_schema.processlistId, requiredperformance_schema.threadsSo the final query statement should be
SELECT * FROM performance_schema.events_statements_history WHERE thread_id = (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = <PROCESSID>)
Copy the code
  • performance_schema.events_statements_history,performance_schema.processlist,performance_schema.threadsAll are just storageThe current connection is not brokenSo the probability of obtaining the complete transaction statement in this way is not high.

View information in binlog

Use mysqlbinlog to view the binlog of the deadlock point in time, you can analyze the complete statement. Of course, binlog only records transactions that have been committed, so there is no way to retrieve statements from rolled back transactions.

Mysqlbinlog-vvv --start-datetime= "2021-06-18 09:13:20" --stop-datatime= "2021-06-18 09:13:22" mysql-bin.0000xxCopy the code

Get guess

After analysis, we found two transactions that could cause deadlocks:

  • tx1:
    • select * from t_user where id = 2 lock in share mode;
    • insert into t_user (name.age.score_rank) values (‘zhangsan’, 25, 4)
  • tx2:
    • select * from t_user where age > 20 lock in share mode;
    • update t_user set score_rank = 10 where id = 2;

Verify the guess

With the possible deadlocked transaction statements in place, we then simulate the transaction execution order for deadlock validation with performance_schema.datA_LOCKS and Performance_schema.datA_LOCK_WAITS observations holding/waiting lock information during execution. Data_locks has been covered before, so let’s now introduce the meaning of the datA_lock_WAITS fields:

  • REQUESTING_ENGINE_LOCK_ID: specifies the ID of the lock to be obtained.

  • REQUESTING_ENGINE_TRANSACTION_ID: specifies the ID of the transaction waiting to obtain the lock.

  • REQUESTING_THREAD_ID: specifies the ID of the thread waiting to obtain the lock.

  • BLOCKING_ENGINE_LOCK_ID: ID of the lock that causes the block, corresponding to the ENGINE_LOCK_ID column in the DATA_LOCKS table.

  • BLOCKING_ENGINE_TRANSACTION_ID: ID of the transaction that caused the blocking.

  • BLOCKING_THREAD_ID: ID of the thread causing the block.

Each line reads as a transaction REQUESTING_ENGINE_TRANSACTION waiting for the transaction BLOCKING_ENGINE_TRANSACTION to release the BLOCKING_ENGINE_LOCK. To get the REQUESTING_ENGINE_LOCK lock.

Here are the concurrent deadlock scenarios we suspect:

Time t2:

Tx1 execute statement 1, hold lock:

  1. tablet_userShared intent lock (IS).
  2. Primary key row-level shared lock (S, REC_NOT_GAP), corresponding row ID = 2;

There is no lock wait, so the datA_lock_WAITS is null.

Time t3:

Tx2 execute statement 2, hold lock:

  1. tablet_userShared intent lock (IS).
  2. The indexidx_ageNext-key lock: (20, 30) where age = 30 The corresponding row lock is a shared lock.
  3. The indexidx_ageNext-key lock: (30, +∞].
  4. Primary key row-level shared lock (S, REC_NOT_GAP), corresponding row ID = 2;

There is no lock wait, so the datA_lock_WAITS is null.

Time t4:

Tx1 executes statement 3 to add an exclusive intent lock (IX) for table T_USER. Wait to acquire the insert intent lock for the interval (20, 30) on index IDx_age.

Can be found from data_lock_waits tx1, waiting for tx2 id = 140135469928688:11:5:4:140135387707184 of the lock is released, Backtracking datA_LOCKS reveals that the next key lock (20, 30) tx2 added on index idx_age is causing the block.

Moment t5:

The row lock on primary key (id = 2) is already held by TX1 at time t2. Tx1 and TX2 block each other, resulting in a deadlock. The database rolls back TX2, and tx1 statement 3 has to be executed.

At this point, our previous guess is verified.

Deadlock prevention

Deadlocks are mostly the result of bad posture in development. Here are a few tips to avoid deadlocks:

  1. Try to avoid big things. The life cycle of a lock is not released until the transaction is committed. Therefore, the larger the transaction, the more locks it holds, and the more likely it is to cause deadlocks.

  2. Design indexes properly. Higher differentiation precedes the federated index, allowing the query to locate to fewer rows through the index, reducing the scope of locking. Counter example: “Update… Select * from table where name = ‘XXX’ where name = ‘XXX ‘”;

  3. Unified execution sequence. Not only must different tables be locked in the same order, but the same table must also be locked. And it’s easier to ignore.

_, err = eng.Transaction(func(session *xorm.Session) (ret interface{}, err error) {
		userIDScoreRankMap := map[int]int64{
			1: 1.2: 2,}for userID, scoreRank := range userIDScoreRankMap {
			_, err = session.Table(new(TUser)).ID(userID).Update(map[string]interface{} {"score_rank": scoreRank})
			iferr ! =nil {
				return nil, err
			}
		}
		return
	})
Copy the code

UserIDScoreRankMap is a map structure. When a map is circulated, the order of its keys is uncertain, which may lead to deadlocks.

  1. Hotspot data should be placed behind transactions to reduce lock time, reduce lock conflicts, and improve concurrency.

Write in the last

If you like this article, welcome to pay attention to the public account “will play code”, focus on plain English to share practical technology

Welfare of public account

Mysql > get free test database!!

Reply [PDF] get continuous update massive learning materials!!