The MyISAM storage engine supports concurrent
inserts to reduce contention between readers and writers for a
given table: If a MyISAM table has no holes
in the data file (deleted rows in the middle), an
INSERT statement can be executed
to add rows to the end of the table at the same time that
SELECT statements are reading
rows from the table. If there are multiple
INSERT statements, they are
queued and performed in sequence, concurrently with the
SELECT statements. The results of
a concurrent INSERT may not be
visible immediately.
The concurrent_insert system
variable can be set to modify the concurrent-insert processing.
By default, the variable is set to 1 and concurrent inserts are
handled as just described. If
concurrent_insert is set to 0,
concurrent inserts are disabled. If the variable is set to 2,
concurrent inserts at the end of the table are allowed even for
tables that have deleted rows. See also the description of the
concurrent_insert
system variable.
Under circumstances where concurrent inserts can be used, there
is seldom any need to use the DELAYED
modifier for INSERT statements.
See Section 12.2.5.2, “INSERT DELAYED Syntax”.
If you are using the binary log, concurrent inserts are
converted to normal inserts for CREATE ...
SELECT or
INSERT ...
SELECT statements. This is done to ensure that you can
re-create an exact copy of your tables by applying the log
during a backup operation. See Section 5.2.4, “The Binary Log”. In
addition, for those statements a read lock is placed on the
selected-from table such that inserts into that table are
blocked. The effect is that concurrent inserts for that table
must wait as well.
With LOAD DATA
INFILE, if you specify CONCURRENT
with a MyISAM table that satisfies the
condition for concurrent inserts (that is, it contains no free
blocks in the middle), other sessions can retrieve data from the
table while LOAD DATA is
executing. Use of the CONCURRENT option
affects the performance of LOAD
DATA a bit, even if no other session is using the
table at the same time.
If you specify HIGH_PRIORITY, it overrides
the effect of the
--low-priority-updates option if
the server was started with that option. It also causes
concurrent inserts not to be used.
For LOCK
TABLE, the difference between READ
LOCAL and READ is that
READ LOCAL allows nonconflicting
INSERT statements (concurrent
inserts) to execute while the lock is held. However, this cannot
be used if you are going to manipulate the database using
processes external to the server while you hold the lock.
User Comments
Note that "... concurrent inserts are converted to normal inserts for CREATE ... SELECT or INSERT ... SELECT statements" means these statements will NOT be compatible with concurrent inserts.
In other words, if someone runs INSERT INTO tbl2 SELECT * FROM tbl1, any inserts into tbl1 will block until the INSERT..SELECT is done, even if you would normally be able to insert concurrently into tbl1.
If you use mysqladmin debug to examine the locks, on a normal select from tbl1 you'll see this:
Locked - read Low priority read lock
But on INSERT..SELECT, you'll see this:
Read lock without concurrent inserts
Add your own comment.