>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-rtti' 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-rtti'
---------------------------------------------------------------------
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
