Re: innodb_lock_wait_timeout
- Original Message - From: Markus Falb markus.f...@fasel.at With a low timeout the connection will be terminated sooner, but if the application retries another connection is taken. I could have raised the timeout with the same effect on the db side (1 process is waiting) but maybe more performant (no new connection necessary) and with simpler logic on the application side (no retry logic) Maybe you imply that there is some kind of sleep before the retry, so that other statements could be fulfilled? No, a *lock* timeout does not kill your connection, it merely rolls back the active transaction. You do not need another connection, as you haven't lost the current one. You can of course close the connection and sleep for a moment to allow other clients time to do stuff, too; but then you lose any local session state you had (like sql variables that you may have set). Besides, if your server is so busy that you can't spare the time for a retry on a failed connection, it may be time to start looking for ways to extend capacity, too. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: innodb_lock_wait_timeout
- Original Message - From: trimurthy skd.trimur...@gmail.com hi sir even i also have a doubt regarding the connections. suppose if there is an existing connection to the server with the user name xxx and password if i send another request with the same user name and password then what will happen Please always ask questions on the list. I'm not all-knowing, and it is often that others have better answers or different experiences. As to your question, MySQL's default behaviour is to allow multiple connections for the same user; so your second request will also execute just fine - within the limits of max_connections, table locks et cetera, of course. -- -- -- What's tiny and yellow and very, very dangerous? A canary with the root password. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: innodb_lock_wait_timeout
On 11.10.2012 14:43, Johan De Meersman wrote: - Original Message - From: Markus Falb markus.f...@fasel.at But why is retrying better than raising the value? So what is better, adjusting the timeout or retrying application side and why? Well, raising the timeout would probably help, but may cause more concurrent connections to stay open until they block the server. The idea of retrying is that everything you've done before that particular transaction has already been committed, so you (probably) can't easily undo it. A retry gives you a chance to still finish what you were doing with no other loss than some time waiting. Regardless of how many retries fail, you will still be in the same position as you were when the first attempt failed. With a low timeout the connection will be terminated sooner, but if the application retries another connection is taken. I could have raised the timeout with the same effect on the db side (1 process is waiting) but maybe more performant (no new connection necessary) and with simpler logic on the application side (no retry logic) Maybe you imply that there is some kind of sleep before the retry, so that other statements could be fulfilled? I still don't get it. -- Kind Regards, Markus Falb signature.asc Description: OpenPGP digital signature
Re: innodb_lock_wait_timeout
Am 12.10.2012 15:39, schrieb Markus Falb:
With a low timeout the connection will be terminated sooner, but if the
application retries another connection is taken. I could have raised the
timeout with the same effect on the db side (1 process is waiting) but
maybe more performant (no new connection necessary) and with simpler
logic on the application side (no retry logic)
Maybe you imply that there is some kind of sleep before the retry, so
that other statements could be fulfilled?
I still don't get it
usually if you implement a db-layer with reconnect on error
you will also make a sleep before re-connect
below the relevant snippet of my since years used mysql-layer
this is from the connect-method, the query()-method itself
does disconect/connect on recoverable errors and try the same
query again after a succesfull re-connect
the intention here was to allow restart mysqld at every time
without breaking webserver-requests, usually you do not recognize
the short lag, and yes - this sort of error-handling relaxes locks
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '', $flags);
if(!$rw)
{
for($retry=1; $retry=240; $retry++)
{
$this-conn = @mysqli_init();
if($this-ssl)
{
if($this-ssl_crt === '')
{
$this-ssl_crt = 'dummy.crt';
}
/** SSL aktivieren */
$this-conn-ssl_set($this-ssl_key, $this-ssl_crt, $this-ssl_ca,
NULL, NULL);
}
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '',
$flags);
if($rw)
{
$this-conn = @mysqli_init();
if($this-ssl)
{
if($this-ssl_crt === '')
{
$this-ssl_crt = 'dummy.crt';
}
$this-conn-ssl_set($this-ssl_key, $this-ssl_crt, $this-ssl_ca,
NULL, NULL);
}
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '',
$flags);
break;
}
usleep(62500);
}
if(!$rw)
{
$this-conn = 0;
$this-error(mysqli_connect_error());
}
}
signature.asc
Description: OpenPGP digital signature
innodb_lock_wait_timeout and replication
I encountered an error MySQL Error: Lock wait timeout exceeded; try restarting transaction MySQL Error No: 1205 For this very statement an entry in the binlog was filed ... # Query_time: 52 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 ... Why is there an entry in the binlog if the statement failed? If it is in the binlog it will be replicated to a slave and on the slave it will succeed (maybe). Does this mean that a Lock wait timeout exceeded potentially drive the slave out of sync? -- Kind Regards, Markus Falb signature.asc Description: OpenPGP digital signature
Re: innodb_lock_wait_timeout
In my implementation we found no need to establish a new connection after a
lock timeout but just retried on the existing connection. We did instigate
a sleep timeout of 10 ms which theoretically increased on each iteration
but we never had to try a third time even under very heavy load.
On 2012-10-12 10:02 AM, Reindl Harald h.rei...@thelounge.net wrote:
Am 12.10.2012 15:39, schrieb Markus Falb:
With a low timeout the connection will be terminated sooner, but if the
application retries anot...
usually if you implement a db-layer with reconnect on error
you will also make a sleep before re-connect
below the relevant snippet of my since years used mysql-layer
this is from the connect-method, the query()-method itself
does disconect/connect on recoverable errors and try the same
query again after a succesfull re-connect
the intention here was to allow restart mysqld at every time
without breaking webserver-requests, usually you do not recognize
the short lag, and yes - this sort of error-handling relaxes locks
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '', $flags);
if(!$rw)
{
for($retry=1; $retry=240; $retry++)
{
$this-conn = @mysqli_init();
if($this-ssl)
{
if($this-ssl_crt === '')
{
$this-ssl_crt = 'dummy.crt';
}
/** SSL aktivieren */
$this-conn-ssl_set($this-ssl_key, $this-ssl_crt, $this-ssl_ca,
NULL, NULL);
}
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '',
$flags);
if($rw)
{
$this-conn = @mysqli_init();
if($this-ssl)
{
if($this-ssl_crt === '')
{
$this-ssl_crt = 'dummy.crt';
}
$this-conn-ssl_set($this-ssl_key, $this-ssl_crt,
$this-ssl_ca, NULL, NULL);
}
$rw = @mysqli_real_connect($this-conn, $this-host, $this-user,
$this-pwd, $this-db, $this-port, '',
$flags);
break;
}
usleep(62500);
}
if(!$rw)
{
$this-conn = 0;
$this-error(mysqli_connect_error());
}
}
RE: innodb_lock_wait_timeout and replication
I get all your mails for yearscould you not help me and suppress my name from your contact thanks To: mysql@lists.mysql.com From: markus.f...@fasel.at Subject: innodb_lock_wait_timeout and replication Date: Fri, 12 Oct 2012 16:08:42 +0200 I encountered an error MySQL Error: Lock wait timeout exceeded; try restarting transaction MySQL Error No: 1205 For this very statement an entry in the binlog was filed ... # Query_time: 52 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 ... Why is there an entry in the binlog if the statement failed? If it is in the binlog it will be replicated to a slave and on the slave it will succeed (maybe). Does this mean that a Lock wait timeout exceeded potentially drive the slave out of sync? -- Kind Regards, Markus Falb
Re: innodb_lock_wait_timeout and replication
You are subscribed to the MySQL mailing list. You will need to unsubscribe yourself. On Fri, Oct 12, 2012 at 6:58 PM, Hubert de Donnea hubertdedon...@hotmail.com wrote: I get all your mails for yearscould you not help me and suppress my name from your contact thanks To: mysql@lists.mysql.com From: markus.f...@fasel.at Subject: innodb_lock_wait_timeout and replication Date: Fri, 12 Oct 2012 16:08:42 +0200 I encountered an error MySQL Error: Lock wait timeout exceeded; try restarting transaction MySQL Error No: 1205 For this very statement an entry in the binlog was filed ... # Query_time: 52 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 ... Why is there an entry in the binlog if the statement failed? If it is in the binlog it will be replicated to a slave and on the slave it will succeed (maybe). Does this mean that a Lock wait timeout exceeded potentially drive the slave out of sync? -- Kind Regards, Markus Falb
Re: innodb_lock_wait_timeout and replication
Am 12.10.2012 19:58, schrieb Hubert de Donnea: I get all your mails for yearscould you not help me and suppress my name from your contact what the hell why do people subscribe to a mailing-list without use google to understand what a mailing-list is? http://en.wikipedia.org/wiki/Mailing_list and now read your mail-headers and use the List-Unsubscribe: included in every message of any mailing-list on this world before you disgrace yourself more signature.asc Description: OpenPGP digital signature
innodb_lock_wait_timeout
Hi, I have mostly myisam tables, only a few central used tables are innodb to avoid table locks. The innodb tables are used like myisam tables e.g. no explicit transactions. I encountered a error MySQL Error: Lock wait timeout exceeded; try restarting transaction MySQL Error No: 1205 My question is not what caused this timeout but how to handle it. Should I raise innodb_lock_wait_timeout? What are the disadvantages? What would be a sensible value? Should I retry application side? http://dev.mysql.com/doc/refman/5.0/en/innodb-error-handling.html says ...snip Both deadlocks and lock wait timeouts are normal on busy servers and it is necessary for applications to be aware that they may happen and handle them by retrying. snap... But why is retrying better than raising the value? So what is better, adjusting the timeout or retrying application side and why? -- Kind Regards, Markus Falb signature.asc Description: OpenPGP digital signature
Re: innodb_lock_wait_timeout
- Original Message - From: Markus Falb markus.f...@fasel.at But why is retrying better than raising the value? So what is better, adjusting the timeout or retrying application side and why? Well, raising the timeout would probably help, but may cause more concurrent connections to stay open until they block the server. The idea of retrying is that everything you've done before that particular transaction has already been committed, so you (probably) can't easily undo it. A retry gives you a chance to still finish what you were doing with no other loss than some time waiting. Regardless of how many retries fail, you will still be in the same position as you were when the first attempt failed. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: innodb_lock_wait_timeout
On Thu, Oct 11, 2012 at 7:29 AM, Markus Falb markus.f...@fasel.at wrote: Should I raise innodb_lock_wait_timeout? What are the disadvantages? The disadvantage is that if the locks still don't clear by the time the timeout is reached, you're just making the other process wait longer before failing. Should I retry application side? Most people don't do that, but you could. The best solution is to find out what's holding the locks and make it stop holding them for so long or use a narrower lock. I usually find these are a result of a transaction or an INSERT...SELECT that's running too long. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: innodb_lock_wait_timeout
Are you managing transactions with mysql + innodb? I had a similar issue, and I need to rework the application. innodb does a row level transaction lock. Read locks aren't exclusive, update locks are exclusive. What I was doing was something like this: Thread P1 begin; innodb: update field set field + K where filter=A; (other transactions) in a parallel thread P2 the same excecution, begin; innodb: update field set field + K where filter=A; (other transactions) P2- commit; P1- commit; SInce I was trying to update the same field, with a self reference, within 2 separete threads, I had a lock issue, because, at the end, it was a non deterministic situation. What was the field value at the end I wanted to update? what version? The command show engine innodb status; will provide information about the last wait lock / dead lock, and that will give you an Idea of what sql is making the fuss... My current lock wait ratio after code rework: Current Lock Wait ratio = 1 : 1110458921 (using tuning-primer.sh) Why is better to retry, because since things happens almost at random maybe your next retry will not encouter the issue. But neither retrying or making bigger the wait time for the lock will solve the issue, and the more load you have, the more this trouble will arise... On Thu, Oct 11, 2012 at 7:43 AM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Markus Falb markus.f...@fasel.at But why is retrying better than raising the value? So what is better, adjusting the timeout or retrying application side and why? Well, raising the timeout would probably help, but may cause more concurrent connections to stay open until they block the server. The idea of retrying is that everything you've done before that particular transaction has already been committed, so you (probably) can't easily undo it. A retry gives you a chance to still finish what you were doing with no other loss than some time waiting. Regardless of how many retries fail, you will still be in the same position as you were when the first attempt failed. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: innodb_lock_wait_timeout
Hi, Check the transactions which are causing locks. Use show engine innodb status \G to find out the transactions acquiring locks for so long. As the scenario you mentioned (like you use innodb at simpler level), you might be in a situation where there are SELECTs causing the issue. It is strange that selects will hold such locks, but a SELECT inside a transaction with tx_isolation level set to Repeatable-read (which is default), can be CONSISTENT reads. And the locks acquired by those SELECTs inside transactions will not be released unless transactions end. Find out if such SELECTs exist, if it is so, then use a more granular lock level read-committed. This will make the SELECTs release locks as soon as they finish. Usually increasing the timeout value will make it wait more before it dies. Hence not suggested. And retrying transactions is not needed since this can be solved at a DB level. Also if at all possible or desired, use the tx_isolation level at per session level, before starting any transaction. Hope this helps. Thanks, Akshay S On Thu, Oct 11, 2012 at 7:35 PM, Andrés Tello mr.crip...@gmail.com wrote: Are you managing transactions with mysql + innodb? I had a similar issue, and I need to rework the application. innodb does a row level transaction lock. Read locks aren't exclusive, update locks are exclusive. What I was doing was something like this: Thread P1 begin; innodb: update field set field + K where filter=A; (other transactions) in a parallel thread P2 the same excecution, begin; innodb: update field set field + K where filter=A; (other transactions) P2- commit; P1- commit; SInce I was trying to update the same field, with a self reference, within 2 separete threads, I had a lock issue, because, at the end, it was a non deterministic situation. What was the field value at the end I wanted to update? what version? The command show engine innodb status; will provide information about the last wait lock / dead lock, and that will give you an Idea of what sql is making the fuss... My current lock wait ratio after code rework: Current Lock Wait ratio = 1 : 1110458921 (using tuning-primer.sh) Why is better to retry, because since things happens almost at random maybe your next retry will not encouter the issue. But neither retrying or making bigger the wait time for the lock will solve the issue, and the more load you have, the more this trouble will arise... On Thu, Oct 11, 2012 at 7:43 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Markus Falb markus.f...@fasel.at But why is retrying better than raising the value? So what is better, adjusting the timeout or retrying application side and why? Well, raising the timeout would probably help, but may cause more concurrent connections to stay open until they block the server. The idea of retrying is that everything you've done before that particular transaction has already been committed, so you (probably) can't easily undo it. A retry gives you a chance to still finish what you were doing with no other loss than some time waiting. Regardless of how many retries fail, you will still be in the same position as you were when the first attempt failed. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: innodb_lock_wait_timeout
A 50-second 'transaction' is much too long. If you have slow queries, let's see them, together with SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN SELECT. Quite possibly we can make them run faster, thereby eliminating your problem. -Original Message- From: Akshay Suryavanshi [mailto:akshay.suryavansh...@gmail.com] Sent: Thursday, October 11, 2012 8:31 AM To: Andrés Tello Cc: Johan De Meersman; Markus Falb; mysql@lists.mysql.com Subject: Re: innodb_lock_wait_timeout Hi, Check the transactions which are causing locks. Use show engine innodb status \G to find out the transactions acquiring locks for so long. As the scenario you mentioned (like you use innodb at simpler level), you might be in a situation where there are SELECTs causing the issue. It is strange that selects will hold such locks, but a SELECT inside a transaction with tx_isolation level set to Repeatable-read (which is default), can be CONSISTENT reads. And the locks acquired by those SELECTs inside transactions will not be released unless transactions end. Find out if such SELECTs exist, if it is so, then use a more granular lock level read-committed. This will make the SELECTs release locks as soon as they finish. Usually increasing the timeout value will make it wait more before it dies. Hence not suggested. And retrying transactions is not needed since this can be solved at a DB level. Also if at all possible or desired, use the tx_isolation level at per session level, before starting any transaction. Hope this helps. Thanks, Akshay S On Thu, Oct 11, 2012 at 7:35 PM, Andrés Tello mr.crip...@gmail.com wrote: Are you managing transactions with mysql + innodb? I had a similar issue, and I need to rework the application. innodb does a row level transaction lock. Read locks aren't exclusive, update locks are exclusive. What I was doing was something like this: Thread P1 begin; innodb: update field set field + K where filter=A; (other transactions) in a parallel thread P2 the same excecution, begin; innodb: update field set field + K where filter=A; (other transactions) P2- commit; P1- commit; SInce I was trying to update the same field, with a self reference, within 2 separete threads, I had a lock issue, because, at the end, it was a non deterministic situation. What was the field value at the end I wanted to update? what version? The command show engine innodb status; will provide information about the last wait lock / dead lock, and that will give you an Idea of what sql is making the fuss... My current lock wait ratio after code rework: Current Lock Wait ratio = 1 : 1110458921 (using tuning-primer.sh) Why is better to retry, because since things happens almost at random maybe your next retry will not encouter the issue. But neither retrying or making bigger the wait time for the lock will solve the issue, and the more load you have, the more this trouble will arise... On Thu, Oct 11, 2012 at 7:43 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Markus Falb markus.f...@fasel.at But why is retrying better than raising the value? So what is better, adjusting the timeout or retrying application side and why? Well, raising the timeout would probably help, but may cause more concurrent connections to stay open until they block the server. The idea of retrying is that everything you've done before that particular transaction has already been committed, so you (probably) can't easily undo it. A retry gives you a chance to still finish what you were doing with no other loss than some time waiting. Regardless of how many retries fail, you will still be in the same position as you were when the first attempt failed. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: RE: innodb_lock_wait_timeout
The original poster mentioned that he is not using transactions explicitly. Some transactions may still occur as a side effect of some operations under certain conditions and, in a busy high load environment, cannot be entirely avoided. Having some experience with this, I can report that it is safe and highly effective to retry at the application layer. With a site supporting 1.5M users/day, we set a loop to retry up to 3 times.. Out of tens of millions of writes/day, we only hit the lock/timeout a couple hundred times, and never needed the second retry. On 2012-10-11 12:36 PM, Rick James rja...@yahoo-inc.com wrote: A 50-second 'transaction' is much too long. If you have slow queries, let's see them, together with SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN SELECT. Quite possibly we can make them run faster, thereby eliminating your problem. -Original Message- From: Akshay Suryavanshi [mailto:akshay.suryavansh...@gmail.com] ...
RE: RE: innodb_lock_wait_timeout
Further comments... With autocommit=1, every InnoDB sql statement is a transaction by itself. That is (as Michael says), you get transactions without explicitly saying BEGIN. With autocommit=0, you get the messy situation that a transaction is started, but you have to explicitly finish it with a COMMIT or ROLLBACK. I hope you are not in this case. For something to block a SELECT for 50 seconds implies that it is doing something really lengthy -- like an ALTER / UPDATE / DELETE and the table is _big_. If it is DELETE, see http://mysql.rjweb.org/doc.php/deletebig InnoDB tries to avoid _blocking_ other statements. (MyISAM tries much less hard.) -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Thursday, October 11, 2012 10:00 AM To: MySQL Subject: Re: RE: innodb_lock_wait_timeout The original poster mentioned that he is not using transactions explicitly. Some transactions may still occur as a side effect of some operations under certain conditions and, in a busy high load environment, cannot be entirely avoided. Having some experience with this, I can report that it is safe and highly effective to retry at the application layer. With a site supporting 1.5M users/day, we set a loop to retry up to 3 times.. Out of tens of millions of writes/day, we only hit the lock/timeout a couple hundred times, and never needed the second retry. On 2012-10-11 12:36 PM, Rick James rja...@yahoo-inc.com wrote: A 50-second 'transaction' is much too long. If you have slow queries, let's see them, together with SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN SELECT. Quite possibly we can make them run faster, thereby eliminating your problem. -Original Message- From: Akshay Suryavanshi [mailto:akshay.suryavansh...@gmail.com] ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
(innodb_lock_wait_timeout = 0) == 1
Hi all, do you know how to set innodb_lock_wait_timeout = 0? Why is it to be 0? Regards, TomH -- PROSOFT EDV-Loesungen GmbH Co. KGphone: +49 941 / 78 88 7 - 121 Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0 Geschaeftsfuehrer: Axel-Wilhelm Wegmann [EMAIL PROTECTED] AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
