A workaround for MYSQL deadlock "turn"

Transferred from: http://blog.csdn.net/mchdba/article/details/38313881

Before also met once, today encountered this problem, so this must be solved, online to find this article helped a lot, convenient for later review. The solution to this article is valid for my situation.

My specific situation is: the use of robotframework test, could have passed a case error, the error is: Internalerror: (1205, U ' Lock wait timeout exceeded; try restarting Transaction A lot of online search did not solve the problem, or this article is simple and effective.
2016-10-12 Update: Find the problem, that is, the online development environment and open the local environment at the same time, as a result of the scramble for database resources caused by database deadlock. The workaround can actually be simplified to two steps: 1 is to isolate the locked database thread SELECT trx_mysql_thread_id FROM information_schema.INNODB_TRX; , and 2 is to kill the detected thread.

The friend consults me to say that executes the simple UPDATE statement to fail, the symptom is as follows:
mysql> Update order_info set province_id=15, city_id= 1667 where order_from=10 and order_out_sn= ' 1407261241xxxx ';
ERROR 1205 (HY000): Lock wait timeout exceeded; Try restarting transaction
Mysql>

QQ remote past, start check

1. View the isolation level of the database:

Mysql> SELECT @ @tx_isolation;
+-----------------+
| @ @tx_isolation |
+-----------------+
| Repeatable-read |
+-----------------+
1 row in Set (0.00 sec)

Mysql>

2. To see the current thread of the library:

Mysql> show Processlist;
+----------+-----------------+-------------------+-----------------+-------------+---------+------------------- ------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-----------------+-------------------+-----------------+-------------+---------+------------------- ------+-----------------------+
| 1 | Event_scheduler | localhost | NULL | Daemon | 9635385 | Waiting on Empty queue | NULL |
| 9930577 | Business_web | 192.168.1.21:45503 | business_db | Sleep | 153 | | NULL |
| 9945825 | Business_web | 192.168.1.25:49518 | business_db | Sleep | 43 | | NULL |
| 9946322 | Business_web | 192.168.1.23:44721 | business_db | Sleep | 153 | | NULL |
| 9960167 | Business_web | 192.168.3.28:2409 | business_db | Sleep | 93 | | NULL |
| 9964484 | Business_web | 192.168.1.21:24280 | business_db | Sleep | 7 | | NULL |
| 9972499 | Business_web | 192.168.3.28:35752 | business_db | Sleep | 13 | | NULL |
| 10000117 | Business_web | 192.168.3.28:9149 | business_db | Sleep | 6 | | NULL |
| 10002523 | Business_web | 192.168.3.29:42872 | business_db | Sleep | 6 | | NULL |
| 10007545 | Business_web | 192.168.1.21:51379 | business_db | Sleep | 155 | | NULL |
......
+----------+-----------------+-------------------+-----------------+-------------+---------+------------------- ------+-----------------------+

Do not see the slow SQL record thread being executed, then go to the InnoDB transaction table Innodb_trx, see if there is a transaction thread that is being locked, and see if the ID is in the sleep thread in show full processlist, if so, It proves that the thread transaction of sleep is stuck without commit or rollback, and we need to kill it manually.

Mysql> SELECT * from INFORMATION_SCHEMA. Innodb_trx;
1. Row ***************************
trx_id:20866
Trx_state:lock WAIT
Trx_started:2014-07-31 10:42:35
Trx_requested_lock_id:20866:617:3:3
Trx_wait_started:2014-07-30 10:42:35
Trx_weight:2
trx_mysql_thread_id:9930577
Trx_query:delete from Dltask where id=1
Trx_operation_state:starting Index Read
Trx_tables_in_use:1
Trx_tables_locked:1
Trx_lock_structs:2
trx_lock_memory_bytes:376
Trx_rows_locked:1
trx_rows_modified:0
trx_concurrency_tickets:0
Trx_isolation_level:read COMMITTED
Trx_unique_checks:1
Trx_foreign_key_checks:1
Trx_last_foreign_key_error:null
trx_adaptive_hash_latched:0
trx_adaptive_hash_timeout:10000
trx_is_read_only:0
trx_autocommit_non_locking:0

3, see there is this 9930577 sql,kill off, execute kill 9930577;

Mysql> kill 9930577;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

Then go to query the Innodb_trx table, there will be no blocking transaction sleep thread exists, as follows:
Mysql> SELECT * from Innodb_trx;
Empty Set (0.00 sec)

ERROR:
No query specified

Mysql>
To execute the UPDATE statement again, it will execute normally, as follows:
mysql> Update order_info set province_id=15, city_id= 1667 where order_from=10 and order_out_sn= ' 1407261241xxxx ';
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

Mysql>

4, summary analysis

The table data volume is not big, according to the normal situation, the simple update should not cause blocking, MySQL is autocommit, will not appear the update stuck situation, to see the value of the next autocommit.
Mysql> SELECT @ @autocommit;
+--------------+
| @ @autocommit |
+--------------+
| 0 |
+--------------+
1 row in Set (0.00 sec)

Mysql>

See shiny 0, this setting causes the original UPDATE statement if there is no commit, you re-execute the UPDATE statement, you will wait for the lock, when the waiting time is too long, will be reported error 1205 (HY000): Lock wait Timeout exceeded; Try restarting transaction error.
So hurry commit the UPDATE statement just executed, then set global autocommit=1;

The cause and solution of the classic deadlock in MySQL concurrency-ze Jin-blog Park
Https://www.cnblogs.com/zejin2008/p/5262751.html

How to handle MySQL deadlock-csdn Blog
http://blog.csdn.net/revivedsun/article/details/71037362

MySQL Deadlock etc 6 combat problem solving-CSDN Blog
http://blog.csdn.net/ouyida3/article/details/47258015

A workaround for MYSQL deadlock "turn"