RE: Deadlock due lockwait. How can I tell mysql to wait longer?

age- > From: Claudio Nanni [mailto:claudio.na...@gmail.com] > Sent: Monday, May 14, 2012 12:29 PM > To: Andrés Tello > Cc: Baron Schwartz; MySql > Subject: Re: Deadlock due lockwait. How can I tell mysql to wait > longer? > > Andrés, > > with pleasure. > > I

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Andrés, with pleasure. Imagine a website that is used to search, just for example, hotel rooms for booking. It is possible that a programmer would: 1) issue a select that returns the IDs the rooms matching the criteria 2) do a loop in the code scanning each ID of the resultset and for each ID i

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Claudio, would you please extend the example to the use of in? On Mon, May 14, 2012 at 10:08 AM, Claudio Nanni wrote: > In my experience if you have a poor designed code that run the same query > for hundreds or thousands of times in a very short timespan (like some > programmers do in for-loop

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

In my experience if you have a poor designed code that run the same query for hundreds or thousands of times in a very short timespan (like some programmers do in for-loop instead of using a IN for example) you can put mysql on its knees, in some cases it may be the practical implementation of some

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Argh. I meant to send this to the list but it doesn't have the reply-to set as I expect... On Mon, May 14, 2012 at 10:46 AM, Baron Schwartz wrote: > Johan, > > On Mon, May 14, 2012 at 9:27 AM, Johan De Meersman wrote: >> What I fail to understand, Baron, is how there can be a deadlock here - bo

RE: Deadlock due lockwait. How can I tell mysql to wait longer?

le indexes. In our case, the deletes/inserts statements were invoked by a stored procedure. David. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Monday, May 14, 2012 9:28 AM To: Baron Schwartz Cc: MySql Subject: Re: Deadlock due lockwait. How can I tell mys

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

- Original Message - > From: "Baron Schwartz" > > Because it can be resolved by rolling back just one of them. Why > destroy ALL the work people are trying to accomplish, if you could > just throw away some of it? What I fail to understand, Baron, is how there can be a deadlock here - bo

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

humm, I see.. and if is encapusulated with it's own begin-commit inside a bigger transacion, only that small part get rolled back... If I get this straigth... On Fri, May 11, 2012 at 2:32 PM, Baron Schwartz wrote: > Andres, > > On Fri, May 11, 2012 at 1:48 PM, Andrés Tello > wrote: > > Ok, so

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Andres, On Fri, May 11, 2012 at 1:48 PM, Andrés Tello wrote: > Ok, so I had a deadlock... > > But then, why a deadlock doesn't rollback all the transaccion? Because it can be resolved by rolling back just one of them. Why destroy ALL the work people are trying to accomplish, if you could just th

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

The genral log is the log that "logs" everything? humm... dunno if I can.. as always... stuuupid production server with no testing instance available... And it happens very seldom, but force us to do a select (sum) from the movements table instead just a select balance from account... On Fr

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Andrés, may be you can enable the general log, recreate the deadlock, and attach the general log? If I had to reason as InnoDB, what I see is two updates statements that arrive and want to update the same record, I would be confused exactly as InnoDB is because I would not know which update is th

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Yup, but a far I understand... I made a select balance for update where accountid=3235296 lock in shared mode; over the same accountid , so the second transacion just would need to wait to the first transaccion to finish... That is why I'm confuse if I have a Deadlock o a wait lock... That is

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Hello Andrés did you notice that both transactions are trying to update records with same *accountid='3235296' * and that they lock the same index page? *space id 5806 page no 69100 n bits 176 index* Cheers Claudio 2012/5/11 Andrés Tello > Ok, so I had a deadlock... > > But then, why a deadlo

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Ok, so I had a deadlock... But then, why a deadlock doesn't rollback all the transaccion? On Fri, May 11, 2012 at 9:55 AM, Baron Schwartz wrote: > Deadlocks and lock wait timeouts are independent of one another. A > deadlock happens when there is a cycle in the waits-for graph. Your > transact

Re: Deadlock due lockwait. How can I tell mysql to wait longer?

Deadlocks and lock wait timeouts are independent of one another. A deadlock happens when there is a cycle in the waits-for graph. Your transactions are *active* for 132 and 33 seconds, but the deadlock happens at the instant the conflict is detected, not after waiting. A deadlock cannot be resolved

Re: Deadlock

YES :) after removing CustomerID and OrderID from Primary index to unique index, every thing is working fine and I don't get any deadlock anymore :) Thanks Everyone :) On 11/16/06, Asif Lodhi <[EMAIL PROTECTED]> wrote: Hi Ahmad, On 11/13/06, Ahmad Al-Twaijiry <[EMAIL PROTECTED]> wrote: > al

Re: Deadlock

Hi Ahmad, On 11/13/06, Ahmad Al-Twaijiry <[EMAIL PROTECTED]> wrote: also I want to mention that I have 3 primary key in my table: ShopID CustomerID OrderID Could this be the problem ? Yes, may be. Cause I am sure I have read somewhere in the docs that having multiple cols in the primary key o

Re: Deadlock

- From: Ahmad Al-Twaijiry <[EMAIL PROTECTED]> To: Rolando Edwards <[EMAIL PROTECTED]> Cc: MySQL List Sent: Monday, November 13, 2006 10:39:02 AM GMT-0500 US/Eastern Subject: Re: Deadlock Sorry I have $dblink->commit(); right after $dblink->exec($sql); but I forgot to write i

Re: Deadlock

Sorry I have $dblink->commit(); right after $dblink->exec($sql); but I forgot to write it here (my mistake, sorry ) also I want to mention that I have 3 primary key in my table: ShopID CustomerID OrderID Could this be the problem ? On 11/13/06, Rolando Edwards <[EMAIL PROTECTED]> wrote: You

Re: Deadlock

You should play it safe and add $dblink->commit(); right after $dblink->exec($sql); The reason for this is from Page 419 of the MySQL 5.0 Certification Study Guide bullet point #3: During the course of a transaction, InnoDB may acquire row locks AS IT DISCOVERS THEM TO BE NECESSARY. I don't li

RE: deadlock - further information

> As 11 > 4, 4.1.11 is newer than 4.1.4, so this variable is available to you. Gawwddd... I really made an ass of myself that time... :o) Thanks for pointing it out though. > 4.1.11 is almost a year old. Have you read the list of bugs fixed since then? >

Re: deadlock - further information

Rithish Saralaya wrote: in 4.1.xx, you can use the my.cnf option innodb_locks_unsafe_for_binlog to make InnoDB to use a consistent read in the SELECT tables in CREATE ... SELECT. Read the caveats about the my.cnf option, though. Thanks. However, whatever is written in http://dev.mysql.com/do

Re: deadlock - further information

Rithish, - Original Message - From: ""Rithish Saralaya"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Monday, March 20, 2006 10:02 AM Subject: deadlock - further information --=_NextPart_000_0059_01C64C23.16088020 Content-Type: text/plain; charset="iso-8859-1" Co

Re: Deadlock found when trying to get lock; try restarting transaction

Hello. > BEGIN; > SELECT id FROM ttt WHERE id=3D7 FOR UPDATE; > INSERT INTO ttt(id) VALUES (7); > DELETE FROM ttt WHERE id=3D7; > COMMIT; Maybe we have this scenario: A, B - transactions. A -> SELECT ... FOR UPDATE - is setting an X lock on index for id=8 (next key locking, it is

Re: deadlock with innodb

Gleb Paharenko wrote: Hello. Among other things check that you correctly process lock timeouts. I've check this, but it seems fine. I'm testing it this way with 3 applications on the same computer (2 processors) : - 2 clients applications with an open connection to the DB (which is remote with si

Re: deadlock with innodb

Hello. Among other things check that you correctly process lock timeouts. > > Well, I'm sure it's a bug hidden somewhere in my apps, i've check with > another connexion and it worked ;) > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is spon

Re: deadlock with innodb

Philippe Poelvoorde wrote: Gleb Paharenko wrote: Hello. What transaction isolation level do you use? By the way - there's a fresh bug related to SELECT ... FOR UPDATE: http://bugs.mysql.com/bug.php?id=9512 tx_isolation is set to : REPEATABLE-READ (which is the default) I've stripped everything u

Re: deadlock with innodb

Gleb Paharenko wrote: Hello. What transaction isolation level do you use? By the way - there's a fresh bug related to SELECT ... FOR UPDATE: http://bugs.mysql.com/bug.php?id=9512 tx_isolation is set to : REPEATABLE-READ (which is the default) I've stripped everything uneeded from my code, and pri

Re: deadlock with innodb

Hello. What transaction isolation level do you use? By the way - there's a fresh bug related to SELECT ... FOR UPDATE: http://bugs.mysql.com/bug.php?id=9512 Philippe Poelvoorde <[EMAIL PROTECTED]> wrote: > Hello, > > here is a snippet of my code : > > BEGIN > SELECT ...

Re: deadlock caused by odbc and transaction handling

Robert, - Original Message - From: ""Bagyinszki Róbert"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, October 26, 2004 12:46 PM Subject: deadlock caused by odbc and transaction handling --_=_NextPart_001_01C4BB40.B000D6DB Content-Type: text/plain; charset="i

Re: Deadlock

Christophe, - Original Message - From: "Christophe Lombart" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Tuesday, November 04, 2003 10:45 PM Subject: Deadlock > Hello, > > I'm using MySQL 4.0.15 standard - InnoDB and I got a deadlock. I don't > see why ! > How Can I find

Re: deadlock problem?

Hi! It is not a deadlock. These two transactions have been dangling uncommitted for 14 hours: > ---TRANSACTION 0 713900169, ACTIVE 50231 sec, process no 17432, OS thread id > 98317 > 19 lock struct(s), heap size 2496, undo log entries 13 > MySQL thread id 9, query id 1213960 db2 10.10.7.2 web > T

Re: DeadLock bug using mysql/Innodb

Rafa, - Original Message - From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, November 05, 2002 11:00 AM Subject: DeadLock bug using mysql/Innodb > Description: > Hi Heikki, > > In your case: > > >CREATE TABLE t (a INT NOT NULL, PRIMARY KEY (a)) TYPE = INNODB;

Re: DeadLock bug using mysql/Innodb

Rafa, - Original Message - From: <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Monday, November 04, 2002 6:06 PM Subject: DeadLock bug using mysql/Innodb > Description: > Hello, > > I have a deadlock problem when I try to execute some update sentences > using 'Lock For U

Re: Deadlock errors - Can nobody help??!!

Hi. On Tue, Apr 30, 2002 at 04:54:27PM +0100, [EMAIL PROTECTED] wrote: [...] > >(From http://www.sleepycat.com/docs/ref/transapp/put.html - did not > >find an explanation in the MySQL manual :-( ) > > > This reference is very useful, actually. If I modify my test program to > detect the deadloc

Re: Deadlock errors - Can nobody help??!!

Benjamin Pflugmann wrote: >>>[...] Which table >>>type (MyISAM/InnoDB/BDB) do you use? Do you intend to use transactions >>>or not? >>> >>I am using BDB tables, and I am making use of transactions. >> >Okay, than that is the reason for the deadlocks and it is to be >expected, as far as I understa

Re: Deadlock errors - Can nobody help??!!

Hello. First, my apologies, if my answer sounded too harsh. It wasn't meant personally, but after answering ten or more mail with too few details, one gets somehow bored, you know. ;-) On Tue, Apr 30, 2002 at 09:29:41AM +0100, [EMAIL PROTECTED] wrote: [...] > and referred by date to the original

Re: Deadlock errors - Can nobody help??!!

Benjamin Pflugmann wrote: >Hi. > >Well, a perfect example of misunderstanding due to lack of >information. I interpreted your former description in a way that your >applications simply hangs. > My apologies for the sparse information in the e-mail to which you originally replied. It was actuall

Re: Deadlock errors - Can nobody help??!!

Hi. Well, a perfect example of misunderstanding due to lack of information. I interpreted your former description in a way that your applications simply hangs. Had you quoted the error message you got (or even told that you got one), it would have been more obvious that you meant a rollback due

Re: Deadlock errors - Can nobody help??!!

Benjamin Pflugmann wrote: >On Mon, Apr 29, 2002 at 08:41:16AM +0100, [EMAIL PROTECTED] wrote: > >>Can anybody help with the problem that I posted last week (26/04/2002 >>11:31) regarding deadlock errors via the Perl DBD::mysql module? >> >>To summarize, the following ridiculously simple program

Re: Deadlock errors - Can nobody help??!!

Hi. On Mon, Apr 29, 2002 at 08:41:16AM +0100, [EMAIL PROTECTED] wrote: > Hi, > > Can anybody help with the problem that I posted last week (26/04/2002 > 11:31) regarding deadlock errors via the Perl DBD::mysql module? > > To summarize, the following ridiculously simple program works fine when

RE: deadlock - Innodb Monitor

Gisella, At 04:24 PM 10/31/01 -0800, you wrote: >Heikki, > >The output I get from the Monitor does not have the section > >-- >LOCKS HELD BY TRANSACTIONS >-- > >that I see in the manual (www.mysql.com/doc), section 7.5.9.1, The InnoDB >Monitor. sor

RE: deadlock - Innodb Monitor

Heikki, The output I get from the Monitor does not have the section -- LOCKS HELD BY TRANSACTIONS -- that I see in the manual (www.mysql.com/doc), section 7.5.9.1, The InnoDB Monitor. I see two transactions issuing table locks to the same tables,

RE: deadlock - Innodb Monitor

keys generate secondary indexes? Regards, Gisella -Original Message- From: Miguel Angel Solórzano [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 2:38 PM To: Gisella Saavedra; [EMAIL PROTECTED] Cc: Miguel Angel Solórzano Subject: RE: deadlock - Innodb Monitor At 13:50 31/10

Re: deadlock - Innodb Monitor

Gisella, sorry, the printout in InnoDB Monitor is ambiguous in its words: non-debug version there means a version not compiled with #define UNIV_SYNC_DEBUG in univ.i The monitor in .44 does not use the ambiguous term any more. But you do not need semaphore info, only user lock info. When I sp

RE: deadlock - Innodb Monitor

ailto:[EMAIL PROTECTED]] >Sent: Wednesday, October 31, 2001 1:15 PM >To: Heikki Tuuri; [EMAIL PROTECTED] >Cc: [EMAIL PROTECTED]; Gisella Saavedra >Subject: Re: deadlock - Innodb Monitor > > >At 22:52 31/10/2001 +0200, Heikki Tuuri wrote: > >Heikki, > >For you to have

RE: deadlock - Innodb Monitor

MAIL PROTECTED]] Sent: Wednesday, October 31, 2001 1:15 PM To: Heikki Tuuri; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Gisella Saavedra Subject: Re: deadlock - Innodb Monitor At 22:52 31/10/2001 +0200, Heikki Tuuri wrote: Heikki, For you to have at the shell prompt the output is necessary the com

Re: deadlock - Innodb Monitor

Miguel, I can look at the file /mysql/data/mysql.err by using emacs. I do not need to stop the server. If I try a Windows tool like WordPad?, yes, it does not let me see the file. Heikki, I checked the task manager. I only have one mysqld running which is the mysqld-max-nt. I had mysqlgui

Re: deadlock - Innodb Monitor

At 22:52 31/10/2001 +0200, Heikki Tuuri wrote: Heikki, For you to have at the shell prompt the output is necessary the command: mysqld-max-net --standalone --console Like I show below: Microsoft Windows 2000 [Versão 5.00.2195] (C) Copyright 1985-1999 Microsoft Corp. C:\>cd\mysql\bin C:\mysql

Re: deadlock - Innodb Monitor

Gisella, >Heikki, >I looked at the mysql error log and all the stuff was there. >I was expecting it on the standard output as the documentation said. it should come to the standard output. I am sending a copy of this email to Miguel of MySQL AB. He should check why mysqld started as --standalone

Re: deadlock - Innodb Monitor

At 10:48 31/10/2001 -0800, Gisella Saavedra wrote: Hi! Take a look at /mysql/data/mysql.err for to see the output for innodb_monitor. I guess that you need to stop the server, for open this file. Regards, Miguel >Heikki, > >thanks for the reply. Regarding the trace output, My test has been >

Re: deadlock - Innodb Monitor

Heikki, I looked at the mysql error log and all the stuff was there. I was expecting it on the standard output as the documentation said. Thanks again. I'll have to restart the server again since the innodb monitor output on the error log says that I'm not running a debugable version, so there

Re: deadlock - Innodb Monitor

Gisella, >Heikki, > >thanks for the reply. Regarding the trace output, My test has been >"bombarding" the db server with 15 simultaneous clients, each >generating up to 1000 transaction sets. this is strange. When you start the server from the command promp, does it then print something to the

Re: deadlock - Innodb Monitor

Heikki, thanks for the reply. Regarding the trace output, My test has been "bombarding" the db server with 15 simultaneous clients, each generating up to 1000 transaction sets. I still do not get any output from Innodb Monitor. I created table innodb_monitor in the database that I'm accessing

Re: deadlock - Innodb Monitor

Gisella, .43 only prints info when there is MySQL server activity. I have changed it to .44 so that it always prints info every 15 seconds, regardless of server activity. If you want to study locks, then innodb_lock_monitor is the right tool. About your other question, I am afraid that timest