CODEWITHSUNDEEP
mysqldatabasetransactionslocking
olovp
olovp

Reputation: 91

How do I guarantee that a transaction is committed in MySQL before querying it?

I have an application that is storing job state in a MySQL database (recently migrated from DynamoDB). I'm now running into the problem that an update is not visible for subsequent queries immediately, causing failures. This is the flow, completely sequential, no concurrency going on:

  1. an object in db has state X
  2. the object is updated to have state Y
  3. the object is retrieved and is expected to have state Y
  4. the retrieved object has state X, the task fails

From what I've learned, there seem to be two options to get around this:

  1. Set isolation level to serializable in the update transaction.

  2. Query using select ... for update, locking the row from updates.

I don't know enough to weigh the options though, maybe someone can help me sort this out.

For the db interface I'm using gorm in Go, no fancy custom sql or anything. Performance is not really an issue, the dataset is small and updates are relatively infrequent. The state field is indexed since it's frequently queried on.

Upvotes: 0

Views: 372

Answers (1)

aschoerk
aschoerk

Reputation: 3593

Since you are working with different transactions:

MySql-innodb-engine works with MVCC. That means as soon as you start a transaction its view to the database stays the same for the complete transaction. Even if parallel running transactions are completed and therefore no dirty read would be done, the changes will not be seen by the earlier started transaction.

If you keep this in mind, you might have an explanation for the weird behavior and can take deterministic actions to get around this.

Upvotes: 1

Related Questions