Remember mysql Gone Away and Deadlock Deadlock solutions

Gone Away and Deadlock have appeared on several projects in a program that has been running for two years recently. Here are the solutions for future reference.

The possible cause is mysql gone Away

Reference ronaldbradford.com/blog/sqlsta…

The MySQL service is down

To check whether this is the cause, run the following command to check the running time of mysql

$ mysql -uroot -p -e "show global status like 'uptime';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 68928 |
+---------------+-------+
1 row in set (0.04 sec)
Copy the code

Or check the MySQL error log to see if there is a restart message

$tail /var/log/mysql/error.log 130101 22:22:30 InnoDB: Initializing buffer pool, size = 256.0m 130101 22:22:30 InnoDB: Completed initialization of buffer pool 130101 22:22:30 InnoDB: Highest supported file format is Barracuda. 130101 22:22:30 InnoDB: 1.1.8 started; Log sequence number 63444325509 130101 22:22:30 [Note] Server hostname (bind-address): '127.0.0.1'; Port: 3306 130101 22:22:30 [Note] - '127.0.0.1' OR '127.0.0.1'; 130101 22:22:30 [Note] Server socket created on IP: '127.0.0.1'. Loaded 0 events 130101 22:22:30 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.5.28-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)Copy the code

If the uptime value is large, the mysql service has been running for a long time. The service has not been restarted recently.

If the log does not contain related information and the mysql service has not been restarted recently, check the following items.

Connection timeout

This is more likely if the program uses a long connection.

That is, no new request is initiated for a long time, the timeout on the server reaches, and the server forcibly closes the connection.

Error: Server has gone Away

$ mysql -uroot -p -e "show global variables like '%timeout';" +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 30 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout |  OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 28800 | +----------------------------+----------+ mysql> SET SESSION wait_timeout=5; # Wait 10 seconds mysql> SELECT NOW(); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 132361 Current database: *** NONE *** +---------------------+ | NOW() | +---------------------+ | 2013-01-02 11:31:15 | +---------------------+ 1 Row in set (0.00 SEC)Copy the code

So in this case, you make wait_timeout larger, and you optimize your code to make your SQL run faster.

The process is actively killed on the server. Procedure

This case is similar to case 2, except that the initiator is a DBA or another job. There is a long time slow query caused by executing kill XXX.

$ mysql -uroot -p -e "show global status like 'com_kill'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_kill      | 0     |
+---------------+-------+
Copy the code

Your SQL statement was too large.

This error also occurs when the query result set exceeds max_allowed_packet. The location method is to type the relevant error statement.

Run select * into outfile to export the file to check whether the file size exceeds max_allowed_packet. If so, adjust parameters or optimize statements.

mysql> show global variables like 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 1048576 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Modify parameters:

mysql> set global max_allowed_packet=1024*1024*16; mysql> show global variables like 'max_allowed_packet'; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | max_allowed_packet | 16777216 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Change the mysql configuration file for a long time

max_allowed_packet = 128M
Copy the code

Deadlock

screening

Deadlocks can be quickly checked by using the following command:

show engine innodb status\G;
Copy the code

Here is the deadlock log, I change the IP to localhost, desensitization

------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-11-08 11:00:01 0x7f1d3d4fc700 *** (1) TRANSACTION: TRANSACTION 13058816, ACTIVE 0 SEC Starting index read MYSQL tables in use 1, locked 1 # LOCK WAIT 7 LOCK struct(s), heap size 1136, 7 row LOCK (s), undo log entries 6## MySQL thread ID 49581, OS Thread handle 139762045613824, Updating ##mysql thread ID 49581, query ID 9659334, Update server_info_ex SET# this is the lock table SQL, I omit part, desensitising...... *** (1) HOLDS THE LOCK(S): RECORD LOCKS SPACE ID 710 Page no 14 N bits 96 index PRIMARY of table 'cloud'. 'server_info_ex' TRX ID 13058816 RECORD LOCKS SPACE ID 710 Page No 14 N bits 96 index PRIMARY of table 'cloud' Lock_mode X locks rec but not gap # lock_mode X locks rec but not gap # lock_mode X locks 96 bits rec but not gap # *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 710 page no 11 n bits 104 index PRIMARY of table `cloud`.`server_info_ex` trx id 13058816 lock_mode X locks rec but not gap waiting ..... *** (2) TRANSACTION: TRANSACTION 13058786, ACTIVE 1 SEC fetching rows mysql tables in use 4, locked 4 #mysql LOCK WAIT 925 LOCK struct(s), heap size 106704, 22490 row LOCK (s), undo log entries 249 # The size of the heap in memory is 106,704.22,490 rows locked, which is quite a lot, because the update statement uses 4 tables at the same time, updating a lot of records. MySQL thread ID 49577, OS Thread handle 139762044397312 Query ID 9659236 Localhost root executability #mysql thread ID 49577, query ID 9659236, Update server_info_ex SET server_info_ex. CPU = (SELECT * from 'SQL') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 710 page no 11 n bits 104 index PRIMARY of table `cloud`.`server_info_ex` trx id 13058786 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; Info bits 0 # type: row lock, transaction ID 13058786, cloud library server_info_ex primary key X lock (not gap), 104 bits 0: len 8; hex 73757072656d756d; asc supremum;; . *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 710 page no 14 n bits 88 index PRIMARY of table `cloud`.`server_info_ex` trx id 13058786 lock_mode  X waiting .... *** WE ROLL BACK TRANSACTION (1)Copy the code

You can see it’s an X lock, and an X lock is a mutex.

The principle of

Exclusive locks (X locks) and shared locks (S locks) :

• The so-called X lock is that when transaction T adds X lock to data A, only transaction T is allowed to read and modify data A, and other transactions cannot read and modify data A. Therefore, it is also called exclusive lock and mutually exclusive
• The so-called S lock means that when transaction T adds S lock to data A, other transactions can only add S lock to data A, but not X lock, until T releases S lock on data A. Other transactions also use S lock, so it is also called shared lock, which is not mutually exclusive

If transaction T for A data object S lock, the T can read for A but can’t be updated (S lock so called read lock), before T release A on the S lock, the other transactions can again for A S lock is also can be read, but can’t write, that is, can’t add X lock, which can be read. A, but it can’t update A.

Because the X and S locks are mutually exclusive, session2 wants the X lock and must wait for session3 to release the S lock. Session3 wants the X lock and must wait for session2 to release the S lock.

Cause of my deadlock

Update table1 set xx=(SELECT table1 from table1 where (SELECT * from table1 where (SELECT * from table1 where (SELECT * from table1)))) Select s lock, then want x lock.

The x lock of transaction 1 is about to be added or not yet. In fact, the X lock exists, but the S lock is added to transaction 2. Transaction 1 will wait for the S lock of transaction 2. Cause loop waiting.

At the end I will do a full analysis of mysql locks.

MYSQL rolls back transaction 1 (x lock only)

The solution

My program is the program of the monitoring system, and these transactions have low requirements for real-time data. I can also determine their execution time, so I can solve the problem by putting them in off-peak execution. If the real-time high procedure reference

1. Distributed lock, you can use Redis, or ZK, etc., to modify the parallelization into serial entry, the efficiency is not too bad, generally in the Internet application, in fact, is still relatively common way, the disadvantage is the introduction of new components, there is another deficiency in this place is, if this is an external interface, This is where idempotence and availability of interfaces need to be considered, which is beyond the scope of this article.
2. Retry mechanism, transaction 1 deadlock occurs, wait for a while and retry
3. 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.
4. Insert select * for update with X lock.

Database isolation level

Isolation,

There are four kinds of isolation:

• READ UNCOMMITTED: The system can READ UNCOMMITTED data. UNCOMMITTED data is called dirty data. At this point: phantom read, non-repeatable read and dirty read are allowed;
• READ COMMITTED: Only COMMITTED data can be READ. At this point: Phantom and unrepeatable reads are allowed, but dirty reads are not allowed, so the RC isolation level requires dirty reads to be addressed;
• REPEATABLE READ: Execute the same select multiple times in the same transaction without changing the READ data; In this case, phantom reads are allowed, but unrepeatable reads and dirty reads are not allowed. Therefore, the RR isolation level requires that unrepeatable reads be resolved.
• SERIALIZABLE: Unreal read, unrepeatable read and dirty read are not allowed, so SERIALIZABLE requires to solve unreal read;

Several concepts

• Dirty read: Uncommitted data can be read. RC calls for solving dirty reading;
• Non-repeatable read: when the same select is executed multiple times in the same transaction, the read data is changed (updated and committed by other transactions);
• Repeatable read: execute the same select multiple times in the same transaction without changing the read data (generally implemented using MVCC); RR levels are required to meet repeatable standards;
• Phantom read: When the same select is executed multiple times in the same transaction, the rows read are changed. That is, the number of rows decreased or increased (by other transactions delete/ INSERT and commit). SERIALIZABLE requires solving the phantom problem;

It is important to distinguish between unrepeatable and phantom reads:

Non-repeatable reading is the focus of modification:

Select with the same condition, you read the data, you read it again and it’s different

Hallucination focuses on adding or deleting:

Select the same condition, the first and second read record number is different

From the point of view of the results, both are because the results of multiple reads are inconsistent. But if you look at it from an implementation point of view, there’s a big difference:

Select for UPDATE; select in share mode; select in share mode; RRR isolation using MVCC repeatable read;

In the latter case, a gap lock is required to lock the records that satisfy the condition and the gap between all of them.

With the exception of MySQL, which uses the RR isolation level by default, other major databases use the RC isolation level.

Modify the isolation level method

permanent

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE}
Copy the code

Change the isolation level for a single session or for all new connections (effective temporarily, invalid after restart)

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
Copy the code

What conditions might cause a deadlock

The following cases are two transactions, RR isolation level

S lock: INSERT /select X lock: update

• Transaction 2 has the S lock and wants to lock X; transaction 1 has the S lock and also wants to lock X, both of them are waiting for the other S lock.
• Case 2: transaction 1 gets S, requests X, and transaction 2 gets X (there is a process, first try X, mark the lock waiting state, determine whether there is a conflict lock). When transaction 1 finds s lock, it waits for transaction 1 to release S

As shown in the figure above, the S lock is added to T1, while the X lock is not successfully added to T2, but it is real and marked as waiting state. T1 tried to obtain the X lock again, but found that it conflicted with the X lock in t2 waiting state. It is found that the X lock in t2 waiting state conflicts with the S lock in T1, resulting in deadlock.

reference

MySQL Deadlock found when trying to get lock; MySQL > insert transaction deadlock; MySQL > insert transaction deadlock

This article is formatted using MDNICE