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:
Implicit locks last for the duration of a single query only. Should you want to perform a multiple-statement update that requires no interference by other clients, you can acquire an explicit lock, which remains in effect until you release it. Multiple-statement locking can improve performance. For example, multiple INSERT statements executed using implicit locking result in index flushing for each statement. If you lock a table explicitly and then perform all the inserts, index flushing occurs only once when you release the lock. This results in less disk activity. It also eliminates the need to acquire the implicit lock for each statement.
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.
Locks fall into two general categories: read locks and write locks. For each type of lock, there are also options to modify lock behavior somewhat.
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
By default, the server schedules queries for execution as follows:
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.
When working with MyISAM tables, certain scheduling modifiers are available to change the priority of requests:
The LOW_PRIORITY modifier may be applied to statements that update tables (INSERT, DELETE, REPLACE, or UPDATE). A low-priority write request waits not only until all current readers have finished, but for any pending read requests that arrive while the write request itself is waiting. That is, it waits until there are no pending read requests at all. It is therefore possible for a low-priority write request never to be performed, if read requests keep arriving while the write request is waiting. HIGH_PRIORITY may be used with a SELECT statement to move it ahead of updates and ahead of other SELECT statements that do not use the HIGH_PRIORITY modifier. DELAYED may be used with INSERT (and REPLACE). The server buffers the rows in memory and inserts them when the table is not being used. Delayed inserts increase efficiency because they're done in batches rather than individually. While inserting the rows, the server checks periodically to see whether other requests to use the table have arrived. If so, the server suspends insertion of delayed rows until the table becomes free again. Using DELAYED allows the client to proceed immediately after issuing the INSERT statement rather than waiting until it completes.
If you use DELAYED, keep the following points in mind:
If a crash occurs while the server is buffering delayed rows in memory, those rows are lost. Delayed rows tend to be held for a longer time on a very busy server than on a lightly loaded one.
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.
|
