Re: Query id on SHOW INNODB STATUS
Posted by:
Kin JOY
Date: April 10, 2012 06:04AM
Rick James Wrote:
-------------------------------------------------------
> > About the innodb_lock_wait_timeout, I had no
> choice and put this value in order to prevent the
> developper's transaction from restarting...
>
> No. You have to beat on the developers to write
> transactions that don't last so long!
>
> An InnoDB transaction should not last more than a
> couple of seconds. If it does, they need to
> design there own "locks". If they need tips on
> how to do this, get them to respond to this
> thread.
>
> What language are they using? What are they doing
> that needs such a long lock?
>
> Example (job processing):
>
> 1. "acquire" an item:
> BEGIN;
> SELECT id ... WHERE who_owns IS NULL AND ... FOR
> UPDATE -- lock for an item to work on
> UPDATE ... SET who_owns = 'me', acquisition_time =
> NOW(), ... WHERE id = ...;
> test -- if the UPDATE fails, then someone else
> grabbed it, so ROLLBACK, and do not continue.
> COMMIT;
>
> 2. Work with that "item" for a long time
>
> 3. "release" the item:
> BEGIN;
> UPDATE ... SET who_owns = NULL, completion_time =
> NOW(), ... WHERE id = ...
> COMMIT;
>
> Another job should probably test this table
> periodically, looking for orphaned items. That
> is, items that were acquired hours ago, but not
> yet released. `who_owns` tells you who is 'out to
> lunch'.
It's exactly what I have done, I proved where was the problem, what caused the lock. I told them that we have two solutions to improve the current situation:
- They have to stop doing the same multiple queries, improve them and more importantly keep transactions short.
- Or, set the isolation level at ReadCommited with the drawbacks that come with it.
Anyway, thanks for your help, it's a tricky question. I even searched on MySQLperformanceblog and Baron Shwartz, Peter Zaitsev's book, they don't have my answer...
Regards,
Kin
Edited 1 time(s). Last edit at 04/10/2012 06:05AM by Kin JOY.