MySQL Bugs: #26162: Trigger DML ignores low_priority

Bug #26162	Trigger DML ignores low_priority_updates setting
Submitted:	7 Feb 2007 20:27	Modified:	18 Jun 2007 17:35
Reporter:	Harrison Fisk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.34	OS:	Linux (Linux 2.6.15 (Ubuntu 6.06 LTS))
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	bfsm_2007_02_15, low_priority_updates, trigger

Description:
When you have a DML statement, such as an INSERT inside of a trigger, it will automatically always get a high priority lock, even if low_priority_updates is set.  This will prevent selects from running while they block on the high priority data change.

It is possible to still set it to be low priority in the trigger itself, via the LOW_PRIORITY hint to the statement, so a workaround is available.

How to repeat:
Connection 1:

SET GLOBAL LOW_PRIORITY_UPDATES=1;
SET SESSION LOW_PRIORITY_UPDATES=1;

DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;

CREATE TABLE t1 (id int) engine=myisam;
CREATE TABLE t2 (id int) engine=myisam;

# trigger that produces the high priority insert, but should be low, adding LOW_PRIORITY fixes this
CREATE TRIGGER t1_test AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (new.id);

INSERT INTO t1 VALUES (5), (10);

Connection 2:

# acquire read lock on table
SELECT SLEEP(30) FROM t2;

Connection 1:

#waits for connection 2 to finish read lock
INSERT INTO t1 VALUES (15);

Connection 3:

# waits for Connection 1 to finish it's write lock, even though it shouldn't have to
SELECT * FROM t2;

mysqladmin debug will also show you that the trigger INSERT has high priority similar to:

Thread database.table_name          Locked/Waiting        Lock_type

1       test.t2                     Locked - read         Low priority read lock
2       test.t2                     Waiting - write       High priority write lock
2       test.t1                     Locked - write        Concurrent insert lock

Suggested fix:
Honor the low_priority_updates option in the triggered INSERT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27462

ChangeSet@1.2504, 2007-05-28 15:43:22+03:00, gkodinov@macbook.gmz +4 -0
  Bug #26162: 
  The value of "low-priority-updates" option and the LOW PRIORITY 
  prefix was taken into account at parse time.
  This caused triggers (among others) to ignore this flag (if 
  supplied for the DML statement).
  Moved reading of the LOW PRIORITY flag at run time.
  Fixed an incosistency when handling 
  SET GLOBAL LOW_PRIORITY_UPDATES : now it is in effect for
  delayed INSERTs.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27535

ChangeSet@1.2504, 2007-05-29 10:59:06+03:00, gkodinov@magare.gmz +6 -0
  Bug #26162: Trigger DML ignores low_priority_updates setting
  
  The value of "low-priority-updates" option and the LOW PRIORITY
  prefix was taken into account at parse time.
  This caused triggers (among others) to ignore this flag (if
  supplied for the DML statement).
  Moved reading of the LOW_PRIORITY flag at run time.
  Fixed an incosistency when handling
  SET GLOBAL LOW_PRIORITY_UPDATES : now it is in effect for
  delayed INSERTs.
  Tested by checking the effect of LOW_PRIORITY flag via a 
  trigger.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Noted in 5.0.44, 5.1.20 changelogs.

Statements within triggers ignored the value of the
low_priority_updates system variable.

See also Bug#29936 Stored Procedure DML ignores low_priority_updates setting

Pushed into 5.1.22-beta

Pushed into 5.0.48

Please ignore the last 2 entries added automatically.

This bug was resolved in 5.0.44 and 5.1.20.