Is this happening every time you try this, or it happened first time? Yes you right that INNODB uses row level locks, and the only reason for that error should be that someone else is trying to update the same row. As we can see from the o/p of the show procee list, someone else is also trying to update. (can't find from thet if it is the same row or not) Also can you please tell what isolation level are you using now.
sujay -----Original Message----- From: Tony Leake [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 7:27 PM To: mysql@lists.mysql.com Subject: innodb locking 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
