- Documentation Library
- Table of Contents
- MySQL 5.5 Manual
- MySQL 5.4 Manual
- MySQL 5.1 Manual
- MySQL 5.0 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
As of MySQL 3.23.7 (3.23.25 for Windows), 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.
Concurrent inserts are enabled by default, but can be disabled
by setting the
concurrent_insert system
variable to 0.
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 update log or 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.3.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.