CODEWITHSUNDEEP
mysqlsql
Coder Al
Coder Al

Reputation: 49

Does a SELECT with a JOIN lock a table like UPDATE does?

If you were to SELECT and JOIN a large table that takes 10 minutes, would the table be locked so that data cannot be written to it?

From what I understand, there can be reads from the table while the SELECT JOIN is executing, how about UPDATEs or INSERTs?

Upvotes: 0

Views: 354

Answers (1)

Tangentially Perpendicular
Tangentially Perpendicular

Reputation: 5358

With InnoDB SELECT reads a snapshot created at the point in time that it's executed. Subsequent changes won't be visible. This isolation is the 'I' in 'ACID'

The MySQL manual says this:

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions.

The level of isolation can be controlled with various parameters. More details here

Upvotes: 2

Related Questions