|< Day Day Up >|
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.
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:
Broadly speaking, MySQL-level locks fall into one of two classes:
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 | +----+-----------+-----------+
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:
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:
|< Day Day Up >|