MySQL Forums :: Transactions :: Problem: A SELECT ... FOR UPDATE statement locking all rows in a table

I've reached that same conclusion: either gap locking or next-key locking is causing the problem here in default REPEATABLE READ isolation level. Switching to READ COMMITTED level has solved my problem. However, I'm still intrigued why MySQL is locking all the rows in invoice_expenses but not in invoice_components because those two tables have the same kind of foreign key relationship with invoices table.

Just like I wrote in the original post, the first transaction is never committed or rolled back. I absolutely need to use SELECT ... FOR UPDATE in my case.

Like I said, I solved the problem by changing isolation level, but I'm still intrigued by the different behaviour of MySQL for two very similar tables.

My tables look like:


Table: invoices
Create Table: CREATE TABLE `invoices` (
`invoice_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice_number` varchar(255) NOT NULL,
`company` int(10) unsigned NOT NULL,
`invoice_type` int(10) unsigned NOT NULL,
`invoice_creation_time` int(10) unsigned NOT NULL,
`invoice_fiscal_year` smallint(5) unsigned NOT NULL,
`invoice_sum` double unsigned NOT NULL DEFAULT '0',
`invoice_total` double unsigned NOT NULL,
`invoice_realization_time` int(10) unsigned NOT NULL DEFAULT '0',
`invoice_booking_time` int(10) unsigned NOT NULL DEFAULT '0',
`invoice_payment_time` int(10) unsigned NOT NULL DEFAULT '0',
`invoice_payment_amount` double unsigned NOT NULL DEFAULT '0',
`invoice_discount` double unsigned NOT NULL DEFAULT '0',
`currency` int(10) unsigned DEFAULT NULL,
`invoice_exchange_rate` double unsigned NOT NULL DEFAULT '0',
`tax_rate` int(10) unsigned NOT NULL DEFAULT '0',
`created_by` int(10) unsigned NOT NULL,
`booked_by` int(10) unsigned NOT NULL DEFAULT '0',
`invoice_components_number` int(10) unsigned NOT NULL DEFAULT '0',
`invoice_warehouse_order_time` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`invoice_id`),
KEY `company` (`company`),
KEY `invoice_time` (`invoice_creation_time`),
KEY `invoice_credited_time` (`invoice_booking_time`),
KEY `invoice_payment_time` (`invoice_payment_time`),
KEY `invoice_payment_amount` (`invoice_payment_amount`),
KEY `invoice_type` (`invoice_type`),
KEY `created_by` (`created_by`),
KEY `credited_by` (`booked_by`),
KEY `currency` (`currency`),
KEY `invoice_number` (`invoice_number`),
KEY `invoice_sum` (`invoice_sum`),
KEY `invoice_total` (`invoice_total`),
KEY `invoice_fiscal_year` (`invoice_fiscal_year`),
CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`invoice_type`) REFERENCES `invoice_types` (`invoice_type_id`),
CONSTRAINT `invoices_ibfk_2` FOREIGN KEY (`currency`) REFERENCES `currencies` (`currency_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8



Table: invoice_components
Create Table: CREATE TABLE `invoice_components` (
`invoice_component_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice_component_order` int(10) unsigned NOT NULL DEFAULT '0',
`invoice` int(10) unsigned NOT NULL,
`product` int(10) unsigned NOT NULL,
`storage_item` int(10) unsigned NOT NULL DEFAULT '0',
`device` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'deprecated',
`part` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'deprecated',
`consumable` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'deprecated',
`invoice_component_amount` double unsigned NOT NULL,
`invoice_component_value` double unsigned NOT NULL,
`invoice_component_tax_rate` int(10) unsigned NOT NULL,
`storage_change` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`invoice_component_id`),
KEY `invoice` (`invoice`),
KEY `device` (`device`),
KEY `part` (`part`),
KEY `consumable` (`consumable`),
KEY `storage_change` (`storage_change`),
KEY `product` (`product`),
KEY `invoice_component_order` (`invoice_component_order`),
KEY `invoice_component_tax_rate` (`invoice_component_tax_rate`),
CONSTRAINT `invoice_components_ibfk_1` FOREIGN KEY (`invoice`) REFERENCES `invoices` (`invoice_id`) ON DELETE CASCADE,
CONSTRAINT `invoice_components_ibfk_2` FOREIGN KEY (`invoice_component_tax_rate`) REFERENCES `tax_rates` (`tax_rate_id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8



Table: invoice_expenses
Create Table: CREATE TABLE `invoice_expenses` (
`expense_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice` int(10) unsigned NOT NULL,
`expense_type` int(10) unsigned DEFAULT NULL,
`expense_tax_rate` int(10) unsigned DEFAULT NULL,
`expense_amount` double unsigned NOT NULL,
PRIMARY KEY (`expense_id`),
KEY `invoice` (`invoice`),
KEY `expense_type` (`expense_type`),
KEY `expense_tax_rate` (`expense_tax_rate`),
CONSTRAINT `invoice_expenses_ibfk_3` FOREIGN KEY (`expense_tax_rate`) REFERENCES `tax_rates` (`tax_rate_id`),
CONSTRAINT `invoice_expenses_ibfk_1` FOREIGN KEY (`invoice`) REFERENCES `invoices` (`invoice_id`) ON DELETE CASCADE,
CONSTRAINT `invoice_expenses_ibfk_2` FOREIGN KEY (`expense_type`) REFERENCES `invoice_expense_types` (`expense_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8