|< Day Day Up >|
14.3 MyISAM Locking Strategies
When multiple clients attempt to access a table at the same time, it's necessary to coordinate them. This avoids problems such as one client changing rows while another client is reading them, or two clients making changes to the same row simultaneously. MySQL solves this problem for MyISAM tables using a locking mechanism.
MyISAM locking occurs at the table level. This is not as desirable as page or row locking for concurrency in a mixed read/write environment. However, 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 before executing a statement, so deadlock never occurs.
When processing queries on MyISAM tables, the server manages contention for the tables by simultaneous clients by implicitly acquiring any locks it needs. You can also lock tables explicitly with the LOCK TABLES and UNLOCK TABLES statements. Acquiring locks yourself can be advantageous in certain situations:
14.3.1 MyISAM Locking Characteristics
Only the client that holds a lock acquired with LOCK TABLES can release the lock. Another client cannot release it. In other words, if you acquire a lock, it's yours until you give it up. Another client cannot force you to release it. Locks are released when you issue an UNLOCK TABLES or LOCK TABLES statement. Locks cannot be maintained across connections; if a client has any unreleased locks when its connection to the server terminates, the server implicitly releases its locks. An administrator with the appropriate privilege can terminate a client connection with the KILL statement, which causes release of locks held by the client.
If you need to lock several tables at the same time, you must do so with a single LOCK TABLES statement. The following statement acquires a read lock on Country and a write lock on City:
LOCK TABLES Country READ, City WRITE;
A read lock locks a table for read queries such as SELECT that retrieve data from the table. It does not allow write operations such as INSERT, DELETE, or UPDATE that modify the table. A read lock prevents a write lock from being placed on the table, but does not prevent other clients from acquiring read locks. That is, when a table is locked for reading, other clients can still read from the table at the same time, but no client can write to it. A client that wants to write to a table that is read-locked must wait until all clients currently reading from it have finished and released their locks.
A write lock is an exclusive lock. It can be acquired only when a table is not being used. Once acquired, only the client holding the write lock can read from or write to the table. Other clients can neither read from nor write to it. No other client can lock the table for either reading or writing.
A concurrent insert is a special exception to the "readers block writers" principle. If a MyISAM table has no holes in the middle resulting from deleted records, inserts always take place at the end of the table. In that case, a client that is reading from a table can lock it with a READ LOCAL lock to allow other clients to insert into the table while the client holding the read lock reads from it. If a table does have holes, concurrent inserts cannot be performed. However, you can remove the holes by using OPTIMIZE TABLE to defragment the table. (Note that a record deleted from the end of the table does not create a hole and does not prevent concurrent inserts.)
A normal write lock request is satisfied when no other clients are using the table. If other clients are using the table when the request is made, it waits until those clients have finished. However, a LOW_PRIORITY WRITE lock may be requested instead, which also waits for any new read requests that arrive while the lock request is pending.
14.3.2 Query Scheduling Modifiers
However, if a table is being read from when a write request arrives, the write request cannot be processed until all current readers have finished. Any read requests that arrive after the write request must wait until the write request finishes, even if they arrive before the current readers finish. That is, a new read request by default does not jump ahead of a pending write request.
The implication is that DELAYED is more suitable for applications where loss of a few rows is not a problem, rather than applications for which each row is critical. For example, DELAYED can be appropriate for an application that logs activity for informational purposes only.
|< Day Day Up >|