MySQL Bugs: #68273: innodb lock all queries after a while. many threads, many locks

Bug #68273	innodb lock all queries after a while. many threads, many locks
Submitted:	5 Feb 2013 10:11	Modified:	5 Feb 2013 10:38
Reporter:	Azat Khuzhin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.60 and 5.5.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb, lock, mutex

Description:
After some time of mysqld server works normally, it lock all queries and not respond, include queries like 'SHOW CREATE TABLE'

Example of queries available at "full process list" section.

This information for 5.1.60, but 5.5.28 have the same issues.

backtrace #1:
-------------
http://pastebin.com/raw.php?i=MFnpQRV6

backtrace #2:
-------------
http://pastebin.com/raw.php?i=jR8ghR1X

full process list
-----------------
http://pastebin.com/raw.php?i=wCAL6v9M

innodb status
-------------
http://pastebin.com/raw.php?i=fC6jMbJh

mutex status
------------
In os_waits=0: 786770
In os_waits>=0: 202

my.cnf
-----
[mysqld]
max_connections=2000
datadir=/db/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
innodb_locks_unsafe_for_binlog=1
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_doublewrite=0
innodb_buffer_pool_size = 6G
innodb_log_file_size = 512M
innodb_file_per_table=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

How to repeat:
Run mysql server with my.cnf, and run queries like in my "full processlist", 100-200 parallel for 10-20-30 hours, and it must repeated.

Why do you believe this is a bug in MySQL ?

Queries are all waiting on "innodb_lock_wait_timeout", and you have updates with  subqueries with FOR UPDATE.  Locks are expected.  Additionally, index cannot be used to help you here: AND (@IDS := CONCAT_WS(',', id, @IDS).

I'd suggest to rewrite these update queries and check again.

Thanks for fast reply.

First of all, this query is faster than others, that we tried.
And UPDATE is pretty fast (< 1 sec.), so it must use index, and subquery (SELECT) also use index tested separately.

If we run such queries with 50 in parallel, and LIMIT 1, all works fine.
If we run such queries with 50-100 in parallel, and LIMIT 1, all works pretty good.
If we run such queries with 100 in parallel and LIMIT 1, after some time, it lock entire database.
If we run such queries with 100 in parallel and LIMIT 250, it lock entire database in 10 minutes.

And also how is this possible, that mysqld have so much mutexes?