RE: Innodb Message Lock wait timeout exceeded; Try restarting transaction
Basically the application can get this message because another process has a
lock on the rows that the delete needs to cover for a time period then your
lock_wait_timeout. Is there some cron process? Is the table index properly?
What your average query transaction?
Active your slow query log to see what queries are taking a long time to
execute and are covering many rows-look for table scans.
DVP
Dathan Vance Pattishall http://www.friendster.com
-Original Message-
From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
Sent: Monday, September 13, 2004 9:35 AM
To: Mysql General (E-mail)
Subject: Innodb Message Lock wait timeout exceeded; Try restarting
transaction
Hello All
V4.0.16 on Sun Sparc 5.8
Innodb_lock_wait_timeout = 50.
A application using jboss got this error a few days ago. The SQL being
used, AFAIK, was a simple delete from table where date = {date}.
I am trying to determine why this happened. Searching back in the MySQL
lists archives, I was unable to find anything that I thought could help.
Any ideas of troubleshooting this problem would be appreciated.
Thanks - Gabe
There are no problems, only solutions.
Gabe Tucker
Bloomberg LP
(609) 750 6668 - P
(646) 268 5681 - F
--
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]
RE: Innodb Message Lock wait timeout exceeded; Try restarting transaction
DVP - Thanks...
I created a cron job to run show innodb status every minute and dump it to a file. In
additional I turned on innodb_lock_monitor. Hopefully, if this happens again, I will
have a snapshot of the situation.
It's still a bit frustrating not being able to see what was the cause when it happened
originally.
-Original Message-
From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED]
Sent: Monday, September 13, 2004 2:49 PM
To: Tucker, Gabriel; 'Mysql General (E-mail)'
Subject: RE: Innodb Message Lock wait timeout exceeded; Try restarting
transaction
Basically the application can get this message because another process has a
lock on the rows that the delete needs to cover for a time period then your
lock_wait_timeout. Is there some cron process? Is the table index properly?
What your average query transaction?
Active your slow query log to see what queries are taking a long time to
execute and are covering many rows-look for table scans.
DVP
Dathan Vance Pattishall http://www.friendster.com
-Original Message-
From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
Sent: Monday, September 13, 2004 9:35 AM
To: Mysql General (E-mail)
Subject: Innodb Message Lock wait timeout exceeded; Try restarting
transaction
Hello All
V4.0.16 on Sun Sparc 5.8
Innodb_lock_wait_timeout = 50.
A application using jboss got this error a few days ago. The SQL being
used, AFAIK, was a simple delete from table where date = {date}.
I am trying to determine why this happened. Searching back in the MySQL
lists archives, I was unable to find anything that I thought could help.
Any ideas of troubleshooting this problem would be appreciated.
Thanks - Gabe
There are no problems, only solutions.
Gabe Tucker
Bloomberg LP
(609) 750 6668 - P
(646) 268 5681 - F
--
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]
RE: InnoDB and lock wait timeout
Hi, Generally, an apache setup will kill a PHP enabled page when it has run for 30 seconds. Also, when a php script exits, all connections to the database are closed, so somwhere you need to keep the results of your script somwhere (hidden HTML variables, URI string), otherwise, when you change pages (and scripts of course), you lose your connection... and the transaction will probably abort... IMHO, you can only use a transaction within 1 PHP program (therefore within 1 page only), and remember your 30 second execution limit). If you are implementing a PHP / HTML application, I would guide the user over PHP enabled HTML form pages, and pass the result via a post or a URI string to the next page, etc. When you have all the info that you need, you send all the information from your previous pages to 1 parser script, which inserts all info into the database, with or without a transaction. This is the price you have to pay for a stateless connection - Your script is only active when your page is active. Hope that helps ! Cheers, Daniel Page -Original Message- From: Victor [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 06, 2002 10:58 PM To: MySQL Subject: InnoDB and lock wait timeout Hi. I have a question about rollbacks and innodb. Suppose that a query begins with begin. Then a couple inserts happen but before the commit statement is executed, the client hits stop in the browser. What is going to do the rollback? If there is a persistent connection, is it going to wait for 8 hours or so and what would happen then? Is there a significant increase in speed using persistent connections in MySQL? I am using PHP and I am cuious how one would do a persistent connection example. Does php would keep a class loaded in mem like java would? It doesn't sound plausible since php is loaded with an apache thread, which gets recycled every now and then, leaving the connection open on the mysql side but never used by apache agian. What are some approaches to solving these issues? Thanks - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB and lock wait timeout
Hi, On Sat, Apr 06, 2002 at 03:57:39PM -0500, Victor wrote: Suppose that a query begins with begin. Then a couple inserts happen but before the commit statement is executed, the client hits stop in the browser. What is going to do the rollback? If there is a persistent connection, is it going to wait for 8 hours or so and what would happen then? When the user hits the STOP button, your script (most likely) will have no idea that the browser closed the connection until it tries to send it some output, at which point it would probably receive SIGPIPE or some kind of indicator that the client is gone. By default, applications that receive SIGPIPE will terminate (Broken pipe). The connection to the mysql server would be broken, and the mysql server would issue a rollback. If your web server does any buffering, you may not know until the entire page is sent that the client disappeared. Your environment may provide different default behavior, but you should be able to modify these things to taste if you're willing to get low-level enough. PHP (since you mention it in the next paragraph) will by default kill running scripts if a timeout value is reached. This timeout can be modified accordingly. Is there a significant increase in speed using persistent connections in MySQL? I am using PHP and I am cuious how one would do a persistent connection example. Does php would keep a class loaded in mem like java would? It doesn't sound plausible since php is loaded with an apache thread, which gets recycled every now and then, leaving the connection open on the mysql side but never used by apache agian. We don't do persistent connections at all with our sites and we don't notice a speed problem (mod_perl). PHP provides a mysql_pconnect() function which is supposed to do persistant connections according to the documentation. If you don't maintain persistent connections, you may want to take measures to ensure that each mysql connection attempt doesn't involve a DNS lookup (either put the hostname in /etc/hosts or use an IP address instead of a name) if your mysql server is not localhost.. -- Michael Bacarella | Netgraft Corporation | 545 Eighth Ave #401 Systems Analysis | New York, NY 10018 Technical Support | 212 946-1038 | 917 670-6982 Managed Services | [EMAIL PROTECTED] - 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 [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
