Skip navigation links
13.5.7 Maria Design Notes »
Section Navigation      [Toggle]

13.5.6. Maria Statement Concurrency

The concurrency of statement execution on tables is handled differently in Maria depending on whether you are using non-transactional or transactional tables.

For non-transactional tables, the rules are identical to MyISAM:

  • SELECT

    All issued SELECT's are running concurrently. While a SELECT is running, all writers (INSERT, DELETE, UPDATE) are blocked from using any of the used tables (that is, they wait for the table to be free before continuing) . The only exception is that one INSERT CONCURRENT can be run on each table that doesn't have any deleted rows.

  • UPDATE

    Only one UPDATE statement can run at the same time on each table. While the UPDATE is running all other threads are blocked from using this table.

  • DELETE

    Only one DELETE statement can run at the same time on each table. While the DELETE is running all other threads are blocked from using this table.

  • INSERT

    If INSERT CONCURRENT is used, and there are no deleted rows in the table, only one INSERT CONCURRENT statement can run at the same time on each table. While the INSERT CONCURRENT is running all other writer threads are blocked for using this table. Any number of SELECT statements can use this table.

    If normal INSERT is used or if there are deleted rows in the table, only one INSERT statement can run at the same time on the table. While the INSERT is running all SELECT, INSERT, DELETE and UPDATE are blocked from using this table.

  • CREATE or DROP

    CREATE's on different tables can be run concurrently. On the same table, first creator wins. DROP waits until all statements using the tables are completed, after which the table is dropped.

When using transactional tables, Maria supports a single writer and multiple readers. The single writer supports both INSERT and UPDATE operations.

  • SELECT

    All issued SELECT's are running concurrently. While a SELECT is running, all writers (INSERT, DELETE, UPDATE) are blocked from using any of the used tables (ie, they wait for the table to be free before continuing).

  • UPDATE

    As part of the single writer, only one UPDATE statement can run at the same time on each table. While the UPDATE is running all other threads using UPDATE or INSERT are blocked from using this table.

  • INSERT

    As part of the single writer, only one INSERT statement can run at the same time on the table. While the INSERT is running all other threads using UPDATE or INSERT are blocked from using this table.

  • DELETE

    Only one DELETE statement can run at the same time on each table. While the DELETE is running all other threads are blocked from using this table.

  • CREATE or DROP

    CREATE operations on different tables can be run concurrently. On the same table, first creator wins. DROP waits until all statements using the tables are completed, after which the table is dropped.

Starting with MySQL 5.1.27-maria, multiple concurrent INSERT statements are supported, with the following notes:

  • To use multiple writers you should lock tables using the statement: 

    LOCK TABLES table_name WRITE CONCURRENT

  • During multiple write operations, all SELECT statements operate in REPEATABLE READ mode.

  • All INSERT statements are considered atomic, and will use concurrent insert locks to ensure consistency.

  • Concurrent inserts are not supported on:

    • Non-transactional tables.

    • Transactional tables that have GIS (spatial) or FULLTEXT indexes.

    • Empty tables.