Mysql – Clarification on thesql deadlock situation

deadlockinnodbMySQL

Following deadlock is encountered while load testing a application. After going through MySQL documentation and other online resources what I figured out was that

1) Transaction (2) holds exclusive lock for PERMISSION_ID index.

2) Transaction (1) comes and try to acquire insert intention exclusive lock for PERMISSION_ID. But put on to wait since exclusive lock is already with (2)

3) Then transaction (2) also wants to acquire insert intention exclusive lock for PERMISSION_ID which place it behind (1) in waiting queue. Thus deadlock.

What I am not sure about my explanation is assumption in step (2) where request to insert intention exclusive lock for PERMISSION_ID waits for exclusive lock to be released. Is it correct or am I missing something here. Please explain.

*** (1) TRANSACTION:
TRANSACTION 22466201, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 10 lock struct(s), heap size 1184, 16 row lock(s), undo log entries 2
MySQL thread id 12287, OS thread handle 0x2b4ea1f61700, query id 130397444 10.0.1.222 admin Sending data

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'))

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466201 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 22466203, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
8 lock struct(s), heap size 1184, 14 row lock(s), undo log entries 1
MySQL thread id 12279, OS thread handle 0x2b4ea1700700, query id 130397446 10.0.1.222 admin Sending data

INSERT INTO ROLE (PERMISSION_ID, ROLE_NAME, DOMAIN_ID) VALUES (4187, 'anonymous', (SELECT DOMAIN_ID FROM DOMAIN WHERE TENANT_ID=1 AND DOMAIN_NAME='SYSTEM'))

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466203 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;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE trx id 22466203 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

Best Answer

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

Related Solutions

MySQL deadlock – cannot restart normally

This particular deadlock is being caused by attempting to read from your v3_cam_date table for inserting rows into your usertmp table while another thread is updating the v3_cam_date table. Indicated by this statement:

LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date FIELDS TERMINATED BY  ','

This documentation page has some useful steps in dealing with deadlocks, but perhaps the most useful tip in your situation might be to use the READ COMMITTED transaction isolation level when doing your insert into usrtmp table.

This basically means that what you are selecting is only the committed values, not any changes that are happening from your LOAD DATA INFILE statement if it hasn't finished committing.

If that is acceptable to you, something like this might work^*:

-- SET SESSION TO READ COMMITTED --
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- ISSUE THE INSERT...SELECT --
INSERT INTO usertmp(userid,npayvalue,balance)       
SELECT B.`userid`, SUM(C.`moneyv`) a,(B.`balance` + B.`promotions`+ B.`promotions1`+ B.`overdraft`) b
FROM `ox_campaigns` A
INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
INNER JOIN `selfserving_users_balances` B ON B.`userid` = A.`uid`
INNER JOIN `selfserving_users` F ON F.`user_id` = B.`userid`
INNER JOIN `selfserving_users_group_balances` E ON E.`groupid` = F.`groupid` AND E.`ispostpaid` = 0
WHERE A.`revenue_type` = 6 AND C.`dt` BETWEEN A.`activate` AND A.`expire` AND C.`dt` >=  (SELECT DATE_ADD( A.ssv_payment_date_lastest, INTERVAL 1 DAY)  FROM `000_sys_params_v3` A)
AND C.`viewcharge` >0 
GROUP BY B.`userid`

-- SET BACK TO 'DEFAULT' --
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

_{^* Note, I haven't personally tried the above to ensure READ COMMITTED will stop the deadlock.}

But how long does it take to load this file? You might add a table lock around the LOAD DATA INFILE, but of course that will make the INSERT wait for the file to load all the time.

Thesql innodb_flush_log_at_trx_commit clarification

What I want to know is, this does not affect the time that the query is actually committed does it? It only affects its recovery ?

It only affects recovery.

I just want to make sure that when I do an insert or update that the query will run right at the time of processing and not 1 second later, no matter what the flush_log is set to.

Correct. There may be reasons to hide changes that other transactions see (as part of multi-version concurrency control). If you're trying to build tests to prove things - make sure you understand how transaction-isolation levels work :)

My understanding is that in the case of a crash , setting to 1 will allow it to recover all queries run on the server, where as setting it to 2 may lose the last second or two of data when trying to recover from the crash, is this correct?

Correct. To be able to recover all (setting: 1) ti also assumes that the underlying hardware is not adding additional buffering to improve IO performance. I have no reason to assume that Amazon doesn't do this, so I think setting it to 2 is a good cloud practice.

Also, if there is slow periods of updates/inserts (ie, not much happening on the server) does setting it to 2 add additional overheads on the server, or is it a case of the benefits of applying innodb_flush_log_at_trx_commit=2 to the database when the database is busy outweighs any additional overhead caused during slow times?

What happens when you set to 2 (and why you get better performance) is that you just buffer changes for longer, and therefor get more IO request merging, and better performance. It doesn't create more work.

Shameless plug for additional reading - When does MySQL perform IO?

Best Answer

SUGGESTION

Related Solutions

MySQL deadlock – cannot restart normally

Thesql innodb_flush_log_at_trx_commit clarification

Related Question