MySQL Forums
Forum List  »  InnoDB

Re: innodb deadlock on UPDATE
Posted by: Rick James
Date: July 09, 2015 11:52PM

> thousands witin each transaction

That means any of a thousand statements may be participating in the deadlock, aside from the table that got caught.

When a SELECT is gathering data from a row that is about to be UPDATEd, do
SELECT ... FOR UPDATE

Add the composite index
INDEX(M_ID, S_ID)

There may be a terminology problem here. A "deadlock" occurs when neither transaction can proceed without getting something locked by the other transaction. InnoDB always detects such and kills (ROLLBACK) one of the transactions; you have not given any clue of that.

A timeout or slowness can occur when one transaction locks some row(s) and second transaction wants those row(s). The second transaction has to sit and wait. (See innodb_lock_wait_timeout, which defaults to 50 seconds.) No ROLLBACK occurs until 50 seconds has elapsed. (or 300, in your case)

How big are the table(s)?
What is innodb_buffer_pool_size?
How much RAM do you have?

information_schema.innodb_locks shows 3 different trx_ids; are you sure there are only 2 running?

Options: ReplyQuote


Subject
Views
Written By
Posted
2052
July 06, 2015 09:37AM
929
July 08, 2015 07:24PM
1334
July 08, 2015 09:52PM
Re: innodb deadlock on UPDATE
1009
July 09, 2015 11:52PM
946
July 15, 2015 12:00PM
879
July 17, 2015 03:43PM
843
July 17, 2015 04:42PM
839
July 24, 2015 03:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.