Hi,
I have mostly myisam tables, only a few central used tables are innodb
to avoid table locks. The innodb tables are used like myisam tables e.g.
no explicit transactions.
I encountered a error
MySQL Error: Lock wait timeout exceeded; try restarting transaction
MySQL Error No: 1205
My
- Original Message -
From: Markus Falb markus.f...@fasel.at
But why is retrying better than raising the value?
So what is better, adjusting the timeout or retrying application side
and why?
Well, raising the timeout would probably help, but may cause more concurrent
connections
On Thu, Oct 11, 2012 at 7:29 AM, Markus Falb markus.f...@fasel.at wrote:
Should I raise innodb_lock_wait_timeout? What are the disadvantages?
The disadvantage is that if the locks still don't clear by the time
the timeout is reached, you're just making the other process wait
longer before
Are you managing transactions with mysql + innodb?
I had a similar issue, and I need to rework the application.
innodb does a row level transaction lock. Read locks aren't exclusive,
update locks are exclusive.
What I was doing was something like this:
Thread P1
begin;
innodb: update field set
Hi,
Check the transactions which are causing locks. Use show engine innodb
status \G to find out the transactions acquiring locks for so long. As the
scenario you mentioned (like you use innodb at simpler level), you might be
in a situation where there are SELECTs causing the issue.
It is
A 50-second 'transaction' is much too long.
If you have slow queries, let's see them, together with SHOW CREATE TABLE, SHOW
TABLE STATUS, and EXPLAIN SELECT. Quite possibly we can make them run faster,
thereby eliminating your problem.
-Original Message-
From: Akshay Suryavanshi
The original poster mentioned that he is not using transactions explicitly.
Some transactions may still occur as a side effect of some operations under
certain conditions and, in a busy high load environment, cannot be entirely
avoided. Having some experience with this, I can report that it is
I know it's been a while since I wrote serious queries, but I'm sure I
have done something like this before:
SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id,
machine.factory_id FROM events JOIN machine ON events.mach_id =
machine.mach_id WHERE machine.factory_id = 1 AND vDate =
Some places allow aliases, some don't. Some (GROUP BY, ORDER BY) even allow
ordinals.
For performance, the optimal index would be
INDEX(factory_id, date)
and then do
WHERE date = 2012-10-11
AND date 2012-10-11 + INTERVAL 1 DAY
-Original Message-
From: Mark
Further comments...
With autocommit=1, every InnoDB sql statement is a transaction by itself.
That is (as Michael says), you get transactions without explicitly saying BEGIN.
With autocommit=0, you get the messy situation that a transaction is started,
but you have to explicitly finish it
2012/10/11 13:46 -0400, Mark Haney
I know it's been a while since I wrote serious queries, but I'm sure I have
done something like this before:
SELECT SUBSTR(date,1,10) as vDate, event_id, events.mach_id, machine.factory_id
FROM events JOIN machine ON events.mach_id = machine.mach_id WHERE
11 matches
Mail list logo
