List:General Discussion« Previous MessageNext Message »
From:Ady Wicaksono Date:October 5 2005 2:27pm
Subject:Re: innodb locking
View as plain text  
I have ever meet this problem, however i never found best solutions.

Make sure that there're no other session that update the data with 
AUTOCOMMIT=0
If there's another session with autocommi=0 and update the data, kill it 
first so your session will not timeout lock

use SHOW INNODB STATUS to see what happen.



Tony Leake wrote:

>Hi, 
>
>I have a query:
>
>UPDATE dbseXyzOrders.tblOrder SET intPoUid = 98 WHERE intOrderUid =
>100000798
>
>intOrderUid is the primary key
>
>There are 25 columns in the table and a further 8 of these have indexes
>on them. The table is innodb
>
>I have just tried to run the above query 3 times and i got the follwing
>error
>
>Invalid Query Lock wait timeout exceeded; try restarting transaction
>
>AFAIK innodb locks on row level, so does that mean that something else
>is locking that row and won't let me update. If so how can i find out
>what? 
>
>Here is a copy of what mytop says at the time the query is being run
>
>Thanks for any help
>
>
>MySQL on localhost (4.1.8a-Debian_1-log)
>up 4+08:28:06 [15:07:19]
> Queries: 6.0M   qps:   17 Slow:   120.0         Se/In/Up/De(%):
>68/19/01/00
>             qps now:    8 Slow qps: 0.0  Threads:    7 (   3/   0)
>77/00/00/00
> Cache Hits: 3.0M  Hits/s:  8.5 Hits now:   4.2  Ratio: 74.2% Ratio now:
>70.0%
> Key Efficiency: 97.7%  Bps in/out:  6.8k/ 9.8k   Now in/out:  3.5k/
>5.0k
>
>      Id      User         Host/IP         DB      Time    Cmd Query or
>State
>      --      ----         -------         --      ----    ---
>----------
>  554082      root       localhost       test         0  Query show full
>processlist
>  603034      root       localhost                    0  Sleep
>  603086      root       localhost                    0  Sleep
>  602989      root       localhost                    1  Sleep
>  603301      root       localhost                    1  Sleep
>  604008      root       localhost                   11  Query UPDATE
>dbseXyzOrders.tblOrder SET intPoUid = 97 WHERE intOrde
>  389249      repl           clank               133504 Binlog Has sent
>all binlog to slave; waiting for binlog to be update
>
>
>
>  
>

Thread
innodb lockingTony Leake5 Oct
  • Re: innodb lockingAdy Wicaksono5 Oct
  • Re: innodb lockingDBA)5 Oct
RE: innodb lockingSujay Koduri5 Oct
  • RE: innodb lockingTony Leake5 Oct
RE: innodb lockingSujay Koduri5 Oct