| The MySQL server uses a multi-threaded architecture that enables it to service many clients concurrently (simultaneously). For each client that connects, the server allocates a thread as a connection handler. If each client accesses different tables than the others, they do not interfere with each other. However, when multiple clients attempt to access a table at the same time, this creates contention and it's necessary to coordinate the clients. Otherwise, problems could occur, such as one client changing rows while another client is reading them or two clients making changes to the same row concurrently. To avoid these problems and prevent data corruption, MySQL uses locking. Locking is a mechanism that prevents problems from occurring with simultaneous data access by multiple clients. Locks are managed by the server: It places a lock on data on behalf of one client to restrict access by other clients to the data until the lock has been released. The lock allows access to data by the client that holds the lock, but places limitations on what operations can be done by other clients that are contending for access. The effect of the locking mechanism is to serialize access to data so that when multiple clients want to perform conflicting operations, each must wait its turn. Not all types of concurrent access produce conflicts, so the type of locking that is necessary to allow a client access to data depends on whether the client wants to read or write: If a client wants to read data, other clients that want to read the same data do not produce a conflict, and they all can read at the same time. However, another client that wants to write (modify) data must wait until the read has finished. If a client wants to write data, all other clients must wait until the write has finished, regardless of whether those clients want to read or write.
In other words, a reader must block writers, but not other readers. A writer must block both readers and writers. Read locks and write locks allow these restrictions to be enforced. Locking makes clients wait for access until it is safe for them to proceed. In this way, locks prevent data corruption by disallowing concurrent conflicting changes and reading of data that is in the process of being changed. A lock on data can be acquired implicitly or explicitly: For a client that does nothing special to acquire locks, the MySQL server implicitly acquires locks as necessary to process the client's statements safely. For example, the server acquires a read lock when the client issues a SELECT statement and a write lock when the client issues an INSERT statement. Implicit locks are acquired only for the duration of a single statement. If implicit locking is insufficient for a client's purposes, it can manage locks explicitly byacquiring them with LOCK TABLES and releasing them with UNLOCK TABLES. Explicit locking may be necessary when a client needs to perform an operation that spans multiple statements and that must not be interrupted by other clients. For example, an application might select a value from one table and then use it to determine which records to update in a set of other tables. With implicit locking, it's possible for another client to perform other, possibly conflicting changes between statements of the first client's operation. To prevent this, the first client can place an explicit lock on the tables that it uses.
Another type of lock is the advisory, or cooperative, lock. Advisory locks do not lock data and they do not prevent access to data by clients except to the extent that they cooperate with each other. Unlike implicit and explicit data locks, advisory locks are not managed by the server. Clients manage advisory locks using a set of function calls to cooperate among themselves. Data locking in MySQL occurs at different levels. Explicit locks acquired with LOCK TABLES are table locks. For implicit locks, the lock level that MySQL uses depends on the storage engine: MyISAM, MEMORY, and MERGE tables are locked at the table level. BDB tables are locked at the page level. InnoDB tables are locked at the row level.
The different levels of locking "granularity" have different concurrency characteristics: Table locking is not as desirable as page or row locking for concurrency in a mixed read/write environment. A table lock prevents other clients from making any changes to the table, even if the client that holds the lock is not accessing the parts of the table that other clients want to modify. With page and row locks, a client that locks a page or row does not prevent changes by other clients to other pages or rows. Deadlock cannot occur with table locking as it can with page or row locking. For example, with row-level locking, two clients might each acquire a lock on different rows. If each then tries to modify the row that the other has locked, neither client can proceed. This is called "deadlock." With table locking, the server can determine what locks are needed and acquire them before executing a statement, so deadlock never occurs. (An exception is possible when applications use cursors, because then the server must hold open a lock for a client across multiple statements. Suppose that client 1 opens a cursor for reading table t1 and client 2 opens a cursor for reading table t2. While the cursors are open, if each client tries to update the table being read by the other, deadlock can occur.)
|
