0

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?

Dharman
  • 30,962
  • 25
  • 85
  • 135
NicolasCanac
  • 57
  • 1
  • 5

2 Answers2

0

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.

Synchro
  • 35,538
  • 15
  • 81
  • 104
mrhn
  • 17,961
  • 4
  • 27
  • 46
0

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.

ah_hau
  • 768
  • 4
  • 11
  • Thanks. Do you know that the query can failed ? With SQL Server, it seems that an error message is displayed "Could not continue scan with NOLOCK due to data movement." – NicolasCanac Aug 04 '14 at 02:01
  • http://stackoverflow.com/questions/16879720/sql-server-error-could-not-continue-scan-with-nolock-due-to-data-movement Try DBCC CHECKDB to check your db is corrupted or not. – ah_hau Aug 04 '14 at 02:13
  • Ok, so it's only in the case that database are corrupted. My final question is: are you sure that with READ UNCOMMITTED, I will always have a result returned ? – NicolasCanac Aug 04 '14 at 02:18
  • yes, you should. Unless during that moment all data being deleted/updated while you executing the query or no valid data due to the where clause – ah_hau Aug 04 '14 at 02:20