Shulou(Shulou.com)05/31 Report--

This article shows you an example analysis of a special MDL LOCK deadlock in MYSQL, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

I. the origin of the problem

Earlier, the developer reported that the database on the timeline is always deadlocked, and we set up innodb_print_all_deadlocks, but in

No corresponding deadlock information was found at the corresponding time point, which led us to get any useful information and could not locate the problem.

Reason.

II. Thinking and analysis of problems

Later, the developer sent me the error code, and I saw that the error code was indeed reported by MYSQL as follows:

{"ER_LOCK_DEADLOCK", 1213, "Deadlock found when trying to get lock; try restarting transaction"}

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Well, it is certain that this deadlock was reported by MYSQL. So why didn't innodb say anything? What is it, BUG?

And then in

Https://bugs.mysql.com/

I looked for BUGS, but I didn't find it. Later, I thought about this problem. Since it is a deadlock, it will be thrown by the corresponding deadlock detection algorithm. We know that MYSQL

There is also MDL LOCK in the upper layer, and it is not only the corresponding lock of innodb that will carry out deadlock detection, is it because MDL LOCK deadlock is taken?

I found the following code in MDL.CC, which is actually the MDL_context::acquire_lock function I mentioned earlier, which is

Try to get the main function of MDL LOCK according to MDL_REQUEST.

Click (here) to collapse or open

Switch (wait_status)

{

Case MDL_wait::VICTIM:

My_error (ER_LOCK_DEADLOCK, MYF (0))

Break

Case MDL_wait::TIMEOUT:

My_error (ER_LOCK_WAIT_TIMEOUT, MYF (0))

Break

Case MDL_wait::KILLED:

If (get_owner ()-> is_killed () = = ER_QUERY_TIMEOUT)

My_error (ER_QUERY_TIMEOUT, MYF (0))

Else

My_error (ER_QUERY_INTERRUPTED, MYF (0))

Break

Default:

DBUG_ASSERT (0)

Break

}

Pay attention to the red part. In fact, the deadlock problem here is basically determined not to be triggered by the innodb layer. Since it is not triggered by the lower layer of innodb, innodb certainly will not print.

Give any information. Why the reason why the innodb layer does not print deadlock information has been found, but under what circumstances will MDL LOCK deadlocks occur?

III. Problem orientation

Since some conditions must be met to produce a deadlock:

1. At least 2 separate threads (sessions).

2. The unit operation contains several relatively independent locking steps, with a certain time difference.

For example, multiple operations in a thing.

There is also an example of repeat operations (although very segments are also available). Refer to my article on the deadlock problem of replace as follows:

Http://blog.itpub.net/7728585/viewspace-2141409/

3. Locking objects between multiple threads (sessions) must wait for each other and wait for a ring to appear.

By the way, there are three general ways to deal with deadlocks

A. Forecast in advance

B, resource classification

C. Ex post testing and release

MDL LOCK and INNODB both use C time to detect release, and its algorithm should be determined by (DSF or BSF) traversal of the graph, which should be taken a good look at later.

Well, I consider that there may be some MDL LOCK situations when backing up with MYSQLDUMP, such as adding-F,-- flush-logs-- single-transaction will

Brief use of flush table with read lock

As follows:

2017-08-08T06:22:44.916055Z 15 Connect root@localhost on using Socket

2017-08-08T06:22:44.916270Z 15 Query / *! 40100 SET @ @ SQL_MODE='' * /

2017-08-08T06:22:44.916521Z 15 Query / *! 40103 SET TIME_ZONE='+00:00' * /

2017-08-08T06:22:44.916604Z 15 Query FLUSH TABLES

2017-08-08T06:22:44.922889Z 15 Query FLUSH TABLES WITH READ LOCK

2017-08-08T06:22:44.923009Z 15 Refresh

/ mysqldata/mysql5.7/bin/mysqld, Version: 5.7.13-log (Source distribution). Started with:

Tcp port: 3307 Unix socket: / mysqldata/mysql5.7/mysqld3307.sock

Time Id Command Argument

2017-08-08T06:22:44.949003Z 15 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

2017-08-08T06:22:44.949089Z 15 Query START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * /

2017-08-08T06:22:44.949200Z 15 Query SHOW VARIABLES LIKE 'gtid\ _ mode'

2017-08-08T06:22:44.953060Z 15 Query SELECT @ @ GLOBAL.GTID_EXECUTED

2017-08-08T06:22:44.953160Z 15 Query UNLOCK TABLES

As mentioned earlier, this operation adds an S lock to the GLOBAL.

2017-08-03T18:19:11.603971Z 3 [Note] (- > MDL PRINT) Namespace is:GLOBAL

2017-08-03T18:19:11.603994Z 3 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED (S)

This causes all operations that require an IX lock on the GLOBAL to wait, such as (DML operation / DDL operation), but the flush table with read lock here

Does not meet the conditions for the deadlock described above, no matter how to first check to see if it is a problem with MYSQLDUMP, this check really detected the problem, we

During the MYSQL backup, a separate backup of the table structure was performed, which my colleagues thought was quick without adding-- single-transaction, and then checked the end of the backup.

The time is basically the same as the point in time when the deadlock occurs, so the problem becomes how to lock it if it is not added-- single-transaction,MYSQLDUMP.

In fact, from the general log at this time, the lock is as follows:

2017-08-08T06:33:22.427691Z 20 Init DB dumptest

2017-08-08T06:33:22.427794Z 20 Query SHOW CREATE DATABASE IF NOT EXISTS `dumptest`

2017-08-08T06:33:22.428100Z 20 Query show tables

2017-08-08T06:33:22.428443Z 20 Query LOCK TABLES `kkkk` READ / *! 32311 LOCAL * /, `llll` READ / *! 32311 LOCAL * /

2017-08-08T06:33:22.428551Z 20 Query show table status like 'kkkk'

2017-08-08T06:33:22.428870Z 20 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-08-08T06:33:22.428929Z 20 Query SET SESSION character_set_results = 'binary'

2017-08-08T06:33:22.429026Z 20 Query show create table `kkkk`

2017-08-08T06:33:22.429157Z 20 Query SET SESSION character_set_results = 'utf8'

2017-08-08T06:33:22.429212Z 20 Query show fields from `kkkk`

2017-08-08T06:33:22.429534Z 20 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `kkkk`

2017-08-08T06:33:22.429680Z 20 Query SET SESSION character_set_results = 'binary'

2017-08-08T06:33:22.429721Z 20 Query use `dumptest`

2017-08-08T06:33:22.429769Z 20 Query select @ @ collation_database

2017-08-08T06:33:22.429830Z 20 Query SHOW TRIGGERS LIKE 'kkkk'

2017-08-08T06:33:22.430141Z 20 Query SET SESSION character_set_results = 'utf8'

2017-08-08T06:33:22.430195Z 20 Query show table status like 'llll'

2017-08-08T06:33:22.430411Z 20 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-08-08T06:33:22.430456Z 20 Query SET SESSION character_set_results = 'binary'

2017-08-08T06:33:22.430493Z 20 Query show create table `llll`

2017-08-08T06:33:22.430557Z 20 Query SET SESSION character_set_results = 'utf8'

2017-08-08T06:33:22.430599Z 20 Query show fields from `llll`

2017-08-08T06:33:22.430813Z 20 Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `llll`

2017-08-08T06:33:22.430909Z 20 Query SET SESSION character_set_results = 'binary'

2017-08-08T06:33:22.430945Z 20 Query use `dumptest`

2017-08-08T06:33:22.431003Z 20 Query select @ @ collation_database

2017-08-08T06:33:22.431098Z 20 Query SHOW TRIGGERS LIKE 'llll'

2017-08-08T06:33:22.431330Z 20 Query SET SESSION character_set_results = 'utf8'

2017-08-08T06:33:22.431375Z 20 Query UNLOCK TABLES

My dumptest data has only two tables, kkkk and llll. We can see that mysqldump is locked through LOCK TABLES `kkkk` READ and `llll` READ.

Use unlock tables to unlock after the backup is complete. In fact, this is the root cause of the problem. It will be generated as a MDL deadlock. If you add-- single-transaction, it will not.

Will use SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ to change the isolation level to RR using things to ensure data consistency

Instead of LOCK TABLES to ensure data consistency.

IV. Simulated MDL deadlock and compatibility analysis

First of all, we need to take a look at what MDL LOCK is added to LOCK TABLES a READ, b READ, which can be seen through the print function my_print_ticket that I added to MDL.CC (specifically above)

As follows:

LOCK TABLES a READ, b READ

2017-08-08T07:12:11.764164Z 2 [Note] (acquire_lock) THIS MDL LOCK acquire ok!

2017-08-08T07:12:11.764184Z 2 [Note] (> MDL PRINT) Thread id is 2:

2017-08-08T07:12:11.764201Z 2 [Note] (- > MDL PRINT) DB_name is:test

2017-08-08T07:12:11.764258Z 2 [Note] (--> MDL PRINT) OBJ_name is:a

2017-08-08T07:12:11.764344Z 2 [Note] (- > MDL PRINT) Namespace is:TABLE

2017-08-08T07:12:11.764363Z 2 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY (SRO)

2017-08-08T07:12:11.764376Z 2 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

2017-08-08T07:12:11.764586Z 2 [Note] (acquire_lock) THIS MDL LOCK acquire ok!

2017-08-08T07:12:11.764605Z 2 [Note] (> MDL PRINT) Thread id is 2:

2017-08-08T07:12:11.764620Z 2 [Note] (- > MDL PRINT) DB_name is:test

2017-08-08T07:12:11.764634Z 2 [Note] (--> MDL PRINT) OBJ_name is:b

2017-08-08T07:12:11.764648Z 2 [Note] (- > MDL PRINT) Namespace is:TABLE

2017-08-08T07:12:11.764687Z 2 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY (SRO)

2017-08-08T07:12:11.764704Z 2 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

We can clearly see that this statement will lock the SRO MDL lock type of object to the function MDL_context::acquire_lock called by an and b respectively. Although it is a statement, the locking is separate.

For SRO lock compatibility is as follows

Request | Granted requests for lock |

Type | S SH SR SW SWLP SU SRO SNW SNRW X |

-+

SRO | +-- +-- |

You can see that SRO is not compatible with SW\ SNRW\ X, that is, it is not compatible with DML (SW)\ SNRW (LOCK TABLE WRITE)\ X (DDL).

If there is another thing that needs to DML an and b, the MDL deadlock appears as follows:

THREAD1 THREAD2

Begin; (things begin)

Insert into b values (1)

LOCK TABLES a READ, b READ; (jam)

Insert into a values (1); (blocking deadlock)

Now let's analyze it.

Thread 1

Begin; (things begin)

Insert into b values (1)

The MDL LOCK obtained is as follows:

2017-08-08T07:25:45.875676Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire ok!

2017-08-08T07:25:45.875699Z 3 [Note] (> MDL PRINT) Thread id is 3:

2017-08-08T07:25:45.875713Z 3 [Note] (- > MDL PRINT) Namespace is:GLOBAL

2017-08-08T07:25:45.875726Z 3 [Note] (- > MDL PRINT) Fast path is: (y)

2017-08-08T07:25:45.875740Z 3 [Note] (- > MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE (IX)

2017-08-08T07:25:45.875757Z 3 [Note] (- > MDL PRINT) Mdl duration is:MDL_STATEMENT

2017-08-08T07:25:45.875772Z 3 [Note] (- > MDL PRINT) Mdl status is:GRANTED

2017-08-08T07:25:45.875798Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire ok!

2017-08-08T07:25:45.875812Z 3 [Note] (> MDL PRINT) Thread id is 3:

2017-08-08T07:25:45.875826Z 3 [Note] (- > MDL PRINT) DB_name is:test

2017-08-08T07:25:45.875839Z 3 [Note] (--> MDL PRINT) OBJ_name is:b

2017-08-08T07:25:45.875853Z 3 [Note] (- > MDL PRINT) Namespace is:TABLE

2017-08-08T07:25:45.875875Z 3 [Note] (- > MDL PRINT) Fast path is: (y)

2017-08-08T07:25:45.875888Z 3 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED_WRITE (SW)

2017-08-08T07:25:45.875900Z 3 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

2017-08-08T07:25:45.875913Z 3 [Note] (- > MDL PRINT) Mdl status is:GRANTED

Regardless of GLOBAL, you can see the object mdl lock that will get SW on the b table.

Thread 2

LOCK TABLES a READ, b READ; (jam)

2017-08-08T07:27:28.684508Z 2 [Note] (acquire_lock) THIS MDL LOCK acquire ok!

2017-08-08T07:27:28.684534Z 2 [Note] (> MDL PRINT) Thread id is 2:

2017-08-08T07:27:28.684546Z 2 [Note] (- > MDL PRINT) DB_name is:test

2017-08-08T07:27:28.684558Z 2 [Note] (--> MDL PRINT) OBJ_name is:a

2017-08-08T07:27:28.684569Z 2 [Note] (- > MDL PRINT) Namespace is:TABLE

2017-08-08T07:27:28.684580Z 2 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY (SRO)

2017-08-08T07:27:28.684592Z 2 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

2017-08-08T07:27:28.684604Z 2 [Note] (- > MDL PRINT) Mdl status is:GRANTED

2017-08-08T07:27:28.684622Z 2 [Note] (acquire_lock) THIS MDL LOCK acquire WAIT (MDL_LOCK WAIT QUE)!

2017-08-08T07:27:28.684633Z 2 [Note] (> MDL PRINT) Thread id is 2:

2017-08-08T07:27:28.684644Z 2 [Note] (- > MDL PRINT) DB_name is:test

2017-08-08T07:27:28.684824Z 2 [Note] (--> MDL PRINT) OBJ_name is:b

2017-08-08T07:27:28.684838Z 2 [Note] (- > MDL PRINT) Namespace is:TABLE

2017-08-08T07:27:28.684850Z 2 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY (SRO)

2017-08-08T07:27:28.684871Z 2 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

2017-08-08T07:27:28.684884Z 2 [Note] (- > MDL PRINT) Mdl status is:EMPTY

We can clearly see that the SRO on table an is successful, but the MDL LOCK requirements on table b are waiting for MDL_LOCK WAIT QUE.

And because the b table has been acquired by the insert statement of thread 1, thread 2 needs to wait to acquire the SRO lock of the b table.

Waiting for table metadata lock | lock table a read,b read

Thread 1

Execute again

Mysql > insert into a values (1)

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

2017-08-08T07:31:13.053322Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire ok!

2017-08-08T07:31:13.053359Z 3 [Note] (> MDL PRINT) Thread id is 3:

2017-08-08T07:31:13.053388Z 3 [Note] (- > MDL PRINT) Namespace is:GLOBAL

2017-08-08T07:31:13.053401Z 3 [Note] (- > MDL PRINT) Fast path is: (y)

2017-08-08T07:31:13.053417Z 3 [Note] (- > MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE (IX)

2017-08-08T07:31:13.053589Z 3 [Note] (- > MDL PRINT) Mdl duration is:MDL_STATEMENT

2017-08-08T07:31:13.053613Z 3 [Note] (- > MDL PRINT) Mdl status is:GRANTED

2017-08-08T07:31:13.053658Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire WAIT (MDL_LOCK WAIT QUE)!

2017-08-08T07:31:13.053676Z 3 [Note] (> MDL PRINT) Thread id is 3:

2017-08-08T07:31:13.053694Z 3 [Note] (- > MDL PRINT) DB_name is:test

2017-08-08T07:31:13.053711Z 3 [Note] (--> MDL PRINT) OBJ_name is:a

2017-08-08T07:31:13.053728Z 3 [Note] (- > MDL PRINT) Namespace is:TABLE

2017-08-08T07:31:13.054065Z 3 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED_WRITE (SW)

2017-08-08T07:31:13.054092Z 3 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

2017-08-08T07:31:13.054118Z 3 [Note] (- > MDL PRINT) Mdl status is:VICTIM

Here thread 1 wants to get the sw mdl lock of table a, but thread 2 holds the SRO mdl lock of table a, obviously they are not compatible, deadlock occurs (VICTIM)

After the MDL deadlock occurs, the rollback based on a weight is as follows:

Static const uint DEADLOCK_WEIGHT_DML= 0

Static const uint DEADLOCK_WEIGHT_ULL= 50

Static const uint DEADLOCK_WEIGHT_DDL= 100

Although the principle of deadlock detection has not been studied, it should be a graph with weight and roll back the operation with small weight. Finally, the transaction operation of thread 1 is rolled back.

From the print point of view, after things are rolled back, LOCK TABLES a READ, b READ; got all the SRO MDL LOCK successfully, printed as follows:

2017-08-08T07:31:13.062693Z 2 [Note] (acquire_lock) THIS MDL LOCK acquire ok!

2017-08-08T07:31:13.062710Z 2 [Note] (> MDL PRINT) Thread id is 2:

2017-08-08T07:31:13.062725Z 2 [Note] (- > MDL PRINT) DB_name is:test

2017-08-08T07:31:13.062741Z 2 [Note] (--> MDL PRINT) OBJ_name is:a

2017-08-08T07:31:13.062756Z 2 [Note] (- > MDL PRINT) Namespace is:TABLE

2017-08-08T07:31:13.062768Z 2 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY (SRO)

2017-08-08T07:31:13.062781Z 2 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

2017-08-08T07:31:13.062795Z 2 [Note] (- > MDL PRINT) Mdl status is:GRANTED

2017-08-08T07:31:13.062954Z 2 [Note] (acquire_lock) THIS MDL LOCK acquire ok!

2017-08-08T07:31:13.062974Z 2 [Note] (> MDL PRINT) Thread id is 2:

2017-08-08T07:31:13.062989Z 2 [Note] (- > MDL PRINT) DB_name is:test

2017-08-08T07:31:13.063005Z 2 [Note] (--> MDL PRINT) OBJ_name is:b

2017-08-08T07:31:13.063023Z 2 [Note] (- > MDL PRINT) Namespace is:TABLE

2017-08-08T07:31:13.063039Z 2 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY (SRO)

2017-08-08T07:31:13.063052Z 2 [Note] (- > MDL PRINT) Mdl duration is:MDL_TRANSACTION

2017-08-08T07:31:13.063065Z 2 [Note] (- > MDL PRINT) Mdl status is:GRANTED

Sum up the deadlocks here.

Thread 1 gets the SW MDL LOCK-- of b > thread 2 gets the SRO MDL LOCK of a, but doesn't get the SRO MDL LOCK of b-- > thread 1 can't get the SW MDL LOCK of a.

In this way, both thread 1 and thread 2 are blocked, releasing thread 1 according to the weight of the MDL LOCK deadlock graph because it is a DML operation.

5. Handling

It is relatively simple to know the reason, and add-single-transaction to the backup table structure. Although the backup table structure takes a short time, deadlocks can occur under high pressure.

The above is an example analysis of a special MDL LOCK deadlock in MYSQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.