CODEWITHSUNDEEP
mysqlinnodb
StackyStack
StackyStack

Reputation: 115

InnoDB MySQL Select Query Locking

I have an isolation level of Repeatable Read and I am making a: Select * From examplequery. I read in https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html that select...from queries use consistent reads from snapshot and therefore set no locks on rows or table. Does that mean, an update, insert, or delete initiated after the select but before the select query ends would still be able to run even though the modification won't show up in the select results?

Upvotes: 2

Views: 3029

Answers (2)

Shadow
Shadow

Reputation: 34231

Basically, yes, this is the case, with some complication.

By default, in repeatable read a select ... from ... does not place any locks on the underlying data and establishes a snapshot.

If another transaction changes the underlying data, then these changes are not reflected if the same records are selected again in the scope of the first transaction. So far so good.

However, if your first transaction modifies records that were affected by other committed transactions after the snapshot was established, then those modifications done by other transactions will be also become visible to the 1st transaction, so your snapshot may not be that consistent after all.

See the 1st notes section in Consistent Nonlocking Reads chapter of MySQL manual on further details of this feature.

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562230

Yes, you can update/insert/delete while an existing transaction holds a repeatable-read snapshot on the data.

This is implemented by Multi-Version Concurrency Control or MVCC.

It's a fancy way of saying that the RDBMS keeps multiple versions of the same row(s), so that repeatable-read snapshots can continue reading the older version as long as they need to (that is, as long as their transaction snapshot exists).

If a row version exists that was created by a transaction that committed after your transaction started, you shouldn't be able to see that row version. Every row version internally keeps some metadata about the transaction that created it, and every transaction knows how to use this to determine if it should see the row version or not.

Eventually, all transactions that may be interested in the old row versions finish, and the MVCC can "clean up" the obsolete row versions.

Upvotes: 5

Related Questions