- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 7.3 Locking Issues
- 7.3.1 Internal Locking Methods
- 7.3.2 Table Locking Issues
- 7.3.3 Concurrent Inserts
- 7.3.4 External Locking
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), inserts can be
performed to add rows to the end of the table at the same time
that SELECT statements are reading rows from
the table.
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.4.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 threads 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 thread 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 non-conflicting
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.