Re: Abort transaction when detecting lock
Posted by:
Rick James
Date: August 14, 2016 06:17PM
I don't think you can directly do what you propose.
* Deadlocks are immediately recognized and one transaction is rolled back.
* If you are processing multiple items, do them in some predictable order (eg, sort them). This will cut back on deadlocks, but may lead to timeouts.
* Having a transaction run longer than a few seconds is a no-no.
* If you are hitting innodb_lock_wait_timeout, your transaction need rethinking.
It sounds like you have two of those issues... "N records to update and the update incurs some work and N is big"
Let's look at other ways to "fix" the problem.
* "The update incurs some work". Can some of the work be done before STARTing the TRANSACTION? (Even if it is thrown away sometimes, this may help speed up the transaction.)
* Why is N "big"? Could you get the desired transactional integrity with smaller N?
* Might it be better to completely lock out one thread while another thread is working? That would involve something other than what you currently have involved in the transaction. A complet lockout would probably make the xaction run faster, and would avoid deadlocks and timeouts.
Run an experiment -- see how long a xaction takes when there is no competition from other threads. Then see how long it takes if multiple threads are competing.
I realize I am doing a lot of handwaving. But so are you. Would you care to be more explicit?
Subject
Views
Written By
Posted
1709
August 09, 2016 03:16AM
766
August 09, 2016 08:26AM
Re: Abort transaction when detecting lock
846
August 14, 2016 06:17PM
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.