Search code examples
mysqlsqltransactionsisolation-level

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?

Solution

  • 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.