Hi! Ok, now I think I understand the problem. Since you use the -q option, the client mysql retrieves rows in the result set in chunks of some size. As InnoDB keeps the adaptive hash index latch S-locked till the end of the the big
SELECT ... ORDER BY ... query, it will probably be S-latched when you start retrieving the rows. And then you cannot perform much further operations in the database because of the S-latch! As you pointed out, a workaround would be to remove the -q option, since then mysql would retrieve all rows in one chunk and release the S-latch. Hmm... the underlying problem is that InnoDB does not know when mysqld moves control to the client, and does not know to release the S-latch then. I have to check if I can see when mysql_use_result() is used to retrieve the result in smaller chunks. I could then release the S-latch always when the program control leaves InnoDB. Thank you, Heikki ----- Original Message ----- From: "Heikki Tuuri" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, March 03, 2003 10:24 AM Subject: Re: innodb deadlock leads to server crash > Hi! > > Your email address gives the error: > > " > DNS for host dev.noris.de is mis-configured > > The following recipients did not receive this message: > > <[EMAIL PROTECTED]> > " > > Still one question: is this a deadlock of threads at all? Maybe the sorting > which mysqld does, or the fetches which: > > mysql -q ... | program > > executes take so long that InnoDB thinks the server has hung? How big is the > result set of your ORDER BY query in terms of rows and megabytes? > > Regards, > > Heikki > > > ----- Original Message ----- > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Monday, March 03, 2003 10:05 AM > Subject: Re: innodb deadlock leads to server crash > > > > Hi! > > > > A deadlock of threads is a bug. It is is not connected to transactions or > > multiversioning. > > > > Is the problem repeatable in your computer? > > > > Can you compile a debug version of mysqld? Go to the source tree root > > directory /mysql/ and do: > > > > ./BUILD/compile-pentium-debug-max > > > > Then run the compiled /mysql/sql/mysqld inside gdb. When it hangs do: > > > > (gdb) info threads > > > > (gdb) thread 1 > > > > (gdb) bt full > > > > ... and so on for all threads. Send the output to me. > > > > You could also try an official MySQL binary you can download from > > www.mysql.com. Your build platform gcc-2.96 + Red Hat 7.0 is somewhat > > suspicious and might produce broken binaries. > > > > I tried to repeat the hang in my computer, but did not succeed. Can you > send > > me what > > > > SHOW CREATE TABLE ticketlast; > > > > prints? > > > > Best regards, > > > > Heikki Tuuri > > Innobase Oy > > --- > > Order technical MySQL/InnoDB support at https://order.mysql.com/ > > See http://www.innodb.com for the online manual and latest news on InnoDB > > > > sql query > > > > > > ----- Original Message ----- > > From: <[EMAIL PROTECTED]> > > Newsgroups: mailing.database.mysql > > Sent: Monday, March 03, 2003 3:58 AM > > Subject: innodb deadlock leads to server crash > > > > > > > >Description: > > > A deadlock within innodb leads to a server crash. > > > > > > I have a large table which I need to update in-place. So one mysql > > > connection does a SELECT, and another updates the data. I thought > > > that, since innodb supports transactions and multiversioning, the > > > two should not block each other. > > > > > > Apparently, this is wrong and leads to a server crash. > > > > > > The relevant output from mysqld's server log is this: > > > ---------- > > > SEMAPHORES > > > ---------- > > > OS WAIT ARRAY INFO: reservation count 13947, signal count 13945 > > > --Thread 13326 has waited at btr0sea.c line 448 for 499.00 seconds the > > semaphore: > > > X-lock on RW-latch at 40172668 created in file btr0sea.c line 128 > > > a writer (thread id 13326) has reserved it in mode wait exclusive > > > number of readers 1, waiters flag 1 > > > Last time read locked in file btr0sea.c line 683 > > > Last time write locked in file btr0sea.c line 1117 > > > --Thread 7176 has waited at btr0sea.c line 863 for 490.00 seconds the > > semaphore: > > > S-lock on RW-latch at 40172668 created in file btr0sea.c line 128 > > > a writer (thread id 13326) has reserved it in mode wait exclusive > > > number of readers 1, waiters flag 1 > > > Last time read locked in file btr0sea.c line 683 > > > Last time write locked in file btr0sea.c line 1117 > > > Mutex spin waits 662, rounds 8840, OS waits 94 > > > RW-shared spins 31791, OS waits 13822; RW-excl spins 34, OS waits 31 > > > ------------ > > > TRANSACTIONS > > > ------------ > > > Trx id counter 0 2108142 > > > Purge done for trx's n:o < 0 2108136 undo n:o < 0 0 > > > Total number of lock structs in row lock hash table 0 > > > LIST OF TRANSACTIONS FOR EACH SESSION: > > > ---TRANSACTION 0 2107229, not started, OS thread id 10251 > > > MySQL thread id 28, query id 881 gemini.office.noris.de 10.2.0.132 > updater > > > ---TRANSACTION 0 2107136, not started, OS thread id 9226 > > > MySQL thread id 23, query id 815 gemini.office.noris.de 10.2.0.132 > updater > > > ---TRANSACTION 0 2108141, ACTIVE 499 sec, OS thread id 13326 starting > > index read > > > MySQL thread id 30, query id 1484 gemini.office.noris.de 10.2.0.132 > > updater preparing > > > select timestamp,seq,wann from ticketlast where ticket = '1823' and > > person = '3 > > > 406' > > > ---TRANSACTION 0 2108137, ACTIVE 507 sec, OS thread id 11276 > > > , holds adaptive hash latch > > > MySQL thread id 29, query id 1481 gemini.office.noris.de 10.2.0.132 > > updater Sending data > > > select timestamp,ticket,person from ticketlast where timestamp >= > > FROM_UNIXTIME (916760612) order by ticket,person > > > Trx read view will not see trx with id >= 0 2108138, sees < 0 2108138 > > > -------- > > > > > > >How-To-Repeat: > > > Create a table with suitably many records. > > > mysql -q -e "select id from FOO order by id" | program > > > > > > The program would do a loop with "select * from FOO where id=$ID", > > > and do an occasional UPDATE. > > > > > > >Fix: > > > the two processes should not block each other. > > > Dropping the "-q" is not a solution; the table is too large. > > > > > > > > > >Submitter-Id: <submitter ID> > > > >Originator: > > > >Organization: > > > noris network AG, Nuernberg, Germany > > > >MySQL support: license > > > >Synopsis: innodb deadlock > > > >Severity: serious > > > >Priority: high > > > >Category: mysql > > > >Class: sw-bug > > > >Release: mysql-3.23.56 > > > > > > >Environment: > > > System: Linux dev1.dev.noris.de 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 2002 > > i686 unknown > > > Architecture: i686 > > > > > > Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc > > /usr/bin/cc > > > GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs > > > gcc version 2.96 20000731 (Red Hat Linux 7.0) > > > Compilation info: CC='gcc' CFLAGS='-g -O2' CXX='g++' > > > CXXFLAGS='-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtt > > i' LDFLAGS='' > > > LIBC: > > > lrwxrwxrwx 1 root root 13 Jun 6 2002 /lib/libc.so.6 -> > > libc-2.2.5.so > > > -rwxr-xr-x 1 root root 1260480 Apr 15 2002 > /lib/libc-2.2.5.so > > > -rw-r--r-- 1 root root 2310808 Apr 15 2002 /usr/lib/libc.a > > > -rw-r--r-- 1 root root 178 Apr 15 2002 /usr/lib/libc.so > > > -rwxr-xr-x 1 root root 2194520 Feb 6 12:32 > > /usr/lib/libc-client.a > > > Configure command: ./configure '--prefix=/usr' '--without-debug' > > '--enable-shared' '--without-mit-threads' '--libexecdir=/usr/sbin' > > '--localstatedir=/var/mysql' '--enable-thread-safe-client' > > '--sysconfdir=/etc' '--datadir=/usr/share' '--enable-large-files' > > '--without-readline' '--with-mysqld-user=mysql' > > '--with-unix-socket-path=/var/run/mysql.socket' '--enable-strcoll' > > '--with-comment=noris network MySQL' '--with-docs' '--with-bench' > > '--without-berkeley-db' '--without-bench' '--with-innodb' > > 'CPPFLAGS=-DTHREAD_SAFE_CLIENT' > > > 'CXXFLAGS=-DTHREAD_SAFE_CLIENT -felide-constructors -fno-exceptions -fno-rtt > > i' > > > > > > > > > --------------------------------------------------------------------- > > > 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
