Mysql – SQL row data mismatch for different users

MySQLrails

I am using MySQL (version 5.5.43) as my database.

I have a RoR micro-service that does an update column to a model Active Record class:

model.update_column(status: 0)

The next line is an api call to different micro-service that synchronously does a SQL query:

select * from model where status = 0;

The code runs without any errors but the latter query is not fetching the record that is being updated by the former. There is milliseconds of difference between the update and the read.

Both the services are connected to the same database as different users but same access.

I don't understand why this would happen? the update_column is obviously a commit to the db, then why would the select query not fetch the updated record. What am I missing here?

Best Answer

Some things must be put in a "transaction".

One example:

BEGIN;
SELECT ... FOR UPDATE;
do some non-sql processing
UPDATE ...  -- specifying the row that was `SELECTed`
COMMIT;

Without the FOR UPDATE, another connection might sneak in and update (or delete or ...) the row that you thought you had.

Related Question