------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-07 19:35:30 7f4a5748a700
*** (1) TRANSACTION:
TRANSACTION 12291417, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 406 lock struct(s), heap size 46632, 54095 row lock(s), undo log entries 4
MySQL thread id 1648312, OS thread handle 0x7f4a570ec700, query id 21648823 xx.xx.xx.xx oc5z Sending data
UPDATE deal_product_rows p SET status= 'D', deletetion_date = now() WHERE deal_id = 9612 and status<>'D'  AND NOT EXISTS (SELECT 1 FROM deal_product_rows_tmp t                     WHERE t.deal_id = p.deal_id                       and t.batch_no=5354                      and p.product_id = t.product_id)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 206 page no 3 n bits 184 index `GEN_CLUST_INDEX` of table `db1`.`deal_product_rows_tmp` trx id 12291417 lock mode S waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 6; hex 0000001031f6; asc     1 ;;
 1: len 6; hex 000000bb8d52; asc      R;;
 2: len 7; hex ab0000016e0110; asc     n  ;;
 3: len 4; hex 800014e9; asc     ;;
 4: len 4; hex 800fca6a; asc    j;;
 5: len 4; hex 8000251a; asc   % ;;
 6: len 4; hex 8000968a; asc     ;;
 7: len 4; hex 80000001; asc     ;;
 8: len 30; hex 4152544558206172747967656c2030303120d0bad180d0b0d181d0bdd18b; asc product 001             ; (total 41 bytes);
 9: len 8; hex 0000000000507040; asc      Pp@;;
 10: len 1; hex 4d; asc M;;

*** (2) TRANSACTION:
TRANSACTION 12291410, ACTIVE 1 sec starting index read
mysql tables in use 2, locked 2
6 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 119
MySQL thread id 1648305, OS thread handle 0x7f4a5748a700, query id 21648929 xx.xx.xx.xx oc5z updating
UPDATE deal_product_rows p SET status= 'D', deletetion_date = now() WHERE deal_id = 9498 and status<>'D'  AND NOT EXISTS (SELECT 1 FROM deal_product_rows_tmp t                     WHERE t.deal_id = p.deal_id                       and t.batch_no=5353                      and p.product_id = t.product_id)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 206 page no 3 n bits 152 index `GEN_CLUST_INDEX` of table `db1`.`deal_product_rows_tmp` trx id 12291410 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 6; hex 0000001031f6; asc     1 ;;
 1: len 6; hex 000000bb8d52; asc      R;;
 2: len 7; hex ab0000016e0110; asc     n  ;;
 3: len 4; hex 800014e9; asc     ;;
 4: len 4; hex 800fca6a; asc    j;;
 5: len 4; hex 8000251a; asc   % ;;
 6: len 4; hex 8000968a; asc     ;;
 7: len 4; hex 80000001; asc     ;;
 8: len 30; hex 4152544558206172747967656c2030303120d0bad180d0b0d181d0bdd18b; asc product 001             ; (total 41 bytes);
 9: len 8; hex 0000000000507040; asc      Pp@;;
 10: len 1; hex 4d; asc M;;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-08 06:16:23 7f4a5769a700
*** (1) TRANSACTION:
TRANSACTION 12314813, ACTIVE 1 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 2936, 355 row lock(s), undo log entries 110
MySQL thread id 1654182, OS thread handle 0x7f4a57658700, query id 21892885 xx.xx.xx.xx oc5z updating
DELETE FROM deal_product_rows_tmp WHERE batch_no=5754
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 3 n bits 168 index `PRIMARY` of table `db1`.`deal_product_rows_tmp` trx id 12314813 lock_mode X waiting
Record lock, heap no 88 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 4; hex 80001bb9; asc     ;;
 1: len 6; hex 000000bbe8c0; asc       ;;
 2: len 7; hex be000001b50110; asc        ;;
 3: len 4; hex 8000167b; asc    {;;
 4: len 4; hex 800fe7c4; asc     ;;
 5: len 4; hex 80002516; asc   % ;;
 6: len 4; hex 8000986e; asc    n;;
 7: len 4; hex 80000003; asc     ;;
 8: len 30; hex 415254455820d184d0bed180d0bcd18b20d0bfd180d18fd0bcd0bed183d0; asc PROD                        ; (total 81 bytes);
 9: len 8; hex 0000000000208c40; asc        @;;
 10: len 1; hex 4d; asc M;;

*** (2) TRANSACTION:
TRANSACTION 12314816, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
14 lock struct(s), heap size 2936, 95 row lock(s), undo log entries 14
MySQL thread id 1654175, OS thread handle 0x7f4a5769a700, query id 21892888 xx.xx.xx.xx oc5z updating
DELETE FROM deal_product_rows_tmp WHERE batch_no=5755
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 219 page no 3 n bits 168 index `PRIMARY` of table `db1`.`deal_product_rows_tmp` trx id 12314816 lock_mode X locks rec but not gap
Record lock, heap no 25 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

 SHOW VARIABLES LIKE "%version%";
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_version          | 5.6.39     |
| protocol_version        | 10         |
| slave_type_conversions  |            |
| version                 | 5.6.39-log |
| version_comment         | (Google)   |
| version_compile_machine | x86_64     |
| version_compile_os      | Linux      |
+-------------------------+------------+
7 rows in set (0.01 sec)

RECORD LOCKS space id 219 page no 3 n bits 112 index `PRIMARY` of table `db1`.`deal_product_rows_tmp` trx id 12336911 lock_mode X locks rec but not gap waiting
RECORD LOCKS space id 219 page no 3 n bits 112 index `PRIMARY` of table `db1`.`deal_product_rows_tmp` trx id 12336913 lock_mode X locks rec but not gap

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-08 23:35:28 7f4a572fc700
*** (1) TRANSACTION:
TRANSACTION 12336911, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1184, 37 row lock(s), undo log entries 73
MySQL thread id 1668546, OS thread handle 0x7f4a5733e700, query id 22337604 xx.xx.xx.xx oc5z updating
DELETE FROM deal_product_rows_tmp WHERE batch_no=5845
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 3 n bits 112 index `PRIMARY` of table `db1`.`deal_product_rows_tmp` trx id 12336911 lock_mode X locks rec but not gap waiting
Record lock, heap no 37 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 4; hex 80002638; asc   &8;;
 1: len 6; hex 000000bc3f11; asc     ? ;;
 2: len 7; hex e0000001c00110; asc        ;;
 3: len 4; hex 800016d6; asc     ;;
 4: len 4; hex 800ff03a; asc    :;;
 5: len 4; hex 8000246a; asc   $j;;
 6: len 4; hex 800095b0; asc     ;;
 7: len 4; hex 80000001; asc     ;;
 8: len 29; hex d094d0bed181d182d0b0d0b2d0bad0b020d0b7d0b0d0bad0b0d0b7d0b0; asc                              ;;
 9: len 8; hex 3333333333bb7240; asc 33333 r@;;
 10: len 1; hex 4d; asc M;;

*** (2) TRANSACTION:
TRANSACTION 12336913, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 8
MySQL thread id 1668549, OS thread handle 0x7f4a572fc700, query id 22337609 xx.xx.xx.xx oc5z updating
DELETE FROM deal_product_rows_tmp WHERE batch_no=5846
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 219 page no 3 n bits 112 index `PRIMARY` of table `db1`.`deal_product_rows_tmp` trx id 12336913 lock_mode X locks rec but not gap
Record lock, heap no 37 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
 0: len 4; hex 80002638; asc   &8;;
 1: len 6; hex 000000bc3f11; asc     ? ;;
 2: len 7; hex e0000001c00110; asc        ;;
 3: len 4; hex 800016d6; asc     ;;
 4: len 4; hex 800ff03a; asc    :;;
 5: len 4; hex 8000246a; asc   $j;;
 6: len 4; hex 800095b0; asc     ;;
 7: len 4; hex 80000001; asc     ;;
 8: len 29; hex d094d0bed181d182d0b0d0b2d0bad0b020d0b7d0b0d0bad0b0d0b7d0b0; asc                              ;;
 9: len 8; hex 3333333333bb7240; asc 33333 r@;;
 10: len 1; hex 4d; asc M;;

     CREATE TABLE `deal_product_rows_tmp` (
      `batch_no` int(11) NOT NULL,
      `bitrix_id` int(11) NOT NULL,
      `deal_id` int(11) NOT NULL,
      `product_id` int(11) NOT NULL,
      `quantity` int(11) NOT NULL,
      `product_name` varchar(1000) NOT NULL,
      `price` double DEFAULT NULL,
      `status` varchar(50) NOT NULL,
      `tmp_id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`tmp_id`),
      KEY `idx_deal_row_tmp_deal` (`deal_id`),
      KEY `idx_deal_row_tmp_batchno` (`batch_no`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7500 DEFAULT CHARSET=utf8 

 explain DELETE FROM deal_product_rows_tmp WHERE batch_no=5846 ;
+----+-------------+-----------------------+-------+--------------------------+--------------------------+---------+-------+------+-------------+
| id | select_type | table                 | type  | possible_keys            | key                      | key_len | ref   | rows | Extra       |
+----+-------------+-----------------------+-------+--------------------------+--------------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | deal_product_rows_tmp | range | idx_deal_row_tmp_batchno | idx_deal_row_tmp_batchno | 4       | const |    1 | Using where |
+----+-------------+-----------------------+-------+--------------------------+--------------------------+---------+-------+------+-------------+
1 row in set (0.01 sec)

CREATE TABLE test1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(100),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1(name) values ('shit1') ;
Query OK, 1 row affected (0.00 sec)

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1(name) values ('shit2') ;
Query OK, 1 row affected (0.00 sec)

mysql> delete from test1 where name = 'shit1' ;

mysql> delete from test1 where name='shit2' ;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-21 19:00:51 7f61fce4a700
*** (1) TRANSACTION:
TRANSACTION 2010, ACTIVE 76 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
MySQL thread id 14, OS thread handle 0x7f61fce8c700, query id 305 localhost root updating
delete from test1 where name = 'shit1'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test1` trx id 2010 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000007db; asc       ;;
 2: len 7; hex af000001ce0110; asc        ;;
 3: len 5; hex 7368697432; asc shit2;;

*** (2) TRANSACTION:
TRANSACTION 2011, ACTIVE 41 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 13, OS thread handle 0x7f61fce4a700, query id 306 localhost root updating
delete from test1 where name='shit2'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 19 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test1` trx id 2011 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000000007db; asc       ;;
 2: len 7; hex af000001ce0110; asc        ;;
 3: len 5; hex 7368697432; asc shit2;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 19 page no 3 n bits 72 index `PRIMARY` of table `db1`.`test1` trx id 2011 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000000007da; asc       ;;
 2: len 7; hex 2e000001d10110; asc .      ;;
 3: len 5; hex 7368697431; asc shit1;;

*** WE ROLL BACK TRANSACTION (2)