Let's talk about MySQL deadlocks and logs in detail, and talk about mysql logs in detail.

Let's talk about MySQL deadlocks and logs in detail, and talk about mysql logs in detail.

Recently, several successive data exceptions occurred in MySQL in the early morning. Due to the typical data warehouse-type application in business scenarios, the daily stress is low and cannot be reproduced. Even some exceptions are quite strange, and the root cause analysis is very costly. In practice, how can we quickly locate online MySQL problems and fix exceptions? Next, I will share some related experiences and methods based on two actual cases.

Case1:An error occurred while updating some data.

One day, the channel staff reported that the data of a report is mostly 0, and most of the channel data is normal. This data is routinely updated by a statistical program every morning. It is reasonable to say that it is either all normal or all failed. What causes a very few data exceptions?

The first thing we can think of is to look at the statistics task log, but no exception descriptions such as SQL update failure are found after reading the log printed by the statistics program, what happened to the database at that time? Before viewing the MySQL-server log, you can habitually view the database status:

We can see a deadlock in the update in the early morning:

Due to space limitations, the context is omitted much here. From this log, we can see that TRANSACTION 1 and TRANSACTION 2 hold a certain number of row locks respectively, and then wait for the lock of the other party, finally, MySQL detects deadlock and then chooses to roll back TRANSACTION 1: Innodb currently processes deadlocks by rolling back transactions that hold at least row-level exclusive locks.

There are three problems:

1. Does the innodb row lock not lock only one row?

Because this table is created by the innodb engine, InnoDB supports row locks and table locks. The InnoDB row lock is implemented by locking the index items on the index. This is different from Oracle in MySQL. The latter is implemented by locking the corresponding data rows in the data block. The implementation of InnoDB row locks means that InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB uses table locks to lock all scanned rows! In practical applications, pay special attention to the InnoDB row lock feature. Otherwise, it may lead to a large number of lock conflicts, thus affecting concurrent performance. Because MySQL row locks apply to indexes rather than to records, although they access records of different rows, if the same index key is used, yes, there will be lock conflicts. When we use range conditions instead of equal conditions to retrieve data and request sharing or exclusive locks, InnoDB locks the index items of existing data records that meet the conditions; in addition, the gap lock also locks multiple rows. InnoDB uses the gap lock in addition to the range condition locking. If a non-existent reCord is requested to be locked using the equal condition, InnoDB also uses the gap lock!

Let's take a look at the indexing of our business tables:

It can be seen that the index of this table is extremely unreasonable: There are three indexes, but the update does not fully use the index, so the update does not use the index accurately, and the data in multiple rows must be locked, this causes a deadlock.

After understanding the principle, we can carefully construct a combined index of four fields to make update take the innodb Index accurately. In fact, after we update the index, this deadlock problem can be solved.

Note:Innodb not only prints the locks held and waited by transactions, but also records themselves. Unfortunately, it may exceed the length reserved by innodb for output results (only 1 MB of content can be printed and only the last deadlock information can be retained). If you cannot see the complete output, in this case, you can create innodb_monitor or innodb_lock_monitor tables in any database, so that the innodb status information is complete and is recorded in the error log every 15 seconds. For example, create table innodb_monitor (a int) engine = innodb;. You do not need to delete this table when recording the error log.

2. Why does some update statements fail to be rolled back?

If rollback is performed, why only some update statements fail, rather than all updates in the transaction?

This is because our innodb is automatically submitted by default:

When multiple update or insert statements are executed, innodb immediately commit the SQL statement once for persistent modification and releases the lock, this is exactly the reason why only a few statements fail after the deadlock rollback transaction in this example.

Note that some statements may be rolled back in another situation. There is a parameter named innodb_rollback_on_timeout in innodb.

This is described in the official manual:

In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. if-innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1 ). this variable was added in MySQL 5.1.15.

Explanation: If this parameter is disabled or does not exist, only the last Query of the transaction will be rolled back when timeout occurs. If it is enabled, the entire transaction will be rolled back when timeout occurs.

3. How to Reduce the innodb deadlock probability?

It is difficult to completely eliminate deadlocks in row locks and transaction scenarios. However, you can use table design, SQL adjustment, and other measures to reduce lock conflicts and deadlocks, including:

Try to use a lower isolation level. For example, if a gap lock occurs, you can change the transaction isolation level of the session or transaction to the RC (read committed) level to avoid it, however, you need to set binlog_format to row or mixed format.

Carefully design indexes and try to use indexes to access data, so that the locking is more precise, thus reducing the chance of lock conflicts;

Select a reasonable Transaction size, and the probability of lock conflicts between small transactions is smaller;

When the record set is locked, it is best to request a sufficient lock at a time. For example, if you want to modify data, you 'd better apply for exclusive locks directly, instead of applying for a shared lock first, and then requesting exclusive locks when you modify the data, which may lead to deadlocks;

When different programs access a group of tables, try to agree to access each table in the same order. For a table, try to access the rows in the table in a fixed order. This greatly reduces the chance of deadlocks;

Try to use equal conditions to access data, so as to avoid the impact of gap locks on concurrent inserts;

Do not apply for a lock level that exceeds the actual requirement. Do not display the lock during query unless required;

For some specific transactions, you can use table locks to increase processing speed or reduce the possibility of deadlocks.

Case2:Weird Lock wait timeout

Each task fails at AM and am for several consecutive days. When loading data local infile, the following error occurs: Lock wait timeout exceeded try restarting transaction innodb Java SQL exception, I learned from my colleagues on the platform that the Lock time of our own business database is too short or there is a Lock conflict. But why not? Isn't it always good? In addition, it is basically a single form task, and there are no conflict between people.

Who is responsible for the problem? Let's check whether there is a problem in our database first:

The default lock timeout time is 50 s. This time is really not short. It is estimated that the call is useless. In fact, it is useless to try it...

The show engine innodb status \ G does not SHOW any deadlock information this time, and then looks at the MySQL-server log, we hope to see from the log what operations are performed on the data before and after that time point. Here we will briefly introduce the composition of the MySQL Log File System:

(A) error Log: records the problems that occur when mysqld is started, running, or stopped. It is enabled by default.
(B) general log: general query log, which records all statements and commands. Enabling the database may cause about 5% performance loss.
(C) binlog: a binary log that records all statements for changing data. It is mainly used for slave replication and data recovery.
(D) slow log: records all queries whose execution time exceeds long_query_time seconds or where no index is used. It is disabled by default.
(E) Innodb logs: innodb redo logs and undo logs, used to restore data and cancel operations.

From the above introduction, we can see that the current log of this problem may be in d and B. If I have read the log of d, I can only enable B, however, B has a certain loss on the database performance. Because it is a full log, the volume is very large, so you must be cautious when enabling it:

I started the full log every day half an hour before and after the problem. I didn't find any MySQL-client requests to our business database! The log format is as follows, which records all connections and commands:

The problem is basically fixed. The above exception is thrown when the client requests are not sent to us, and the platform team communicates with each other to confirm the problem, finally, the platform verifies that they need to retrieve the SQL statement from the SQL task table and update the task status before executing the insert operation. As a result, this table has a large number of insert and update concurrency on the whole point, as a result, some SQL statements wait for lock to time out...

MySQL Log Analysis script

Since the early morning is the peak of the data warehouse business, many problems occur at this time. Some strange problems are that the store will not be available after the village, which cannot be reproduced during the day. How can we capture the logs we care about to quickly locate the problem? This is the top priority. Here I wrote a small script for crontab deployment. You can select the time range to enable it and sample logs every minute, it should be noted that general log is okay. Do not enable it easily; otherwise, the database performance will be greatly reduced.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.