'READ UNCOMMITTED and INNER JOIN: can a query failed?

I have two tables in InnoDB:

  • user (id PRIMARY KEY, name VARCHAR(255))
  • key (id PRIMARY KEY, key INTEGER, a_id FOREIGN KEY REFERENCES user.id)

One user can have multiples keys. To display results, I do joins like:

SELECT k.id, k.`key`, u.id, u.name
FROM user u
INNER JOIN `key` k
ON u.ID=k.a_id

Imagine there is others queries which regulary update/insert/delete rows in such tables.

Does the query can simply failed with READ UNCOMMITTED ? What about READ COMMITTED ?

With SQL Server, it seems that an error message is displayed "Could not continue scan with NOLOCK due to data movement." when doing joins with READ UNCOMMITTED. What about MySQL?

mysqlsqltransactionsisolation-level


Solution 1:[1]

The data that you selected might not up to date, example you might select out 10 records while user already inserted 11th. Or in db it left 9 records due to deletion. this is so-called dirty data as it might not reflecting what real situation does.

But the good thing for it is it's fast cause it does not need to wait for the insert/update/delete that is locking the table/row.

Solution 2:[2]

In general I wouldn't be worried that the database will fail such an query. The database will out of the box, lock tables in critical situations. Where innodb is standard to use next key locks and for documentation read about InnoDB locks and InnoDB record locks.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ah_hau
Solution 2 Synchro

Related Questions