![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_1646196729.webp)
Locking Issues
709
The information provided by the
Qcache_lowmem_prunes
[530]
status variable can help you tune the
query cache size. It counts the number of queries that have been removed from the cache to free up
memory for caching new queries. The query cache uses a least recently used (LRU) strategy to decide
which queries to remove from the cache. Tuning information is given in
Section 8.6.3.3, “Query Cache
Configuration”
.
8.7. Locking Issues
MySQL manages contention for table contents using locking:
• Internal locking is performed within the MySQL server itself to manage contention for table contents
by multiple threads. This type of locking is internal because it is performed entirely by the server and
involves no other programs. See
Section 8.7.1, “Internal Locking Methods”
.
• External locking occurs when the server and other programs lock
MyISAM
table files to coordinate
among themselves which program can access the tables at which time. See
Section 8.7.4, “External
Locking”
.
8.7.1. Internal Locking Methods
This section discusses internal locking; that is, locking performed within the MySQL server itself to
manage contention for table contents by multiple sessions. This type of locking is internal because it
is performed entirely by the server and involves no other programs. External locking occurs when the
server and other programs lock
MyISAM
table files to coordinate among themselves which program can
access the tables at which time. See
Section 8.7.4, “External Locking”
.
MySQL uses table-level locking for
MyISAM
,
MEMORY
and
MERGE
tables, page-level locking for
BDB
tables, and row-level locking for
InnoDB
tables.
In many cases, you can make an educated guess about which locking type is best for an application,
but generally it is difficult to say that a given lock type is better than another. Everything depends on the
application and different parts of an application may require different lock types.
To decide whether you want to use a storage engine with row-level locking, you should look at what
your application does and what mix of select and update statements it uses. For example, most Web
applications perform many selects, relatively few deletes, updates based mainly on key values, and
inserts into a few specific tables. The base MySQL
MyISAM
setup is very well tuned for this.
Table locking in MySQL is deadlock-free for storage engines that use table-level locking. Deadlock
avoidance is managed by always requesting all needed locks at once at the beginning of a query and
always locking the tables in the same order.
MySQL grants table write locks as follows:
1. If there are no locks on the table, put a write lock on it.
2. Otherwise, put the lock request in the write lock queue.
MySQL grants table read locks as follows:
1. If there are no write locks on the table, put a read lock on it.
2. Otherwise, put the lock request in the read lock queue.
Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the
lock is made available to the requests in the write lock queue and then to the requests in the read lock
queue. This ensures that updates to a table are not “starved” even if there is heavy
SELECT
activity for
the table. However, if you have many updates for a table,
SELECT
statements wait until there are no
more updates.
For information on altering the priority of reads and writes, see
Section 8.7.2, “Table Locking Issues”
.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...