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
