Bug #80814 internal deadlock occurs
Submitted: 21 Mar 2016 20:20 Modified: 28 Mar 2016 7:56
Reporter: Adam Leszczyński Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.28-0ubuntu0.15.10.1 OS:Ubuntu
Assigned to: CPU Architecture:Any
Tags: internal deadlock

[21 Mar 2016 20:20] Adam Leszczyński
Description:
create table tab1 (
    col1                        int                             not null,
    col2                      int                             not null,
    col3                         int                             not null,
    col4              int                             null,
    col5                       datetime                        not null,
    col6                         datetime                        null
) ENGINE=InnoDB;
alter table tab1 add constraint tab1PK primary key(col1, col2);

mysql> show open tables where in_use <> 0
    -> ;
+----------+-------------+--------+-------------+
| Database | Table       | In_use | Name_locked |
+----------+-------------+--------+-------------+
| db       | tab1        |      1 |           0 |
+----------+-------------+--------+-------------+
1 row in set (0,04 sec)

mysql> show processlist;
+-------+--------------+-----------------+----------+---------+------+----------+----------------------------------------------------------------------------------+
| Id    | User         | Host            | db       | Command | Time | State    | Info                                                                             |
+-------+--------------+-----------------+----------+---------+------+----------+----------------------------------------------------------------------------------+
| 15509 | user         | localhost       | db       | Query   |    0 | init     | show processlist                                                                 |
| 15988 | user         | localhost:55650 | db       | Execute | 3243 | updating | UPDATE tab1 d SET d.col6 = ? WHERE d.col1 = ? and d.col2 = ?                     |
| 16011 | user         | localhost:55696 | db       | Sleep   | 3242 |          | NULL                                                                             |
...
+-------+--------------+-----------------+----------+---------+------+----------+----------------------------------------------------------------------------------+
51 rows in set (0,02 sec)

Processes 15988 and 16011 have deadlocked, but no deadlock has been detected. The situation solves self after one hour, when one of the process is killed (lock timeout).

How to repeat:
I have an application which causes a deadlock. The reproduction is not possible because of huge application and database.
[28 Mar 2016 7:56] Adam Leszczyński
made a workaround so it is not a problem any more