Since you are using repeatable read, this should not be a problem. And regarding autocommit=0, I don't think it is playing any role here. From the show processlist, if at all, at the max there were only two processes acting on the row. But still this should not happen as your query should be kept in the wait queue and served back once the lock gets free.
Also check that no I/O contention is going on(This may be the cause to wait for longer times for getting the lock), when you get into this problem. Also have a look at your INNODB parameters and find out if any tuning is required. sujay -----Original Message----- From: Tony Leake [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 05, 2005 7:55 PM To: Sujay Koduri Cc: mysql@lists.mysql.com Subject: RE: innodb locking On Wed, 2005-10-05 at 07:12 -0700, Sujay Koduri wrote: > Is this happening every time you try this, or it happened first time? It's not every time but this is not the first. Mostly the query is ok, but I would like to find out why it's happening. > Also can you please tell what isolation level are you using now. I am using the default which i beleive is REPEATABLE READ. The particular query that is failing is done using autocommit as it a standalone query. Thanks tony > > 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]
