Your assumption is correct. Nevertheless, you need to pay attention to what is being locked.

Both transactions are locking

• space id 10787
• page no 139
• n bits 176
• index PERMISSION_ID of table IDENTITY.ROLE

The DB Connections 12287 and 12279 ran one INSERT each

Here is what I notice: In each INSERT, you are running SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='...' to fill in DOMAIN_ID. This would cause additional locks because a query's result is needed to fill DOMAIN_ID while other indexes are made to wait for the materialization of ROLE's primary key. This deadlock lasts a little longer that normal because of this intermittent need to run a SELECT.

SUGGESTION

Instead of doing

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188, 'everyone',
(SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL'));

you should retrieve the DOMAIN_ID separately into a variable and then use it in the INSERT

SELECT DOMAIN_ID INTO @domain_id FROM DOMAIN
WHERE TENANT_ID=1 AND DOMAIN_NAME='INTERNAL';
INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4188,'everyone',@domain_id);

This might reduce the need to pile up locks

It took me some time to find a plausible reason because your statement update .... select acquires a shared lock. I have thought of the following reasons.

• statements executed before current one showed in innodb status
• something about transaction isolation level
• something related to a index or foreign constraint

Then I read the manual:

14.3.4 Consistent Nonlocking Reads

The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE.

Select...update happens to take a shared lock on selected the table. I have done the following experiment:

Setting the following variables has the effect to print on error log the innodb monitor with extra informations about locks. (I use MySQL 5.7, but you can activate the innodb monitor for previous version in different way).

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

I created the following tables:

create table fi  (i int primary key, hasAutorunOccurances int, sha256 int);
insert into fi (i, hasAutorunOccurances, sha256) values (1,1,1);
create table fo  (i int primary key, uniqueness int, sha256 int, lastSeen int);
insert into fo (i,uniqueness, sha256, lastSeen) values (1,1,1,1);
create table foi (i int primary key, uniqueness int);
insert into foi (i, uniqueness) values (1,1);

And finally I issued a query similar to yours:

begin;

UPDATE fi
JOIN
(
    SELECT fo.sha256
    FROM foi
    JOIN fo  ON foi.uniqueness = fo.uniqueness
    WHERE fo.lastSeen > 1 
) x ON x.sha256=fi.sha256 
SET hasAutorunOccurances=1;

Without commit. If you look at the MySQL error log, you will find something like this:

---TRANSACTION 21313, ACTIVE 20 sec
6 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 3, OS thread handle 123145316552704, query id 8870 localhost 127.0.0.1 root cleaning up
TABLE LOCK table `test_dbaexchange`.`fi` trx id 21313 lock mode IX
RECORD LOCKS space id 1120 page no 3 n bits 72 index PRIMARY of table `test_dbaexchange`.`fi` trx id 21313 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000533d; asc     S=;;
 2: len 7; hex b2000001120110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;

TABLE LOCK table `test_dbaexchange`.`foi` trx id 21313 lock mode IS
RECORD LOCKS space id 1122 page no 3 n bits 72 index PRIMARY of table `test_dbaexchange`.`foi` trx id 21313 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000533b; asc     S;;;
 2: len 7; hex b0000001240110; asc     $  ;;
 3: len 4; hex 80000001; asc     ;;

TABLE LOCK table `test_dbaexchange`.`fo` trx id 21313 lock mode IS
RECORD LOCKS space id 1121 page no 3 n bits 72 index PRIMARY of table `test_dbaexchange`.`fo` trx id 21313 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000533c; asc     S<;;
 2: len 7; hex b1000001250110; asc     %  ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;
 5: len 4; hex 80000001; asc     ;;

So insert ... select has acquired three locks in particular one shared on fo table and another shared on foi table. Previous table are involved into the select subquery.