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

This article mainly introduces the MySQL single table analog lock which scenes, the article introduces in great detail, has a certain reference value, interested friends must read it!

There is always a lot to talk about about concurrency in MySQL, but in general, it takes some effort to simulate these locks or some lock problems, such as creating multiple tables, creating large amounts of data, and then, like debugging the second hand of a clock, to make the problem happen at exactly the right time. If you change the point of view, a single table to simulate this kind of can, in fact, it is feasible.

Today, we simply simulate deadlocks, implicit commits in transactions (which can actually be understood as bug), and gap locks by testing a single table.

Initialization data

The first preparation is to initialize the data. We create a table test with the default transaction isolation level of RR.

Build a table sentence:

Create table test (

Id int not null

Name int

Primary key (id)

Unique key (name)

) engine=innodb

Transaction isolation level:

Mysql > show variables like'% isolation%'

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | REPEATABLE-READ |

+-+ +

1 row in set (0.00 sec)

In addition, we open two windows, which we call session 1 and session 2 for short.

Analog deadlock

Let's start by simulating the deadlock problem.

Session 1:

Let's start a transaction, insert a row of records, and choose today's date for the data.

Mysql > begin

Mysql > insert into test values (2017827)

Query OK, 1 row affected (0.01sec)

Session 2

Mysql > insert into test values (2016827)

Session 2 will block at this time, and there is a special case at this time, that is, the blocking timeout will stop automatically if it times out.

Session 1:

Mysql > insert into test values (2018826)

Query OK, 1 row affected (0.00 sec)

It can be seen that the DML operation in session 1 is still possible.

Session 2:

Mysql > insert into test values (2016827)

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

If you look at session 2, you will find that there is a deadlock.

If you try the transaction isolation level RC, the effect is the same.

If you take a closer look at the process of this operation, you will find that it is still "strange" that the data are not directly dependent on each other, so how can there be a deadlock? there are silver lock upgrades and gap locks in it, leaving you to think about it.

Simulate the automatic commit of unexpected transactions

In order to successfully insert two pieces of data based on the above test data, we end the transaction in session 2.

Mysql > commit

Then start the unexpected transaction auto-commit test, this time we can test it in the same session. The background of the problem is that if we explicitly declare a transaction and do a DML operation in the same session without committing, if we open another transaction, the previous transaction will be committed automatically.

Session 1:

This is based on the data after the test in scenario 1.

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 2018 | 826 |

| | 2017 | 827 |

+-+ +

2 rows in set (0.00 sec)

We explicitly declare a transaction.

Mysql > begin

Query OK, 0 rows affected (0.02 sec)

Then insert a record and give it a new date.

Mysql > insert into test values (2019825)

Query OK, 1 row affected (0.00 sec)

Instead of committing at this time, we reopen another transaction in the current session.

Mysql > begin

Mysql > insert into test values (2015830)

If you look at session 2 at this time, you will find that the transaction has already been committed for you.

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 2018 | 826 |

| | 2017 | 827 |

+-+ +

2 rows in set (0.00 sec)

If we continue to roll back the transaction in session 1, we will find that it does not help.

Mysql > rollback

Query OK, 0 rows affected (0.01 sec)

At this time, some of the data has been submitted automatically.

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 2018 | 826 |

| | 2017 | 827 |

+-+ +

2 rows in set (0.00 sec)

Gap lock test

In fact, the above test scenarios are more or less related, and the first scenario is also related to the gap lock. I will simply simulate the gap lock with a single table.

First of all, make sure that the transaction isolation level is RR, because gap locks are specific to RR isolation levels, and there is no such customization in RC, which still has a big impact in concurrency scenarios. Let's see how it works.

Session 1:

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Specify the data range, and then display the declaration.

Mysql > select id from test where id begin

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into test values (2016829)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

I didn't give up until the transaction timed out.

The above is all the contents of the article "what are the scenarios of MySQL single table simulation locks". Thank you for reading! Hope to share the content to help you, more related knowledge, 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.