MySQL SQL_NO_CACHE not working

存储架构 2018-03-14

I have a table (InnoDB) that gets inserted, updated and read frequently (usually in a burst of few milliseconds apart). I noticed that sometimes the SELECT statement that follows an INSERT/UPDATE would get stale data. I assume this was due to cache, but after putting SQL_NO_CACHE
in front of it doesn't really do anything.

How do you make sure that the SELECT always wait until the previous INSERT/UPDATE finishes and not get the data from cache? Note that these statements are executed from separate requests (not within the same code execution).

Maybe I am misunderstanding what SQL_NO_CACHE actually does...

UPDATE:

@Uday, the INSERT, SELECT and UPDATE statement looks like this:

INSERT myTable (id, startTime) VALUES(1234, 123456)

UPDATE myTable SET startTime = 123456 WHERE id = 1234

SELECT SQL_NO_CACHE * FROM myTable ORDER BY startTime

I tried using transactions with no luck.

More UPDATE:

I think this is actually a problem with INSERT, not UPDATE. The SELECT statement always tries to get the latest row sorted by time. But since INSERT does not do any table-level locking, it's possible that SELECT will get old data. Is there a way to force table-level locking when doing INSERT?

Problem courtesy of: pixelfreak

Solution

The query cache isn't the issue. Writes invalidate the cache.

MySQL gives priority to writes and with the default isolation level (REPEATABLE READ), your SELECT would have to wait for the UPDATE to finish.

INSERT can be treated differently if you have CONCURRENT INSERTS
enabled for MyISAM, also InnoDB uses record locking, so it doesn't have to wait for inserts at the end of the table.

Could this be a race condition then? Are you sure your SELECT occurs after the UPDATE? Are you reading from a replicated server where perhaps the update hasn't propagated yet?

If the issue is with the concurrent INSERT, you'll want to disable CONCURRENT INSERT on MyISAM, or explicitly lock the table with LOCK TABLES during the INSERT. The solution is the same for InnoDB, explicitly lock the table on the INSERT with LOCK TABLES.

Solution courtesy of: Marcus Adams

您可能感兴趣的

Problem with passing parameters to the SQL procedu... I am trying to pass @intDocumentNo and @intCustomerNo to a stored procedure using VBA but only @intCustomerNo is updated in dbo.tblOrderHead...
Showing a blank page in php, do not redi... I don't know where I am wrong with this PHP code. When i click on log in in my log in Page the username and password gonna check in this code den i...
Jedi SAS Tricks – FedSQL Dictionary Tables Dictionary tables are one of the things I love most about SQL! What a useful thing it is to be able to programmatically determine what your data lo...
Level Up Your KSQL Now that KSQL is available for production use as a part of the Confluent Platform, it has never been easier to run the open-source streaming SQL ...
T-SQL检查停止的复制作业代理,并启动 USE Create PROC . AS DECLARE @jobname VARCHAR(200) DECLARE jobname CURSOR FOR SELECT DISTINCT b.name AS MergeJobName FROM distribu...