MySQL Forums
Forum List  »  InnoDB

Re: innodb deadlock on UPDATE
Posted by: Michael Leitch
Date: July 15, 2015 12:00PM

Here's the settings you requested:
innodb_buffer_pool_size = 4194304000
I have 8GB of RAM

The composite index you suggested, INDEX(M_ID, S_ID) has been added, the "FOR UPDATE" was not. Thats because the transaction period is up to one minute, and it does not make sense in my application to wait for a transaction to complete. (From manual: "If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction.") Also doing so requires a retry in the code and I want to keep things simple.

Regarding the 3rd trx_ids, I am not sure where that came from, but it may have been from either one of my own queries sent while my programs were running or from a 3rd instance of my program.

I've taken the time to confirm as best I can that each instance of my program (transaction) is NOT accessing the same rows in the UNIT_COUNTS table. Its very important to note that the problem I'm seeing shouldn't be happening under row locking.

I've decided to persue an alternate solution to this problem that separates the data of the UNIT_COUNTS table into separate schemas which will solve the problem.

If you have any other advice, I'd be happy to hear it, but I will not be putting significantly more time into diagnosing what's going on.

Thanks very much for your help and time.

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
1009
July 09, 2015 11:52PM
Re: innodb deadlock on UPDATE
945
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.