MySQL in two tables updated when the wonderful problem produced, resulting in deadlocks!

such as the next two table update statements

UPDATE hzxm201610 a,xmhzylb1201610 b SET a.gk07_1_6=b.gk04_11,a.gk07_2_6=b.f06_1,a.gk07_3_6=b.f07_1,a.gk07_4_6= B.f08_1 where substring (a.gk01,1,4) in (' 5323 ') and a.gk01=b.gk01 and dqdm= ' 532300 '

What happens if you miss the Where:

UPDATE hzxm201610 a,xmhzylb1201610 b SET a.gk07_1_6=b.gk04_11,a.gk07_2_6=b.f06_1,a.gk07_3_6=b.f07_1,a.gk07_4_6= B.f08_1 substring (a.gk01,1,4) in (' 5323 ') and a.gk01=b.gk01 and dqdm= ' 532300 '

Generally think that will be error. In fact, this statement is normal execution! And a full scan of two tables, and do nothing at the end! However, it will lock two tables for a long time, causing the application to not work!

Query the MySQL deadlock statement:

#Show processlist;
SELECT * from INFORMATION_SCHEMA. Innodb_trx;
SELECT * from INFORMATION_SCHEMA. Innodb_locks;
SELECT * from INFORMATION_SCHEMA. Innodb_lock_waits;
SHOW VARIABLES like '%lock% ';

MySQL in two tables updated when the wonderful problem produced, resulting in deadlocks!