MySQL Forums
Forum List  »  InnoDB

Re: Lock wait timeout exceeded; try restarting transaction
Posted by: Vector Thorn
Date: June 08, 2010 12:55PM

Well, i've always been able to figure out how to properly partition tables, where applicable, and usually how to ensure everything is properly normalized; and i'm usually good with indexes, but i never read all the nitty gritty details about how they work in the lower-levels until yesterday (that's great reading!). Most of it's almost common sense...

I never changed the default value of autocommit. But, as i've said before, i don't use transactions at all. Unless MySQL uses them in a way that i did not know about, transactions are never used in this database. I only use stored procedures and functions, and i do never use transactions inside them.

The reason i don't use transactions is because if something goes wrong, it will fail the procedure, and i can catch that in php and file a bug report on the error, that way i can investigate it later. If you used a transaction, then you would have to declare continue handler from exception rollback;, which doesn't return an error to the php end of your application (well, at least not without great effort). So yeah, no transactions here.....

Auto commit is ON. But the funny thing is, even if it was off, there shouldn't be anything that lasts more than 1 second. But you have me curious when you say "connections sits around for more than 50 seconds". Do you mean that innoDB locks are connection-oriented, as opposed to query-oriented? I thought that as soon as innoDB could find the appropriate records and then update/delete them, it would release the locks; but it sounds like you may be implying that the locks aren't removed until the connection that issued the query is released. Is that accurate, or did i misinterpret that? I'm going to do some deeper reading about the locks today...

"select eid into @c" works :D. It seems to have shaved a couple hundreths of a second off too. I also dropped the index from T2 which seems to have helped more. I need to find a good way to benchmark execution times, but i'm pretty satisfied this is as fast as it could reasonably be.

Thanks again for your time

premium domain names that i'm selling



Edited 1 time(s). Last edit at 06/08/2010 12:57PM by Vector Thorn.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Lock wait timeout exceeded; try restarting transaction
3023
June 08, 2010 12:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.