MySQL Bugs: #54058: TRIGGER causes table lock for SELECT or UPDATE query with InnoDB plugin

Bug #54058	TRIGGER causes table lock for SELECT or UPDATE query with InnoDB plugin
Submitted:	28 May 2010 10:05	Modified:	12 Dec 2010 0:16
Reporter:	Petr Valkoun	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S2 (Serious)
Version:	MySQL 5.1.47 GA	OS:	Linux (OpenSUSE 11.2 x64)
Assigned to:		CPU Architecture:	Any
Tags:	innodb, InnoDB plugin, table lock, trigger

Description:
Hello,

we have table locking problems on our production server. We have InnoDB tables with READ-UNCOMITED in highly concurrent enviroment. Main 'merged' table have about 10M rows with around 12GB size. There is around 100 clients which are updating or inserting (using REPLACE INTO) rows, no deletes. From time to time we need to UPDATE lots of rows in one query which takes about 30min and 3x a day we do some SELECT to fetch latest rows which takes around 1hour. So far no problems, no table locking, everyone is happy.

Lately we decided we need to keep track of newly updated or inserted rows in separate 'fresh' table, so we added two TRIGGERs for update and insert, which simply copy the updated or inserted row to another InnoDB table. Since that, every time we run our big UPDATE or even SELECT query, after some minutes of run, both (!) tables become locked and no one can use them except of the running UPDATE query. When we disabled triggers, problem disappeared. I understand this is a bug, because in READ-UNCOMMITTED no SELECT should create any kind of lock, especially not table lock, especially not on another table (on both in fact).

How to repeat:
Run many concurrent updates and inserts on 'merged' table and then execute one of problematic queries (at the bottom).

MySQL settings:

innodb_locks_unsafe_for_binlog ON
tx_isolation READ-UNCOMMITTED
InnoDB ha_innodb_plugin.so as part of official package in MySQL 5.1.47
innodb_version 1.0.8
innodb_strict_mode OFF
innodb_table_locks ON

-------------------------------------
structure od 'merge' and 'fresh' table structure:

CREATE TABLE `merged_data_tb` (
	`crawldatetime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	`irowid` INT(11) NOT NULL,
	`crawlpage` CHAR(5) NOT NULL DEFAULT '',
	`make_model` VARCHAR(60) NOT NULL,
	`acid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
	`EZ_origional` CHAR(6) NOT NULL,
	`colour` VARCHAR(30) NOT NULL,
	`KM` MEDIUMINT(7) UNSIGNED NOT NULL DEFAULT '0',
	`engine_KW` SMALLINT(4) UNSIGNED NOT NULL DEFAULT '0',
	`price` MEDIUMINT(6) UNSIGNED NOT NULL DEFAULT '0',
	`options_field` CHAR(100) NOT NULL,
	`CountryPLZ` VARCHAR(7) NOT NULL,
	`source_paramvalues` TEXT NOT NULL,
	`full_webaddress` TEXT NOT NULL,
	`totalparsetext` VARCHAR(200) NOT NULL DEFAULT '',
	`overview_path` TEXT NOT NULL,
	`vendor_address` VARCHAR(255) NOT NULL DEFAULT '',
	`detail_indicator` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
	`commision_no` VARCHAR(100) NOT NULL,
	`md5Hash` CHAR(32) NOT NULL DEFAULT 'ffffffffffffffffffffffffffffffff',
	`kw1_ps0` ENUM('KW','PS','NA') NOT NULL DEFAULT 'KW',
	`sql_ready` BIT(1) NOT NULL,
	`sql_1_finish` BIT(1) NOT NULL,
	`sql_2_finish` BIT(1) NOT NULL,
	`sql_runner` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`picHash` CHAR(32) NULL DEFAULT NULL,
	`picDatetime` BIT(1) NOT NULL,
	`body_type` ENUM('0','1','2','3','4','5','6','7','8','9','10') NOT NULL DEFAULT '0',
	`doors` ENUM('0','2.5','4.5') NOT NULL DEFAULT '0',
	`fuel_type` ENUM('0','1','2','3','4','5','6','7','8','9','10') NOT NULL DEFAULT '0',
	`gear_box_type` ENUM('0','1') NOT NULL DEFAULT '0',
	`fuel_consumption` DOUBLE UNSIGNED NOT NULL DEFAULT '0',
	`cylinder_capacity` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
	`co2_emission` DOUBLE UNSIGNED NOT NULL DEFAULT '0',
	`car_price_km_history` TEXT NOT NULL,
	`currency` CHAR(3) NOT NULL DEFAULT 'DFT',
	`recrawl` ENUM('Y','N') NOT NULL DEFAULT 'N',
	`deleteindikator` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
	`deleteinfo` TEXT NOT NULL,
	PRIMARY KEY (`sql_runner`),
	UNIQUE INDEX `md5Hash_2` (`md5Hash`, `price`, `KM`),
	UNIQUE INDEX `picHash` (`picHash`),
	INDEX `price` (`price`),
	INDEX `KM` (`KM`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1844674407615925465

-------------------------------------
TRIGGERs:

CREATE TRIGGER ac_life_trigger_update BEFORE UPDATE ON merged_data_tb
     FOR EACH ROW
     BEGIN
      IF NEW.recrawl = 'N' THEN
         SELECT COUNT(*) INTO @presentAcid FROM car_model_master_db.make_model_reference_tb WHERE rowid_refmodel = OLD.acid;
         INSERT IGNORE INTO merged_data_tb_fresh
         SELECT * FROM merged_data_tb
         WHERE sql_runner = OLD.sql_runner
         AND NEW.crawldatetime IS NOT NULL
         AND NEW.crawldatetime > OLD.crawldatetime
         AND NEW.crawldatetime >= DATE_ADD(NOW(),INTERVAL -3 DAY)
         AND (@presentAcid * acid) = 0
         AND acid <> 1;
         UPDATE merged_data_tb_fresh SET crawldatetime = NOW() WHERE sql_runner = OLD.sql_runner;
      END IF;
END

CREATE TRIGGER ac_life_trigger AFTER INSERT ON merged_data_tb
     FOR EACH ROW
     BEGIN
     SELECT COUNT(*) INTO @presentAcid FROM car_model_master_db.make_model_reference_tb WHERE rowid_refmodel = NEW.acid;
     INSERT IGNORE INTO merged_data_tb_fresh
     SELECT * FROM merged_data_tb
     WHERE md5Hash = NEW.md5Hash
           AND KM = NEW.KM
           AND price = NEW.price
     AND (@presentAcid * NEW.acid) = 0
     AND NEW.acid <> 1
     AND IF(NEW.crawldatetime IS NULL, 1, NEW.crawldatetime >= DATE_ADD(NOW(),INTERVAL -3 DAY))
     ;
END

-------------------------------------
Problematic UPDATE query:

UPDATE  merged_data_tb SET recrawl = 'Y' WHERE LEFT(crawlpage,2) IN ('lc','m5','gt','ma','ck');

#yes, there is no index in WHERE clause, because full table scan is faster than using index because of IO driven slow index usage for more than some % of rows compared to relatively fast sequential access of full table scan

Problematic SELECT query: 

SELECT sql_runner AS id FROM merged_data_tb WHERE LEFT(crawlpage,2) IN ('lc','m5','gt','ma','ck');

-------------------------------------
Server: Intel C2D,8GB RAM, MD RAID 10 (3xRAID1), OpenSUSE 11.2 x64 with MySQL 5.1.47 rpms for SLES 11 x64 from official MySQL website.

Suggested fix:
Fix TRIGGER implementation so it doesnt lock tables

corrected english

some more english fix

Thank you for the problem report. Please, send the results of:

show innodb status\G

at the moment when you see this locking problem.

This is the status output when executing UPDATE and all tables are locked

Attachment: status.log (application/octet-stream, text), 10.49 KiB.

Sorry, this is the correct file

Attachment: status2.log (application/octet-stream, text), 62.59 KiB.

The table locking problem has been solved by removing AUTO_INCREMENT default value in `fresh` table. Is that a feature, or a bug? How is is possible that this caused table locks on both tables? 

My TRIGGERs are copying updated or inserted rows in `merged` table to `fresh` tables. The AUTO_INCREMENT feature of `sql_runner` column in `fresh` table was never used, because all inserted rows already has some value. Why it caused locks of both tables then?

I believe its a BUG.

So I was wrong, tables are still getting locked by UPDATE command plus now even by SELECT...INTO TEMPORARY TABLE! thats ridiculous, no SELECT should cause table lock in READ_UNCOMMITED right? I will upload another status dump soon, thans for trying to figure it out.

tables locked because of SELECT statement

Attachment: status4.log (application/octet-stream, text), 59.86 KiB.

Thank you for the feedback.

I can not repeat described behavior using test data. Also I don't see any query like "UPDATE IGNORE merged_data_tb SET recrawl = 'Y' WHERE LEFT(crawlpage,2) IN ('lc','m5','gt','ma','ck');" in the attached status.

Please try with current version 5.0.51 and if problem still exists try to create repeatable test case.

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".

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".