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
>
>
>
>
>
