MySQL Database Design and Tuning

Concurrency refers to the ability of multiple database-accessing applications and processes to peacefully coexist at the same time, with no application consuming more than its necessary and fair share of system resources. Good concurrency is more conspicuous by its absence than its presence: No users ever complain when a system is highly concurrent, but imagine the noise when a key database resource is locked indefinitely.

What makes MySQL's locking behavior somewhat difficult to understand is that there are general MySQL locking concepts as well as InnoDB-specific notions. Further complicating matters is that although much of MySQL's locking activities happen automatically, developers can take many steps to influence these actions. To help address this potential confusion, this section explains locking from both the general and InnoDB perspectives, along with how SQL statements can drive locking activities.

General Locking Overview

To begin the locking discussion, it's a good idea to examine locks from the top-level perspective of MySQL itself, including the granularity of all available locks, the types of locks offered, and the concurrency options for these locks.

Lock Granularity

Each MySQL storage engine offers different levels of granularity for their locks. In decreasing granularity (that is, from largest lockable object to smallest), they are as follows:

• Table locks Supported by the MyISAM, MEMORY, and InnoDB storage engines, these restrict access to an entire table. Their effects can be mitigated by the LOCAL and LOW_PRIORITY options available for READ and WRITE locks, respectively.

• Page locks Provided by the BDB storage engine, these locks confine their effects to only those data and index details resident on a particular page. Because this chapter does not cover the BDB engine, page locks are not covered here.

• Row locks A row-level lock hones in on a particular record, leaving all other rows within a table free for others to access and modify. The InnoDB storage engine offers this kind of lock: You can also obtain a table lock for an InnoDB-hosted table, but you should take care to first commit or roll back your transaction before explicitly releasing the lock via the UNLOCK TABLES command.

Lock Types

Broadly speaking, MySQL-level locks fall into one of two classes:

• READ locks By placing a READ lock on a table (via the LOCK TABLES statement), you restrict other users from altering information in the affected table until you release the lock via the UNLOCK TABLES statement. If you have included the LOCAL option with your LOCK TABLES statement, other processes are able to execute concurrent, nonconflicting INSERT operations at the same time. After issuing a READ lock request, your application can count on no data changes (other than inserts if LOCAL was specified) for the duration of the lock.

• WRITE locks Designed to safeguard data modifications, when issued a WRITE lock prevents all other processes from altering information in the table. By including the LOW_PRIORITY directive, you instruct MySQL to wait until all other activities that request READ locks have completed prior to obtaining your WRITE lock.

When to Explicitly Request Table Locks

In most cases, there are only two scenarios in which a database developer or administrator should intercede and overtly request one or more table locks. Before citing these cases, it's important that you recognize the risks inherent in this strategy. These dangers include greatly diminished concurrency as well as the potential for data-integrity problems should something go wrong in the middle of an operation.

The first setting occurs when it's vital that a series of database events happen without interference. This is typically handled in a transaction, but there might be times that you are using a nontransactional storage engine yet need this capability. In these cases, locking a table for the duration of your alterations effectively provides you with transactional behavior. However, because the built-in rollback features found in a transactional storage engine are lacking, it is not easy to undo any changes applied to your tables.

The second situation transpires when you want to coax additional performance from your MyISAM tables during large-scale operations. Locking these tables effectively reduces the amount of overhead necessary to complete these activities, but at the cost of significantly reduced concurrency.

InnoDB Locking Overview

Because InnoDB offers full transactional support, it stands to reason that its locking functionality is more substantial than that found across all MySQL storage engines. This is indeed the case: There are additional lock considerations and capabilities that developers should keep in mind when deploying applications that use this storage engine.

This section begins with an exploration of InnoDB's lock concurrency properties, and then moves on to explain (at a high level) some of InnoDB's locking algorithms. Because there is a strong interplay among locks, SQL statements, transactions, and transaction isolation levels, the section closes with an examination of how specific types of SQL statements and operations leverage locks.

Before starting the discussion, review the following two tables, along with rows from the first table. This chapter periodically refers to these very simple tables to help illustrate a concept.

CREATE TABLE vip ( id INTEGER PRIMARY KEY AUTO_INCREMENT, last_name VARCHAR(30) NOT NULL, vip_level ENUM ('Tin','Silver','Gold'), INDEX(last_name) ) ENGINE = INNODB; CREATE TABLE vip_gifts ( id INTEGER PRIMARY KEY AUTO_INCREMENT, vip_id INTEGER NOT NULL, gift_date DATE NOT NULL, gift_description VARCHAR(80) NOT NULL, INDEX(gift_date), FOREIGN KEY (vip_id) references vip(id) ) ENGINE = INNODB; mysql> SELECT * FROM vip; +----+-----------+-----------+ | id | last_name | vip_level | +----+-----------+-----------+ | 1 | Adir | Gold | | 2 | Bass | Silver | | 3 | Crocker | Silver | | 4 | Dietrich | Tin | | 5 | Egan | Tin | | 6 | Fish | Silver | | 7 | Lapexin | Gold | | 8 | Ramystein | Tin | | 9 | Savedien | Gold | | 10 | Zlotnick | Gold | +----+-----------+-----------+

Lock Concurrency

Whether a lock is set implicitly by MySQL, or explicitly by a user or application, it has a number of characteristics. As you have seen, these include its scope (that is, granularity) as well as whether it is a read or write lock. For locks in the InnoDB storage engine, one additional property is its concurrency, which can be either exclusive or shared. An exclusive lock prevents any other users from obtaining the same kind of lock on the object in question. On the other hand, a shared lock means that other users can obtain the exact same type of lock at the same time.

Row-level Locking Scope

In Chapter 12, "InnoDB Performance Enhancement," which focuses on improving InnoDB performance, you learn that this engine internally stores all rows in a clustered index. In those situations in which you have not defined an index for the table, one is created for you automatically.

Many types of operations cause InnoDB to set row-level locks. Depending on the type of procedure under way, it chooses among several row locking tactics:

• Locking the index entry for the row itself This is known as a "non-next-key" lock.

• Locking the gap (that is, the space) in the index immediately prior to the row Known as a "gap" lock, this prevents other rows from being placed into that position for the duration of the lock.

• Locking both objects This is known as a "next-key" lock.

Monitoring Locks

InnoDB offers detailed diagnostic information via the SHOW INNODB STATUS command. For example, look at the following open transaction, along with its associated entry in this command's output:

START TRANSACTION; UPDATE vip SET vip_level = 'Gold' WHERE id BETWEEN 3 AND 5; SHOW INNODB STATUS\ G ... ------------ TRANSACTIONS ------------ Trx id counter 0 1061815858 Purge done for trx's n:o < 0 1061815856 undo n:o < 0 0 History list length 9 Total number of lock structs in row lock hash table 1 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 1061815857, ACTIVE 3 sec, process no 20846, OS thread id 210858 6928 2 lock struct(s), heap size 320, undo log entries 3 MySQL thread id 8, query id 663 localhost Gabriel ...

Experimenting with the SHOW INNODB STATUS command is a worthwhile exercise for any MySQL designer, developer, or administrator. See the "Optimal Transactions" section of this chapter for numerous other examples of what this command's output looks like in problem situations.

SQL Statements and Locks

Many SQL statements cause InnoDB to place and release locks. Because this usually happens so quickly and smoothly, most developers and administrators never realize that this is transpiring. Exceptions to this rule can occur when a transaction is left running too long, for example. These problem transactions are discussed later in this chapter.

Even if your transactions are perfectly designed, however, it's important that you understand the locking implications of your SQL statements; this can have a significant impact on both your InnoDB server's performance and concurrency.

In increasing order of restrictiveness, these locks and the SQL statements that trigger them include the following:

• Shared same-record lock This lock placed on the row in addition to any locks listed next.

  1. All data-modifying SQL statements.

• Shared next-key lock By preventing insertions immediately adjacent to a given key, a next-key lock helps defend against data integrity problems. The following statements trigger creation of shared versions of this lock.

  1. SELECT ... FROM This happens if the SERIALIZABLE transaction isolation level has been specified. Otherwise, no locking is done. Isolation levels are discussed in the following section.

  2. SELECT ... FROM ... LOCK IN SHARE MODE

• Exclusive non-next-key lock The following statements require more restrictive locking.

  1. INSERT INTO ... VALUES (...)

  2. SELECT ... FROM ... FOR UPDATE

  3. CREATE TABLE_X ... SELECT ... FROM TABLE_Y This lock is placed for rows in Table_X. If binary logging is enabled, Table_Y also receives a shared next-key lock.

  4. INSERT INTO TABLE_X ... SELECT ... FROM TABLE_Y This lock is placed for rows in Table_X. If binary logging is enabled, Table_Y also receives a shared next-key lock.

• Exclusive next-key lock Because of the possibility of data integrity problems, the following statements necessitate very restrictive locks.

  1. SELECT ... FROM ... FOR UPDATE

  2. UPDATE ... WHERE

  3. DELETE FROM ... WHERE

  The preceding three statements have the potential to generate enormous amounts of locks if there is no index available for InnoDB to use to process the query. This is because InnoDB locks all of the rows it evaluated to identify the resultset. In the absence of the assistance provided by an index, this could equate to a table scan.

  For example, suppose that the "vip" table listed previously had tens of thousands of rows, and you started a transaction that did not make use of an index:

  START TRANSACTION; UPDATE vip SET vip_level = 'Rust' WHERE vip_level = 'Tin'; SHOW INNODB STATUS\ G ... ---TRANSACTION 0 1061815874, ACTIVE 9 sec, process no 20846, OS thread id 210858 6928 fetching rows, thread declared inside InnoDB 293 mysql tables in use 1, locked 1 4129 lock struct(s), heap size 240960, undo log entries 313915 MySQL thread id 8, query id 2012 localhost Gabriel Updating update vip set vip_level = 'Rust' where vip_level = 'Tin' ...

  Here's the SHOW INNODB STATUS output after the update operation finished, but before the transaction was closed:

  ... ---TRANSACTION 0 1061815874, ACTIVE 153 sec, process no 20846, OS thread id 2108 586928 26433 lock struct(s), heap size 1502528, undo log entries 2010022 MySQL thread id 8, query id 2319 localhost Gabriel ...

  Look at the number of lock structures and undo log entries. This kind of open-ended, index-free operation can cause serious performance and concurrency problems. Try to ensure that these are rare in your MySQL environment.