Skip navigation links

Additional languages
7.3.4 External Locking »
Section Navigation      [Toggle]

7.3.3. Concurrent Inserts

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), 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. 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 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

Posted by Baron "Xaprb" Schwartz on May 12 2008 3:30pm[Delete] [Edit]

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.