Shulou(Shulou.com)06/01 Report--

When testing MySQL transaction isolation level today, I found an interesting problem, and also referred to an article summarized by Yang Yi before. http://blog.itpub.net/22664653/viewspace-1612574/

The background of the problem is that when MySQL isolation level is RR (Repeatable Read), the uniqueness constraint is not invalid, and the following problems can be reproduced in multiple concurrent scenarios.

Can such a seemingly impossible thing be repeated?

I asked so, the subtext is yes, or today's notes will end with one question.

To simulate this problem, let's open two session windows to simulate this problem.

mysql> create table test3(id1 int primary key,id2 int unique,id3 int);

Query OK, 0 rows affected (0.01 sec)

#Session 1

set autocommit=0;

mysql> insert into test3 values(1,20170831,1);

Query OK, 1 row affected (0.00 sec)

commit;

#Session 2

This time taking full advantage of MVCC features, this is a snapshot read.

mysql> select *from test3;

+-----+----------+------+

| id1 | id2 | id3 |

+-----+----------+------+

| 1 | 20170831 | 1 |

+-----+----------+------+

1 row in set (0.00 sec)

Session 1 inserted a piece of data that we removed in session 2.

mysql> delete from test3 where id1=1;

Query OK, 1 row affected (0.01 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

After submission, session 2 is complete.

#Session 1

At this time, according to the characteristics of MVCC, the record with id1=1 has been deleted from Session 2. Therefore, the primary key column related data is not inserted, so what about the unique index? According to the characteristics of MVCC, it can guarantee the characteristics of repeated reading, and the data read is still unchanged.

mysql> select *from test3;

+-----+----------+------+

| id1 | id2 | id3 |

+-----+----------+------+

| 1 | 20170831 | 1 |

+-----+----------+------+

1 row in set (0.00 sec)

Now the key comes, we insert a piece of data, primary key does not conflict, unique index conflict, see if we can insert successfully.

mysql> insert into test3 values(2,20170831,2);

Query OK, 1 row affected (0.00 sec)

A demonic scene was staged.

mysql> select *from test3;

+-----+----------+------+

| id1 | id2 | id3 |

+-----+----------+------+

| 1 | 20170831 | 1 |

| 2 | 20170831 | 2 |

+-----+----------+------+

2 rows in set (0.00 sec)

Of course, here, we continue to play a play, conventionally speaking, inserting primary key column conflict data may not work, such as id1 =1, id2 =20170831, id3 =1, the client will soon feedback failure. However, when doing uniqueness check here, because the data with id1 =1 has been physically deleted.

mysql> insert into test3 values(1,20170831,1);

ERROR 1062 (23000): Duplicate entry '20170831' for key 'id2'

So let's just keep simulating a deadlock.

Session 2:

This step is to do a data cleaning, where the condition is to find and delete according to the primary key.

mysql> delete from test3 where id1=1;

Query OK, 0 rows affected (0.00 sec)

Session 1:

mysql> insert into test3 values(1,20170831,1);

At this point the session will be blocked

Session 2:

If you continue inserting this value in session 2 at this point, a deadlock problem will be reported.

mysql> insert into test3 values(1,20170831,1);

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

--deadlock generation

Session 1:

At this point there is a deadlock and the transaction automatically rolls back. Insert data that violates constraints again, and it won't work.

mysql> insert into test3 values(1,20170831,1);

ERROR 1062 (23000): Duplicate entry '20170831' for key 'id2'

Let's take a look at the logs for deadlocks in the test above:

2017-08-28T07:27:48.329631Z 14140 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.

2017-08-28T07:27:48.329740Z 14140 [Note] InnoDB:

*** (1) TRANSACTION:

TRANSACTION 31790, ACTIVE 315 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1

MySQL thread id 14138, OS thread handle 139809903986432, query id 108686 localhost root update

insert into test3 values(1,20170831,1)

2017-08-28T07:27:48.329801Z 14140 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 36 page no 3 n bits 72 index PRIMARY of table `test`.` test3` trx id 31790 lock mode S waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000001; asc ;;

1: len 6; hex 000000007c2f; asc |/;;

2: len 7; hex 33000001ac2f63; asc 3 /c;;

3: len 4; hex 8133c84f; asc 3 O;;

4: len 4; hex 80000001; asc ;;

2017-08-28T07:27:48.330040Z 14140 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 31791, ACTIVE 51 sec inserting

mysql tables in use 1, locked 1

5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1

MySQL thread id 14140, OS thread handle 139809903720192, query id 108687 localhost root update

insert into test3 values(1,20170831,1)

2017-08-28T07:27:48.330084Z 14140 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 36 page no 3 n bits 72 index PRIMARY of table `test`.` test3` trx id 31791 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

0: len 4; hex 80000001; asc ;;

1: len 6; hex 000000007c2f; asc |/;;

2: len 7; hex 33000001ac2f63; asc 3 /c;;

3: len 4; hex 8133c84f; asc 3 O;;

4: len 4; hex 80000001; asc ;;

2017-08-28T07:27:48.330342Z 14140 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 36 page no 4 n bits 72 index id2 of table `test`.` test3` trx id 31791 lock mode S waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 8133c84f; asc 3 O;;

1: len 4; hex 80000002; asc ;;

2017-08-28T07:27:48.330470Z 14140 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

This will fully link the x,s lock, fine-grained lock knowledge, understand and progress a lot.

Session 1:

Finally, we submit the transaction, look at the data again, and everything is calm again.

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select *from test3;

+-----+----------+------+

| id1 | id2 | id3 |

+-----+----------+------+

| 2 | 20170831 | 2 |

+-----+----------+------+

1 row in set (0.00 sec)

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.