Performance test sharing: MySQL deadlock

Poptest is the only training institute for developing Test and development engineers in China, aiming at the ability of the trainees to be competent in automated testing, performance testing and testing tools development. If you are interested in the course, please consult qq:908821478, call 010-84505200

Deadlocks are two or more transactions that occupy each other on the same resource and request a lock on the resources occupied by the other, leading to a vicious cycle.

Look at the people say much good, but my Wang two Leng can not from the understanding of the heart, in this road took a lot of detours.

Set a scene

Back to Luoyang, I have been doing is a large futures trading project, since it is a transaction, it is certainly related to the multi-concurrency of the data, the model of large futures trading is very complex, to tell the truth, I maintain the normal operation of the whole project, but for "buy up", "buy down" are likely to make money, or not clear, So we have to give a simple example.

I have two good friends, a call Wang Yi, a call Wang San, a Christina 18 (originally wanted to 28, a think of this age as if underage, feel inappropriate), a Christina 29, a Ruhuasiyu, a shapely. Wang Er, that is, I, as a cock silk male, can make these two heterosexual friends, really entrusted to the programmer's blessing.

1. That day Wang Yi very distressed, she did not know how to Win7 operating system upgrade to WIN10, young are quite like to accept new things, so found me, throw me a sentence: "Ape brother, help me to chant, upgrade an operating system!" "Of course, good, Big Brother I will not ah", I think so, but strong outfit calm, so back to the sentence "En, a little, brother busy." "But eventually I managed to help her upgrade the operating system, this sister, worship me not, so I became friends."
2. About Wang San, forget, I again so shameless yy down, you must come to wake me up, that's it.

The scene is, forget it, or on the SQL bar, I do not go down.

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 two transactions have executed the first UPDATE statement, respectively modifying the age of Wang Yi and Wang San, as well as locking the row statement, and then each transaction attempts to execute the second statement, it is found that the row has been locked by the other side, and then both transactions are waiting for the other party to release the lock, while holding the other required lock. Into the dead loop.

In other words, fleeting water, Wang Yi and Wang San also escape time wasted, however, in my heart, that is, the heart of Wang Er, but never want them old, so I and time old man on the shelf, this will happen?

Waiting for the ending

   time old man   	Wang er
[sql]start TRANSACTION;   affected rows: 0   Time: 0.000s   [sql]update Girl set age=19 where id=1;   affected rows: 0   Time: 0.001s	[sql]start TRANSACTION;   affected rows: 0   Time: 0.000s   [sql]update girl SET age=18 where id=2;   affected rows: 1   Time: 0.001s
[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 detects the cyclic dependency of a deadlock and returns an error immediately.

Well, I'm still but time old man!

What if it does?

There must be no end to the story, I have to think of ways to avoid conflict with the old time, so 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;

In other words, in the actual combat, if we encounter similar problems, although innodb for us to do the wrong detection, but we still want to avoid deadlock.

Time Old man	Wang ER
[sql]start TRANSACTION;   affected rows: 0   Time: 0.037s     [SQL]   UPDATE girl set age=19 where id=1;   affected rows: 0   Time: 0.001s	[sql]start TRANSACTION;   affected rows: 0   Time: 0.001s     [SQL]   update girl SET age=18 where id=1;

That is, when you have such an update, try to do it in order, avoiding conflicts

Performance test sharing: MySQL deadlock