select for update deadlock

delete_insert_deadlock.sql

## Connection 1

1. begin
3. delete from table where id = 1;
5. insert into table values (1);


## Connection 2

2. begin
4. delete from table where id = 2;
6. insert into table values (2);

select_for_update_deadlock.sql

CREATE TABLE `blah` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userId` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`date`),
  KEY `date` (`date`),
  KEY `user_date` (`userId`(4),`date`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

## Connection 1

1) begin;
3) select * from blah where userid = unhex(md5('user1')) and date > '2020-05-01' for update;
5) insert into blah values (null, unhex(md5('user1')), now());
-----LOKING-----
-----UNLOCK AFTER STAGE 6-----
Query OK, 1 row affected (3.87 sec)

## Connection 1
                                            
2) begin;
4) select * from blah where userid = unhex(md5('user1')) and date > '2020-05-01' for update;
6)insert into blah values (null, unhex(md5('user1')), now());
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction