28.2. Explicit Table Locking


Clients manage explicit table locks with two statements. LOCK TABLES acquires table locks and UNLOCK TABLES releases them. Acquisition of explicit locks can be advantageous in certain situations:

  • An implicit lock lasts for the duration of a single query only, which is unsuitable should you want to perform a multiple-statement update that requires no interference by other clients. To handle this, you can acquire an explicit lock, which remains in effect until you release it. Other clients cannot modify tables that you have locked.

  • Explicit locking can improve performance for multiple statements executed as a group while the lock is in effect. First, less work is required by the server to acquire and release locks because it need not do so for each statement. It simply acquires all needed locks at the beginning of the operation, and releases them at the end. Second, for statements that modify data, index flushing is reduced. For example, if you execute multiple INSERT statements using implicit locking, index flushing occurs following each statement. If you lock the table explicitly and then perform all the inserts, index flushing occurs only once when you release the lock. This results in less disk activity.

The LOCK TABLES statement names each table to be locked and the type of lock to be acquired. The following statement acquires a read lock on the Country table and a write lock on the City table:

 LOCK TABLES Country READ, City WRITE; 

To use LOCK TABLES, you must have the LOCK TABLES privilege, and the SELECT privilege for each table to be locked.

If any of the tables to be locked already are in use, LOCK TABLES blocks. It does not return until it has acquired all of the requested locks.

If you need to use multiple tables while holding an explicit lock, you must lock all of them at the same time because you cannot use any unlocked tables while you hold explicit locks. Also, you must lock all the tables with a single LOCK TABLES statement. LOCK TABLES releases any locks that you already hold, so you cannot issue it multiple times to acquire multiple locks.

The following list describes the available lock types and their effects:

  • READ

    Locks a table for reading. 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, even by the client that holds the lock. When a table is locked for reading, other clients can 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.

  • WRITE

    Locks a table for writing. 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.

  • READ LOCAL

    Locks a table for reading, but allows concurrent inserts. A concurrent insert is an exception to the "readers block writers" principle. It applies only to MyISAM tables. If a MyISAM table has no holes in the middle resulting from deleted or updated 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 MyISAM table does have holes, you can remove them by using OPTIMIZE TABLE to defragment the table.

    You can acquire a READ LOCAL lock for a fragmented MyISAM table, or for a non-MyISAM table, but in such cases, concurrent inserts are not allowed. The lock acts like a regular READ lock.

  • LOW_PRIORITY WRITE

    Locks a table for writing, but acquires the lock with a lower priority. That is, if the client must wait for the lock, other clients that request read locks during the wait are allowed to get their locks first. 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. A LOW_PRIORITY WRITE lock request also waits for any new read requests that arrive while the lock request is pending.

To release explicit locks, issue an UNLOCK TABLES statement. This statement names no tables, because it releases all explicit locks held by the issuing client.

Explicit locks held by a client also are released if the client issues another 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 SUPER privilege can terminate a client connection with the KILL statement, which causes release of locks held by the client.

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.

Table locks may be affected by transactions and vice versa. Beginning a transaction with START TRANSACTION causes an implicit UNLOCK TABLES. Issuing a LOCK TABLES statement will implicitly commit any pending transaction. If you have locked any tables, issuing an UNLOCK TABLES statement will implicitly commit any pending transaction.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net