28.3. Advisory Locking

An advisory lock is a cooperative lock. That is, an advisory lock has no power to prevent data access by other clients, but instead is based on the concept that all clients will use an agreed-upon convention to cooperate for use of a resource. The convention is the lock name, which is simply a string. While the name is locked, the advisory lock is considered to be in place and every other cooperating client refrains from whatever action it would perform if it held the lock itself.

Advisory locks are implemented using a set of function calls. To acquire a lock, use the GET_LOCK() function:

mysql>

SELECT GET_LOCK('my lock', 5);

+------------------------+

 GET_LOCK('my lock', 5) 

+------------------------+

                      1 

+------------------------+

The first argument is a string that specifies the name to be locked, and the second argument is a timeout value in seconds that indicates how long to wait for the lock if it cannot be acquired immediately. GET_LOCK() returns 1 for success, 0 if a timeout occurs and the lock cannot be acquired, or NULL if an error occurs.

A client that has acquired an advisory lock can release it by calling RELEASE_LOCK() :

mysql>

SELECT RELEASE_LOCK('my lock');

+-------------------------+

 RELEASE_LOCK('my lock') 

+-------------------------+

                       1 

+-------------------------+

RELEASE_LOCK() returns 1 if the lock was released successfully, 0 if the name was locked but not by the client requesting the release, and NULL if the name was not locked.

An advisory lock also is released if the client makes another call to GET_LOCK() or closes its connection to the server.

Two other functions are available for checking the status of advisory locks:

IS_FREE_LOCK( lock_name ) returns 1 if the name is not locked, 0 if it is locked, and NULL if an error occurs.
IS_USED_LOCK( lock_name ) returns the connection ID of the client that holds the lock on the name, or NULL if the name is not locked.

Chapter 29. Storage Engines

MySQL allows you to choose from any of several storage engines when creating a table. Different table types are managed by different storage engines, each of which has specific characteristics. This chapter discusses the features of several storage engines in detail and summarizes others. It covers the following exam topics:

An overview of the storage engine concept in MySQL
The MyISAM storage engine
The MERGE storage engine
The InnoDB storage engine
The MEMORY storage engine
The FEDERATED storage engine
The Cluster storage engine
A summary of other storage engines

29.1. MySQL Storage Engines

All tables managed by MySQL Server have certain similarities. For example, every table in a database has a format ( .frm ) file in the database directory. This file, which stores the definition of the table's structure, is created by the server. Tables have differences as well, which are tied to the storage engines that the server uses to manage table contents. Each storage engine has a particular set of operational characteristics. For example, engines may create additional disk files to accompany the .frm files, but the types of files that they create to manage data and index storage vary per engine. Storage engines differ in other ways as well, such as in the way that they use locking to manage query contention , or in whether the tables that they provide are transactional or non-transactional. These engine properties have implications for query processing performance, concurrency, and deadlock prevention. (Deadlock occurs when multiple queries are blocked and cannot proceed because they are waiting for each other to finish.)

When you create a table, you can choose what storage engine to use. Typically, this choice is made according to which storage engine offers features that best fit the needs of your application. For example, ask yourself what types of queries you'll use the table for. Then choose a storage engine that uses a locking level appropriate for the anticipated query mix. MyISAM table-level locking works best for a query mix that is heavily skewed toward retrievals and includes few updates. Use InnoDB if you must process a query mix containing many updates. InnoDB 's use of row-level locking and multi-versioning provides good concurrency for a mix of retrievals and updates. One query can update rows while other queries read or update different rows of the table.

To specify a storage engine explicitly in a CREATE TABLE statement, use an ENGINE option. The following statement creates t as an InnoDB table:

CREATE TABLE t (i INT) ENGINE = InnoDB;

If you create a table without using an ENGINE option to specify a storage engine explicitly, the MySQL server creates the table using the default engine, which is given by the value of the storage_engine system variable. Section 8.2.2, "Specifying the Storage Engine for a Table," further discusses how to specify storage engines when creating tables and how to change the default storage engine.

To determine which storage engine is used for a given table, you can use the SHOW CREATE TABLE or the SHOW TABLE STATUS statement:

mysql>

SHOW CREATE TABLE City\G

*************************** 1. row ***************************

       Table: City

Create Table: CREATE TABLE `City` (

  `ID` int(11) NOT NULL auto_increment,

  `Name` char(35) NOT NULL default '',

  `CountryCode` char(3) NOT NULL default '',

  `District` char(20) NOT NULL default '',

  `Population` int(11) NOT NULL default '0',

  PRIMARY KEY  (`ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)



mysql>

SHOW TABLE STATUS LIKE 'CountryLanguage'\G

*************************** 1. row ***************************

           Name: CountryLanguage

         Engine: MyISAM

        Version: 10

     Row_format: Fixed

           Rows: 984

 Avg_row_length: 39

    Data_length: 38376

Max_data_length: 167503724543

   Index_length: 22528

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2005-04-26 22:15:35

    Update_time: 2005-04-26 22:15:43

     Check_time: NULL

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)

The INFORMATION_SCHEMA TABLES table contains storage engine information as well:

mysql>

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES

->

WHERE TABLE_SCHEMA = 'world';

+-----------------+--------+

 TABLE_NAME       ENGINE 

+-----------------+--------+

 City             MyISAM 

 Country          MyISAM 

 CountryLanguage  MyISAM 

+-----------------+--------+

Although you can choose which storage engine to use for a table, in most respects the way that you use the table after creating it is engine independent. Operations on tables of all types are performed using the SQL interface and MySQL manages engine-dependent details for you at a lower level in its architecture. That is, your interface to tables is at the higher SQL tier, and table-management details are at the lower storage-engine tier . There are, nonetheless, times when knowing which storage engine manages a table can enable you to use the table more efficiently . Engine-specific optimizations that you can take advantage of are covered in Chapter 38, "Optimizing Databases."

Before you can use a given storage engine, it must be compiled into the server and enabled. MySQL Server uses a modular architecture: Each storage engine is a software module that is compiled into the server. The use of this modular approach allows storage engines to be easily selected for inclusion in the server at configuration time.

Some storage engines are always available, such as MyISAM , MERGE , and MEMORY . Other engines are optional. Support for optional engines typically can be selected when MySQL is configured and built. Compiled-in optional engines also typically can be enabled or disabled with a server startup option. For example, the InnoDB storage engine is included in all binary distributions. If you build MySQL from source, InnoDB is included by default unless you specify the --without-innodb configuration option. For a server that has the InnoDB storage engine included, support may be disabled at startup with the --skip-innodb option.

To reduce memory use, don't configure unneeded storage engines into the server. This requires that you compile MySQL from source rather than using a precompiled binary distribution. If you are using a binary distribution that includes compiled-in optional engines that you don't need, disable them at runtime.

To see what storage engines are compiled into your server and whether they are available at runtime, use the SHOW ENGINES statement

mysql>

SHOW ENGINES\G

*************************** 1. row ***************************

 Engine: MyISAM

Support: DEFAULT

Comment: Default engine as of MySQL 3.23 with great performance

*************************** 2. row ***************************

 Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

*************************** 3. row ***************************

 Engine: HEAP

Support: YES

Comment: Alias for MEMORY

...

The following sections examine several of MySQL's storage engines in more detail. Much of the discussion involves locking concepts, so you should be familiar with the material covered in Chapter 28, "Locking." Note: When this chapter says that deadlock cannot occur with table locking, that is subject to the exception described in section 28.1 "Locking Concepts."

Buy on amazon.com >>

DuBois P., Hinz S., Pedersen C.

<< Previous page

Table of contents

Next page >>