The XtraDB/InnoDB storage engine uses row-level locking to ensure data integrity. However some storage engines (such as MEMORY, MyISAM, Aria and MERGE) lock the whole table to prevent conflicts. These storage engines use two separate queues to remember pending statements; one is for
SELECTs and the other one is for write statements (
UPDATE). By default, the latter has a higher priority.
To give write operations a lower priority, the
low_priority_updates server system variable can be set to
ON. The option is available on both the global and session levels, and it can be set at startup or via the
When too many table locks have been set by write statements, some pending
SELECTs are executed. The maximum number of write locks that can be acquired before this happens is determined by the
max_write_lock_count server system variable, which is dynamic.
If write statements have a higher priority (default), the priority of individual write statements (
DELETE) can be changed via the
LOW_PRIORITY attribute, and the priority of a
SELECT statement can be raised via the
HIGH_PRIORITY attribute. Also,
LOCK TABLES supports a
LOW_PRIORITY attribute for
If read statements have a higher priority, the priority of an
INSERT can be changed via the
HIGH_PRIORITY attribute. However, the priority of other write statements cannot be raised individually.
The use of
HIGH_PRIORITY for an
INSERT prevents Concurrent Inserts from being used.