CODEWITHSUNDEEP
mysqlconcurrencylocking
Vojtěch
Vojtěch

Reputation: 12416

MySQL isolation levels performance

This is a follow-up question for Transaction isolation levels relation with locks on table

I wonder about the speed differences between the isolation levels. What speed benefits can you achieve switching between the levels? Can you demonstrate when each of these can lead to lock waiting comparing to the other?

I also wonder: Doing this update query:

UPDATE product SET sold = 1 WHERE id = 10 AND sold = 0;

If someone modifies the flag sold during execution of the query, would it have different consequences with these acquisition levels?

Upvotes: 1

Views: 601

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562310

Gap locking is a common difference between READ COMMITTED and REPEATABLE READ. This can affect lock-wait times if you have concurrent queries in contention for the same gap locks.

In the UPDATE query you show, assuming id is the primary key, the query should use the primary key index to select the single row, therefore there will be no gap locks for that query.

Re your comment:

Locking has no impact on performance. Lock waits may delay the start of execution if the query has to wait to acquire locks it needs for the query, but once the execution starts, it will have the same performance in any transaction isolation level.

Also, if there aren't any concurrent queries that hold the needed locks, there will be no additional waiting for locks.

Upvotes: 2

Related Questions