Mysql deadlock _ MySQL

As a matter of fact, when we see the deadlock, we don't need Wang 'er, that is, I am here to let it go. The two names are just as intended, and we can see the essence through the phenomenon. However, although I have a pair of programmers, I have never understood the concept of mysql deadlock. For this reason, I have been troubled and feel that I am actually seeing the word "deadlock, I don't need Wang 2, that's me. let's just drop my tongue. The two names are like their meanings, and we can see the essence through the phenomenon. However, although I have a pair of programmers, I have never understood the concept of mysql deadlock. For this reason, I have been troubled and feel that I am worthy of the faces of programmers. I had the honor to read "High-Performance mysql" for the nth time, and felt that I had understood something, that is, I was a little worthy of the three words of the programmer.

A deadlock occurs when two or more transactions occupy each other on the same resource and request to lock the resources occupied by the other party, resulting in a vicious circle.

Let's see how good people are talking, but I cannot understand it from the heart of my heart, and I have taken many detours on this road.

Scenario

After returning to Luoyang, I have been working on a large futures trading Project. since it is a transaction, it must involve a lot of data concurrency. the large futures trading model is very complicated. To be honest, I maintained the normal operation of the entire project, but I still don't know the number of ways that "buy up" and "buy down" may make money. I can only blame myself for being too slow. So I have to give a simple example.

I have two good friends, one named Wang Yi, the other named Wang San, and the other named Wang San. I thought it was inappropriate to have an age of 18 years, one is like a flower, and the other is a charm. Wang 'er, that is, I, as a diaosi male, can make these two friends of the opposite sex, it is really a blessing for programmers.

That Tianwang was very upset. she didn't know how to upgrade Windows to win10. young people liked to accept new things, so she found me and threw it to me: ", please help me. upgrade the operating system!" "Of course, Big Brother, I can't help it." I thought in my heart, but forced installation was calm, so I returned, "Well, wait a moment, and my brother is busy ." But in the end, I successfully helped her upgrade the operating system. this girl turned into a friend who worships me. For Wang San, forget it. if you want to continue with the shameless yy, you must come and wake me up.

The scenario is, forget it, or go to SQL. I cannot go down to y.

START TRANSACTION;update girl SET age=18 where id=2;update girl set age=29 where id=1;COMMIT;

START TRANSACTION;UPDATE girl set age=19 where id=1;update girl set age=30 where id=2;commit;

If it happens that both transactions execute the first update statement, respectively modifying the age of Wang and Wang, and also locking the statement to change rows. Then, when every transaction tries to execute the second statement, but it is found that the row has been locked by the other party, and then both transactions are waiting for the other party to release the lock, while holding the lock required by the other party, it is in an endless loop.

That is to say, Wang Yi and Wang San cannot escape the time when the water passes for years. However, in my heart, that is, Wang 'er never wants them to grow old, so I joined the old man in time. what would happen?

Waiting for the end

Old Man	Wang 'er
[SQL] START TRANSACTION; Affected rows: 0 Time: 0.000 s [SQL] UPDATE girl set age = 19 where id = 1; Affected rows: 0 Time: 0.001 s	[SQL] START TRANSACTION; Affected rows: 0 Time: 0.000 s [SQL] update girl SET age = 18 where id = 2; Affected rows: 1 Time: 0.001 s
[SQL]update girl set age=30 where id=2;	[SQL]update girl set age=29 where id=1; [Err] 1213 - Deadlock found when trying to get lock; try restarting transaction

The innodb storage engine can detect the circular dependency of deadlocks and immediately return an error.

Okay, I can't do this anymore!

What if so?

The story is definitely not over here. I have to think of a way to avoid conflicts with the old man of time. so that's it!

START TRANSACTION;UPDATE girl set age=19 where id=1;update girl set age=30 where id=2;commit;

START TRANSACTION;update girl SET age=18 where id=1;update girl set age=29 where id=2;COMMIT;

That is to say, in practice, if we encounter similar problems, although innodb has performed error detection for us, we still need to avoid deadlocks.

Old Man	Wang 'er
[SQL] START TRANSACTION; Affected rows: 0 Time: 0.037 s [SQL] UPDATE girl set age = 19 where id = 1; Affected rows: 0 Time: 0.001 s	[SQL] START TRANSACTION; Affected rows: 0 Time: 0.001 s [SQL] Update girl SET age = 18 where id = 1;

That is to say, when such an update is available, it should be executed in order to avoid conflicts. Of course, the situation is different. if you don't want this, please don't trust me. hahaha!

Okay, okay. the mysql deadlock is over like this!