CODEWITHSUNDEEP
mysqlsqltransactionsrdbms
Kohei Nozaki
Kohei Nozaki

Reputation: 1154

Does SELECT keep consistency during concurrent UPDATE or INSERT?

I'm using MySQL 5.6 with innodb. Let's say we have the following two tables:

create table order_head (
    id int not null,
    version int not null,
    order_detail_count int not null,
    primary key (id)
);

create table order_detail (
    id int not null,
    product_id int not null,
    qty int not null,
    order_head_id int not null,
    primary key (id),
    foreign key (order_head_id)
        references order_head (id)
);

Consider a situation that many concurrent INSERT and UPDATE for both two tables are executing all the time. The application which runs those transactions is well-designed with optimistic locking so concurrent executions don't make any inconsistent data.

Under that situation, I have a concern about issuing the following query:

SELECT 
    *
FROM
    order_head h
        JOIN
    order_detail d ON (h.id = d.order_head_id);

Does this query always ensure that it will return consistent results? in other words, does this query never mix data of multiple distinct transactions? For example, I don't expect inconsistent results such as the count of records is 4 while order_head.order_detail_count is 3.

I think I don't have good understanding of transactions, so any pointers to good references (e.g. books about transactions) would be also greatly appreciated.

Upvotes: 1

Views: 261

Answers (1)

Carlos_Mondragon
Carlos_Mondragon

Reputation: 116

That is a basic principle on any RDBMS.

The ACID Rules (https://en.wikipedia.org/wiki/ACID) that any RDBMS must acomplish, in this case the ISOLATION where each Query to the database should not be interfered by another query that is taking place at the same time.

Upvotes: 1

Related Questions