List:General Discussion« Previous MessageNext Message »
From:John Kemp Date:January 9 2002 10:15pm
Subject:Re: InnoDB : Lock wait timeout exceeded; Try restarting transacti on
View as plain text  
Walt / Heikki

Yes, I agree - with autocommit on, you do not need to supply COMMIT, and 
yes, that's the same as other RDBMSs. What I'm pointing out though is 
that I thought Heikki was suggesting that we need to supply the 
BEGIN/COMMIT for a single command even if autocommit was on, in order to 
not get the error "Lock wait timeout exceeded; Try restarting 
transaction". If that's the case, that would indicate that Mysql 
behaviour would be different to what I would expect.

With auto-commit ON, and no explicit "LOCK TABLES' mentioned, I would 
not expect to get any error about a lock wait timeout unless the same 
ROW were being selected as were being updated, inserted or deleted. With 
a sleeping connection being the second potential row-locker, I would not 
expect to see this error ever.

What I'm saying is that this looks like a bug. Sam mentioned that he 
only had one other connection when he got this error, and that this 
connection was a sleeping, persistent connection. The behaviour I saw 
was the same. A sleeping connection was supposedly locking a row needed 
(for a delete in my case). I don't think a sleeping connection (ie. one 
not actually running a query) should ever lock out a running query.

I hope that clears up the confusion.

John

Weaver, Walt wrote:

> John wrote:
> 
>>That's not something that is generally necessary with other RDBMSs.
>>
> 
> I disagree. I would imagine all transaction-oriented RDBMS's work this way.
> Oracle certainly does. You need to do an explicit commit or rollback to
> release the lock. (or close the cursor, etc.)
> 
> Unless, of course, autocommit is on.
> 
> --Walt Weaver
>   Bozeman, Montana
> 
> -----Original Message-----
> From: John Kemp [mailto:jkemp@stripped]
> Sent: Wednesday, January 09, 2002 1:40 PM
> To: Heikki Tuuri
> Cc: mysql@stripped
> Subject: Re: InnoDB : Lock wait timeout exceeded; Try restarting
> transaction
> 
> 
> Heikki,
> 
> Hmm. That's interesting. So if you do a single command, say
> 
> INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ;
> 
> You actually need to write (I'm not sure of the exact transactional 
> syntax for Mysql) -
> 
> BEGIN ; --begin a transaction
> INSERT INTO Table1 (X, Y, Z) VALUES ( A, B, C) ;
> COMMIT ; -- end a transaction
> 
> Even for a single statement with a single row update/insert?
> 
> That's not something that is generally necessary with other RDBMSs.
> 
> Can you confirm that?
> 
> Thanks,
> 
> John
> 
> Heikki Tuuri wrote:
> 
> 
>>Hi!
>>
>>Looks like your are not committing your transactions. Every UPDATE and
>>INSERT automatically sets row level locks, which are only removed when you
>>do a COMMIT or ROLLBACK.
>>
>>Or you have set innodb_lock_wait_timeout too small in my.cnf.
>>
>>InnoDB does not set table level locks. Only LOCK TABLES sets table level
>>locks.
>>
>>You can use innodb_lock_monitor to make the mysqld server program print
>>(somewhat cryptic) information about who has locks and where. See the
>>
> InnoDB
> 
>>manual at http://www.innodb.com/ibman.html
>>
>>Best regards,
>>
>>Heikki Tuuri
>>Innobase Oy
>>---
>>InnoDB - transactions, row level locking, and foreign key support for
>>
> MySQL
> 
>>See http://www.innodb.com, download MySQL-Max from http://www.mysql.com
>>
>>..........
>>
>>Hi SAm,
>>
>>I actually had a similar problem myself, but was unable to prove it was
>>the persistent connection itself causing this. I'm wondering if this
>>means that INNODB thinks that a connection that is now 'sleeping' (ie.
>>where a connection was created, used, but is now unused but still open)
>>might be locking the whole table erroneously for some reason? Which
>>version are you using? I could not figure out why Innodb would think the
>>table was locked, other than if someone specifically said 'LOCK TABLE'
>>in a query, which wasn't the case.
>>
>>Any thoughts?
>>
>>John
>>
>>
>>
>>Sam Lam wrote:
>>
>>
>>
>>>I recently switched to InnoDB & persistent connections from PHP.
>>>
>>>Lately I've been getting these errors "Lock wait timeout exceeded; Try
>>>restarting transaction" on an UPDATE on table. The system is in
>>>development so there is at most one other user ( a back end Perl script).
>>>
>>>When I switched PHP back to non-persistent connections I stopped getting
>>>that error.
>>>
>>>How does one use persistent PHP connections & InnoDB to avoid this error
> ?
>>>
>>>
>>>
>>
>>
>>
>>---------------------------------------------------------------------
>>Before posting, please check:
>>   http://www.mysql.com/manual.php   (the manual)
>>   http://lists.mysql.com/           (the list archive)
>>
>>To request this thread, e-mail <mysql-thread95875@stripped>
>>To unsubscribe, e-mail
>>
> <mysql-unsubscribe-jkemp=streetmail.com@stripped>
> 
>>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>
>>.
>>
>>
>>
> 
> 
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread95884@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-wweaver=rightnow.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread95888@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-jkemp=streetmail.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> .
> 
> 


Thread
InnoDB : Lock wait timeout exceeded; Try restarting transactionSam Lam9 Jan
  • Re: InnoDB : Lock wait timeout exceeded; Try restarting transactionJohn Kemp9 Jan
Re: InnoDB : Lock wait timeout exceeded; Try restarting transactionHeikki Tuuri9 Jan
  • Re: InnoDB : Lock wait timeout exceeded; Try restarting transactionJohn Kemp9 Jan
Re: InnoDB : Lock wait timeout exceeded; Try restarting transactionHeikki Tuuri9 Jan
RE: InnoDB : Lock wait timeout exceeded; Try restarting transactionWalt Weaver9 Jan
  • Re: InnoDB : Lock wait timeout exceeded; Try restarting transacti onJohn Kemp9 Jan
Re: InnoDB : Lock wait timeout exceeded; Try restarting transactionHeikki Tuuri9 Jan
  • Re: InnoDB : Lock wait timeout exceeded; Try restarting transactionJohn Kemp9 Jan