MySQL Bugs: #56046: 'SELECT ... FOR UPDATE' locked another table's index

Bug #56046	'SELECT ... FOR UPDATE' locked another table's index
Submitted:	17 Aug 2010 9:12	Modified:	18 Sep 2010 14:45
Reporter:	Lixun Peng (OCA)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.1.46sp1-enterprise-gpl-pro-log	OS:	Linux (Red Hat Enterprise Linux Server release 5.3 (Tikanga) x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	For Update, lock

Description:
When I make a performance test, I found 'SELECT ... FROM table_a FOR UPDATE' locked another table's index. This problem case a dead lock.

This is the table a:(xxx as a column name)
CREATE TABLE `resources` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `xxx` datetime NOT NULL,
  `xxx` datetime NOT NULL,
  `resource_id` varchar(150) NOT NULL,
  `resource_value` varchar(150) NOT NULL,
  `xxx` varchar(100) DEFAULT NULL,
  `xxx` char(1) NOT NULL DEFAULT '1',
  `client_address` varchar(100) NOT NULL,
  `server_address` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_resources_ResID_ResValue` (`resource_id`,`resource_value`),
  KEY `idx_resources_ResAdd_CliAdd` (`server_address`,`client_address`)
) ENGINE=InnoDB AUTO_INCREMENT=2188 DEFAULT CHARSET=utf8;

This is another table b:(xxx as a column name)
CREATE TABLE `resource_changes` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `xxx` datetime NOT NULL,
  `xxx` datetime NOT NULL,
  `resource_id` varchar(150) NOT NULL,
  `xxx` int(20) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_resource_changes_SerID` (`resource_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3838 DEFAULT CHARSET=utf8;

When I run 'Show InnoDB Status\G', I saw these messages:

*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
100817 16:44:51 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 94, signal count 83
Mutex spin waits 0, rounds 2816, OS waits 18
RW-shared spins 118, OS waits 54; RW-excl spins 43, OS waits 19
------------------------
LATEST DETECTED DEADLOCK
------------------------
100817 16:36:14
*** (1) TRANSACTION:
TRANSACTION 0 3083, ACTIVE 0 sec, process no 13335, OS thread id 1182140736 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1216, 4 row lock(s), undo log entries 2
MySQL thread id 107, query id 12636 10.16.198.71 dubbo_registry statistics
select id from resource_changes where resource_id = 'com.alibaba.demo.DemoService:1.0.491' for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 9 n bits 400 index `idx_resource_changes_SerID` of table `dubbo_registry`.`resource_changes` trx id 0 3083 lock_mode X locks rec but not gap waiting
Record lock, heap no 319 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 636f6d2e616c69626162612e64656d6f2e44656d6f536572766963653a31; asc com.alibaba.demo.DemoService:1;...(truncated); 1: len 4; hex 800001ee; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 0 3081, ACTIVE 0 sec, process no 13335, OS thread id 1182406976 starting index read
mysql tables in use 1, locked 1
16 lock struct(s), heap size 3024, 1342 row lock(s), undo log entries 418
MySQL thread id 105, query id 12642 10.16.198.71 dubbo_registry Sending data
select id from resources where server_address in ('10.16.198.71:9090') for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 9 n bits 400 index `idx_resource_changes_SerID` of table `dubbo_registry`.`resource_changes` trx id 0 3081 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 636f6d2e616c69626162612e64656d6f2e44656d6f536572766963653a31; asc com.alibaba.demo.DemoService:1;...(truncated); 1: len 4; hex 80000103; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 636f6d2e616c69626162612e64656d6f2e44656d6f536572766963653a31; asc com.alibaba.demo.DemoService:1;...(truncated); 1: len 4; hex 80000104; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 636f6d2e616c69626162612e64656d6f2e44656d6f536572766963653a31; asc com.alibaba.demo.DemoService:1;...(truncated); 1: len 4; hex 80000105; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 636f6d2e616c69626162612e64656d6f2e44656d6f536572766963653a31; asc com.alibaba.demo.DemoService:1;...(truncated); 1: len 4; hex 8000001d; asc     ;;

And so on...

Table 'resources' locked index idx_resource_changes_SerID of table 'resource_changes';

How to repeat:
Run Query First:
select id from resources where server_address in ('10.16.198.71:9090') for update

Run Query Second:
select id from resource_changes where resource_id = 'com.alibaba.demo.DemoService:1.0.491' for update

Then, you can see the bug.

please change   
`id` int(20) NOT NULL AUTO_INCREMENT,
to
`id` int(11) NOT NULL AUTO_INCREMENT,

and try again.

You may can temporary solve this problem using Per-Table Tablespaces.

Thank you for the report.

Are you sure first transaction does not use second table? What is the value of autocommit option? Do you use multiple-statement transactions like:

BEGIN;
query 1
query 2
...
query N
COMMIT;
?

I change int(20) to int(10), the problem is same, and I alread used per-table tablespace.
I'm sure I only use one table.

Thank you for the feedback.

What is the value of autocommit option? Do you use multiple-statement transactions like:

BEGIN;
query 1
query 2
...
query N
COMMIT;
?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".