CODEWITHSUNDEEP
mysqldatabasedeadlockdatabase-deadlocks
chinna_82
chinna_82

Reputation: 6403

Database deadlock by select statement

I understand that deadlock occurs when two or more entities are blocking some sources, and none of them is able to finish, because their are blocking sources in a cyclic way.

Can deadlock happens for select statement.? Does select statement block the resource.? I believe inserting or updating will block the row but not very sure for select statement. Please advice.

Upvotes: 2

Views: 594

Answers (1)

Vérace
Vérace

Reputation: 908

With the InnoDB storage engine (the default), reads are non-blocking - so two selects cannot block each other. InnoDB is a versioning engine using MVCC (multi-version concurrency control) meaning that a transaction (A) will take a copy of the records of interest at a moment in time - if it modifies and then commits the data, any transaction starting after that commit will see the new copy, but transactions starting before that any transaction starting (B) after A starts but before A commits will see the copy that A saw. Deadlocks can only occur if one of the transactions modifies the data (locking occurs in InnoDB on a row-level basis).

For further information, see here and here. For MyISAM tables (about which I know very little!), check out the comparison here or just Google "MyISAM vs InnoDB" (129,000 hits.

Upvotes: 1

Related Questions