concept

First of all, integrate the collected concepts, troubleshooting, problem solving need to understand. Deadlock problems encountered by bloggers are not in the cases listed in the concept, and the situations encountered and solutions will be shown below.

1. Mysql lock level

Said by: www.cnblogs.com/zejin2008/p…

Mysql has three lock levels: page level, table level, and row level.

  • Table lock: low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low.

  • Row-level lock: expensive, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.

  • Page lock: the overhead and lock time are between table lock and row lock. Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average.

2. Mysql transaction isolation level

Said by: zhuanlan.zhihu.com/p/117476959

  • Dirty read: Dirty read refers to read data that has not been committed by another transaction. Uncommitted means that the data may be rolled back, that is, it may not end up in the database, that is, it does not exist. Dirty reads are those that read data that must eventually exist.
  • Repeatable read: Repeatable read means that the data read at the beginning of a transaction is the same as the data read at any time until the end of the transaction. Usually for data UPDATE operations.
  • Non-repeatable read: In contrast to repeatable read, non-repeatable read means that the same batch of data may be read at different times in the same transaction and may be affected by other transactions, for example, other transactions change the batch of data and commit it. Usually for data UPDATE operations.
  • Phantom reads: Phantom reads are for INSERT operations. Assume that the transaction for some lines for A change, but has not been submitted and the transaction B insert with the transaction before A change of record of the same line, and the transaction is A submit before you submit, and then, in A transaction in A query, will find that just change for some of the data did not play A role, but in fact is the transaction B just inserted in, let the user feel very magical, The feeling appears the illusion, this is called the illusion read.

Mysql transaction isolation level

Said by: www.520code.net/index.php/a…

  • Read Uncommited: If a transaction has started writing data, the other transaction is not allowed to write data at the same time, but other transactions are allowed to Read the row. This isolation level can be achieved through exclusive write locks.

  • Read Committed: A transaction that reads allows other transactions to continue to access the row, but an uncommitted write transaction prevents other transactions from accessing the row. This can be achieved through “instant shared read locks” and “exclusive write locks”.

  • Repeatable Read transactions that Read data will prohibit write transactions (but allow Read transactions) and write transactions will prohibit any other transactions. This can be achieved through shared read locks and exclusive write locks.

  • SERIALIZABLE: SERIALIZABLE read plus shared lock, write plus exclusive lock, read and write mutually exclusive.

The default transaction isolation level of mysql is Repeatable Read

4. Types of mysql locks

Said by: www.520code.net/index.php/a…

  • Shared lock (S lock) : Used for read-only operations (SELECT) to lock a shared resource. Shared locks do not prevent other users from reading, but prevent other users from writing and modifying.

  • Update lock (U lock) : Used on updatable resources. Prevents the common form of deadlock that occurs when multiple sessions are reading, locking, and possibly following resource updates.

  • Exclusive locks (X locks, also known as exclusive locks) : Only one exclusive lock can be used on a resource at a time, and all other locks are blocked including shared locks. Write is an exclusive lock, which effectively prevents dirty reads.

5. When can deadlock be caused

Said by: www.aneasystone.com/archives/20…

Take the STUDENTS table as an example:

Where, ID is the primary key, NO (student number) is the secondary unique index, name (name) and age (age) are the secondary non-unique indexes, and Score (credit) has no index. The database isolation level is RR.

Deadlock case one

The root cause of a deadlock is that two or more transactions are locked in an inconsistent order. This deadlock case is actually the most classic deadlock scenario.

First, transaction A acquires the lock with ID = 20 (LOCK_mode X locks rec but not gap) and transaction B acquires the lock with ID = 30. Transaction A then tries to acquire the lock with ID = 30, which is already held by transaction B, so transaction A waits for transaction B to release the lock, and then transaction B tries to acquire the lock with ID = 20, which is held by transaction A, so the two transactions wait for each other, resulting in A deadlock.

Deadlock case two

First, transaction A and transaction B execute two UPDATE statements, but because id = 25 and id = 26 records do not exist, transaction A and transaction B do not UPDATE any records, but because the database isolation level is RR, So a gap lock is added between (20, 30) (lock_mode X locks gap before REc) and there is no conflict between gap locks and gap locks. Id = 25; id = 26; Lock_mode X locks gap before rec insert intention needs to be inserted between (20, 30). The intention locks conflict with the gap locks, so the two transactions wait for each other and eventually form a deadlock.

The solution to this deadlock is simple; obviously, the first two UPDATE statements are invalid, so just delete them. Alternatively, you can change the database isolation level to RC so that there are no gap locks during updates. This example is the deadlock log scenario mentioned in the beginning of the article. Although the UPDATE statement is invalid, it looks silly, but it is a real scenario, because in a real project code can be very complicated, such as using ORM framework, application layer and data layer code separation. Most developers don’t know what SQL statements will be generated when they write code. I also got binlog from the DBA and found all the SQL statements for transaction execution in it. I found an invalid UPDATE statement in it. The deadlock was fixed.

Deadlock case three

In this case, there is only one SQL statement per transaction, but it can cause a deadlock problem. In fact, this deadlock is no different from the case, but it is understood in more depth. Note that in a range query, the lock is one record at a time, so even though there is only one SQL statement, if the two SQL statements are locked in a different order, it will also cause deadlock.

In case 1, transaction A is locked in the order of id = 20 -> 30, and transaction B is locked in the order of ID = 30 -> 20, which is exactly the opposite, resulting in A deadlock. Id = 15 -> 18 -> 20; id = 15 -> 18 -> 20; id = 15 -> 18 -> 20 (age, id) = (24, 18) -> (24, 20) -> (25, 15) -> (25, 49), where the lock sequence is id = 18 -> 20 -> 15 -> 49. You can see that transaction A locks 15 and then 18, while transaction B locks 18 and then 15, resulting in A deadlock.

How do I avoid deadlocks

We are not done with deadlocks, but at the end of this article we will discuss ways to avoid deadlocks.

Problems arise

Deadlock problems encountered by bloggers are not in the cases listed in the concept. There is an interface for uploading files to the group, and the test here is to change the count of files in the group. A 500 error was reported while testing concurrency:

### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ### The error may involve defaultParameterMap ### The error occurred while setting parameters  ### SQL: update wx_interchange.team_info SET file_count = ? where tid = ? ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction",Copy the code

No Deadlock was found when trying to get lock. Mysql > restart transaction

Log in to mysql and use:

show engine innodb status;
Copy the code

Check Innodb Status, where Innodb Status records information about the last deadlock:


------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-04-25 17:16:24 0x7fb11e794700
*** (1) TRANSACTION:
TRANSACTION 4858, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 18151, OS thread handle 140398695458560, query id 123200 ip.ip.ip.ip root updating
update wx_interchange.team_info
         SET file_count = 5 
        where tid = '123'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 5 n bits 96 index uk_team_info_tid of table `wx_interchange`.`team_info` trx id 4858 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 11; hex 3137313764343932643439; asc 123;;
 1: len 4; hex 80000015; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 4861, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 18152, OS thread handle 140398697203456, query id 123206 ip.ip.ip.ip root updating
update wx_interchange.team_info
         SET file_count = 5 
        where tid = '123'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 58 page no 5 n bits 96 index uk_team_info_tid of table `wx_interchange`.`team_info` trx id 4861 lock mode S locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 11; hex 3137313764343932643439; asc 123;;
 1: len 4; hex 80000015; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 58 page no 5 n bits 96 index uk_team_info_tid of table `wx_interchange`.`team_info` trx id 4861 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 11; hex 3137313764343932643439; asc 123;;
 1: len 4; hex 80000015; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
Copy the code

Select * from SQL where deadlock occurs;

*** (1) TRANSACTION:
update wx_interchange.team_info
         SET file_count = 5 
        where tid = '123'
        
*** (2) TRANSACTION:
update wx_interchange.team_info
         SET file_count = 5 
        where tid = '123'
Copy the code

Update statement 1 (uk_team_info_TID); update statement 2 (uk_team_info_tid); update statement 1 (uk_team_info_tid); update statement 2 (uk_team_info_tid); Transaction 2 is rolled back.

The necessary conditions for a deadlock to occur:

  • The mutex.
  • Request and hold conditions.
  • No deprivation of conditions.
  • Loop wait.

From the log, transaction 1 and transaction 2 are locked for the same row, which is a little different from the three cases mentioned in the previous concept section. In any case, the circular wait condition cannot be satisfied.

Because the logic where the deadlock occurs adds transaction management, look at this business code:

/** * Upload files to the project team **@paramFileInfo Instance object *@returnInstance object */
@Override
@Transactional(rollbackFor = TeamException.class)
public FileInfoDTO uploadFileToTeam(FileInfo fileInfo, TeamFile teamFile) {
    String fileId = UniqueKeyUtil.getUniqueKey();
    fileInfo.setFileId(fileId);
    teamFile.setFileId(fileId);
    this.fileInfoDao.insert(fileInfo);
    this.teamFileDao.insert(teamFile);

    String tid = teamFile.getTid();
    // Change the project team file count
    this.updateTeamInfoCountProperty(tid, 1);

    return fileInfoDao.queryByFileId(fileInfo.getFileId());
}


/** * Update the count attribute of the project team **@paramTid Project team TID *@paramCountChangeNum Counts the number of changes, plus or minus */
@Override
public void updateTeamInfoCountProperty(String tid, Integer countChangeNum) {
    TeamInfo teamInfoFromQuery = teamInfoDao.queryByTid(tid);
    TeamInfo teamInfoForUpdate = new TeamInfo();
    teamInfoForUpdate.setTid(tid);
    teamInfoForUpdate.setFileCount(teamInfoFromQuery.getFileCount() + countChangeNum);
    teamInfoDao.updateByTid(teamInfoForUpdate);
}
Copy the code

Correlation table structure (simplified) :

create table `team_info` (
    `id` int not null auto_increment comment 'Proxy primary key',
    `tid` varchar(32) not null comment 'Project tid, randomly generated, unique key',
    `file_count` int not null default 0 comment 'Project group File Quantity',
    primary key (`id`),
    unique key `uk_team_info_tid` (`tid`)
) comment 'Project Group Table';
Copy the code
create table `team_file` (
    `id` int not null auto_increment comment 'Proxy primary key',
    `tid` varchar(32) not null comment 'Project tid, foreign key',
    `file_id` varchar(32) not null comment 'File fileId, foreign key',
    `creation_time` timestamp not null default current_timestamp comment 'Creation time, auto write',
    `modified_time` timestamp not null default current_timestamp on update current_timestamp comment 'Modify time, auto write',
    primary key (`id`),
    foreign key `fk_team_file_tid`  (`tid`) references wx_interchange.team_info(`tid`),
    foreign key `fk_team_file_file_id` (`file_id`) references wx_interchange.file_info(`file_id`)
) comment 'Project Group Document List';
Copy the code

The transaction logic is:

  1. When a new file is uploaded to the TID group, insert a row in the file information table
  2. Insert a row in the group-file association table due to the presence of foreign keysfk_team_file_tid (tid), so you need to check if tid exists in the team_info table
  3. Example Query the number of files in the TID group
  4. The number of files in the TID group + 1
  5. Update the number of files in the TID team
  6. Example Query the file information corresponding to fileId

Problem analysis

Reference: juejin. Cn/post / 684490…

Suppose there is a group tid = 123 and the number of files file_count = 5

For the above transaction logic, it is possible for steps 2-5 to occur when two concurrent threads occur:

Transaction 1 Transaction 2
INSERT into team_file(tid, file_id) values (#{tid}, #{fileId}) INSERT into team_file(tid, file_id) values (#{tid}, #{fileId})
Need to check if unique index exists to obtain S lock, block Need to check if unique index exists to obtain S lock, block
Unique index exists, execute

SELECT id, tid, file_count FROM team_info WHERE tid = 123;		 Unique index exists, execute

SELECT id, tid, file_count FROM team_info WHERE tid = 123;
Execute UPDATE statement (S lock is not released)

UPDATE team_info SET file_count = 5 WHERE id = 123;		 Execute UPDATE statement (S lock is not released)

UPDATE team_info SET file_count = 5 WHERE id = 123;
Gets the X lock for the row, blocked by the S lock for transaction 2 Gets the X lock for the row, blocked by the S lock for transaction 1
The update is successful, commit; A deadlock is found and the transaction is rolled back

Tip :S locks are shared locks, and X locks are mutually exclusive locks. In general, X locks and S locks are mutually exclusive, and S locks and S locks are not mutually exclusive.

The default transaction isolation level of mysql is Repeatable Read

Repeatable Read transactions that Read data will prohibit write transactions (but allow Read transactions) and write transactions will prohibit any other transactions. This can be achieved through shared read locks and exclusive write locks.

You can see from the above flow that the key to this deadlock is to acquire the S lock. Why do you need to acquire the S lock when you insert it? Since we need to detect the presence of a foreign key index, reading at RR isolation requires an S lock. Discover that a unique key exists, then perform update. Update is blocked by two transaction S locks on each other, resulting in the circular wait condition above.

To solve the problem

The solution

The core problem is the need to kill the S lock, and there are three possible solutions:

  • Reduce the RR isolation level to the RC isolation level. Here the RC isolation level is read by snapshot so that the S lock is not applied.
  • Insert select * for update with X lock.
  • Distributed locks can be added in advance, using Redis, ZK, etc. Distributed locks can be referenced in this article. Talk about distributed locking

The first approach is not practical because the isolation level cannot be easily changed. The second approach is to eat up the computing performance of the database, which is more valuable and better if you can spread the computing load across the application server.

Here we use the third scheme, redis to do distributed lock.

Redis distributed lock

@Component @Slf4j public class RedisLockService { @Autowired private StringRedisTemplate redisTemplate; /** * lock * @param key Key value * @param value Current time + timeout time * @return trueTo get the lock,falseNot get lock * / public Boolean lock (String key, String value) {/ / here redisTemplate opsForValue () setIfAbsent (key, Value) uses the redis SETNX method // to SET key to value, which is equivalent to the SET command if the key does not exist. While key exists, do nothing. SETNX is"SET if Not eXists"The shorthand. SetIfAbsent (key, value) returns if the key does not existtrue", the setting is successful. Returns if key existsfalseThat is, the setting fails.if (redisTemplate.opsForValue().setIfAbsent(key, value)) {
            return true; } // If a thread blocks between unlocking and unlocking the business code, the subsequent threads are locked and the business code can never be executed. To prevent deadlocks, validity need to set the lock mechanism String currentValue = redisTemplate. OpsForValue () get (key); // If the lock expiresif(! StringUtils.isEmpty(currentValue) && Long.parseLong(currentValue) < System.currentTimeMillis()) { // If I have two threads that get currentValue = A, both of them have value B, One thread to lock a String oldValue = redisTemplate. OpsForValue () getAndSet (key, value);if(! StringUtils.isEmpty(oldValue) && oldValue.equals(currentValue)) {return true; }}return false; } /** * unlock, that is, delete key ** @param key key value * @param value Current time + timeout period. This parameter is used for verification. Delete key and value. */ public void unlock(String key, String value) { try { String currentValue = redisTemplate.opsForValue().get(key);if(! StringUtils.isEmpty(currentValue) && currentValue.equals(value)) { redisTemplate.opsForValue().getOperations().delete(key); } } catch (Exception e) { log.error("[redis distributed lock] unlock exception, errMsg = {}", e.getMessage()); }}}Copy the code

Method of use

@Autowired private RedisLockService redisLockService; /** * Upload file to project team ** @param fileInfo instance object * @returnFileInfoDTO */ @Override @Transactional(rollbackFor = TeamException.class) public FileInfoDTO uploadFileToTeam(FileInfo fileInfo, TeamFile teamFile) { String fileId = UniqueKeyUtil.getUniqueKey(); fileInfo.setFileId(fileId); teamFile.setFileId(fileId); this.fileInfoDao.insert(fileInfo); String tid = teamFile.getTid(); Int timeout = 10 * 1000; int timeout = 10 * 1000; long time = System.currentTimeMillis() + timeout; / / lockwhile(! redisLockService.lock(tid, String.valueOf(time))) { try { Thread.sleep(100); } catch (InterruptedException e) { e.printStackTrace(); } } this.teamFileDao.insert(teamFile); / / modify this project file count. UpdateTeamInfoCountProperty (dar, TeamEnum UPDATE_FILE_COUNT, 1); // Unlock redislockService. unlock(tid, string.valueof (time));return fileInfoDao.queryByFileId(fileInfo.getFileId());
}
Copy the code

Place a lock around the block of code where a deadlock occurs. Here is a possible insert and UPDATE deadlock situation, so the lock is placed before and after the INSERT and UPDATE code blocks.