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
I try to make an hypothesis about the second question:
Given the commit happens before the select (commit is at 14:16:06 and
select is at 14:16:07) how is it that the select does not return the
row inserted by the transaction?
Transactions are managed by Spring. So it would be possible that before running the select
spring has raised a start transaction
or it has already used the connection for running an another query.
I start a first session where I simulate an insert into a table t
:
session1> create table t (i int auto_increment primary key);
Query OK, 0 rows affected (0,32 sec)
session1> insert into t values();
Query OK, 1 row affected (0,00 sec)
session1> select * from t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0,00 sec)
session1> start transaction;
Query OK, 0 rows affected (0,00 sec)
session1> insert into t values();
Query OK, 1 row affected (0,00 sec)
I create a new session, session2, where autocommit
is set to 0. Into this new session, a transaction is implicitly started when running a select.
session2> set autocommit = 0;
Query OK, 0 rows affected (0,00 sec)
session2> select * from t; -- this starts a transaction
+---+
| i |
+---+
| 1 |
+---+
1 rows in set (0,00 sec)
Move to session1 to commit the insert.
session1> commit;
Now move again to session2:
session2> select * from t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0,00 sec)
Session2 cannot see the row just inserted. If a commit
is raised in session2 we can see new row inserted in session1
session2> commit
1 row in set (0,00 sec)
session2> select * from t;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0,00 sec)
The general log looks like :
150804 14:04:10 2 Query select * from t
150804 14:04:30 1 Query start transaction
150804 14:04:39 1 Query insert into t values ()
150804 14:04:44 1 Query commit
150804 14:04:51 2 Query select * from t
150804 14:05:07 2 Query commit
150804 14:05:10 2 Query select * from t
The first row is related to session 2. It is when session 2 opens the transaction.
I don't know if this is what happen in your case. You could check in your general log if the connection_id 36 was used for other queries. Let we know.
Best Answer
Look in the SHOW ENGINE INNODB STATUS output for
LATEST DETECTED DEADLOCK
and between this line and the
WE ROLL BACK TRANSACTION
you will find QUERY content that was involved for each contributor to the deadlock (there may be thousands of lines of detailed info).