Giving SQL SELECT Priority over SQL INSERT/UPDATEs in MySQL

[wp_ad_camp_5]

Let us say you have a table that is being read a lot and updates (SQL INSERT and UPDATE) are occassional or initiated by some asynchronous operations (e.g., Message-driven beans). Furthermore, you consider the reads more important than the updates. How could we give read requests priority over update requests?

Here are the general ways to do it. In a nutshell, updates are delayed until no other clients are reading from the table. It is also worth noting that these constructs have their own limitations and disadvantages – both usage and performance. So, do not use them just to be fancy. For instance, INSERT DELAYED does not work with InnoDB. In addition to that, additional load may be added to the server.

[wp_ad_camp_4]

SQL SELECT

select high_priority f1, f2 from TEST_TABLE;

1	select high_priority f1, f2 from TEST_TABLE;

SQL INSERT or UPDATE

/* Make sure MySQL is not in strict more and
   the table fields are defined with NOT NULLs */
insert delayed into TEST_TABLE values();

update low_priority TEST_TABLE set f1 = 3;

/* Make sure MySQL is not in strict more and

the table fields are defined with NOT NULLs */

insert delayed into TEST_TABLE values();

update low_priority TEST_TABLE set f1 = 3;

[wp_ad_camp_3]