Write Lock Priority

[oredigger]

I'm curious about write locks with MySQL and how they are prioritized with eachother. Specifically, I'm wondering if "low priority write" locks have lower priority than "normal" write locks, or if they are equal. The only documentation I can find states that the low priority locks have lower priority than read locks, allowing read locks to process first. But I can't find anything that states how they relate to other write locks.

The point of this is that I'm looking for a way to "prioritize" write locks, and wondering if just the standard low priority lock will do what I'm looking for.

Thanks!

[cereal]

Not sure if this helps, I checked the source of thr_lock.c as suggested here: http://dev.mysql.com/doc/refman/5.5/en/table-locking.html

It seems this depends on max_write_lock_count, once reached the limit, all the pending read locks are released. But it appears possible to change the behaviour of the queue system. This is what is written in the comments:

The current lock types are:
TL_READ         # Low priority read
TL_READ_WITH_SHARED_LOCKS
TL_READ_HIGH_PRIORITY   # High priority read
TL_READ_NO_INSERT   # Read without concurrent inserts
TL_WRITE_ALLOW_WRITE    # Write lock that allows other writers
TL_WRITE_CONCURRENT_INSERT
            # Insert that can be mixed when selects
TL_WRITE_DELAYED    # Used by delayed insert
            # Allows lower locks to take over
TL_WRITE_LOW_PRIORITY   # Low priority write
TL_WRITE        # High priority write
TL_WRITE_ONLY       # High priority write
            # Abort all new lock request with an error

Locks are prioritized according to:
WRITE_ALLOW_WRITE, WRITE_CONCURRENT_INSERT, WRITE_DELAYED,
WRITE_LOW_PRIORITY, READ, WRITE, READ_HIGH_PRIORITY and WRITE_ONLY

Locks in the same privilege level are scheduled in first-in-first-out order.

Lock types matches SELECT, INSERT, etc. modes:

• SELECT [HIGH_PRIORITY]
• INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY]
• INSERT [LOW_PRIORITY | HIGH_PRIORITY] SELECT
• UPDATE [LOW_PRIORITY]

The prioritized list seems to be in ascending order. So, according to this list, there's an order also in the write queue, where WRITE_ONLY is the higher privilege, however you can change the priority of a write lock to a lower level, check thr_downgrade_write_lock() starting at line ~1256. And check line ~682 for exceptions.

If you are in Debian/Ubuntu you can get the source by typing:

sudo apt-get source mysql-5.5

This command will download and expand the source code archive, so be sure to run it inside a clean directory, to avoid mishmash.