InnoDB locks and transaction isolationWhat is the difference between InnoDB locks and transaction isolation level? We’ll discuss it in this post.

Recently I received a question from a user about one of my earlier blog posts. Since it wasn’t sent as a comment, I will answer it here. The question:

> I am reading your article:
> https://www.percona.com/resources/technical-presentations/troubleshooting-locking-issues-percona-mysql-webinar

> Full table scan locks whole table.

> Some bad select (read) query can do full table scan on InnoDB, does it lock whole table please?

> My understanding was that SELECT (read) blocks another DML only in MyISAM.

To answer this question, we to need understand two different concepts: locking and transaction isolation level.

In the default transaction isolation mode for InnoDB,  REPEATABLE READ and the lower TRANSACTION ISOLATION levels, SELECT  doesn’t block any DML unless it uses  SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE. On slide 20 (31) of my presentation, I use the  SELECT ... FOR UPDATE statement (this is why a lock is seen).

However, if transaction isolation mode is SERIALIZABLE, then  SELECT can block updates. You can see this in the example below:

Regarding the different level locks set by InnoDB, I recommend that you follow the links on slide 37 (67) of the presentation, especially “InnoDB Locking Explained With Stick Figures” by Bill Karwin. Innodb table locks are also explained very well in this blog post by Peter Zaitsev.

6 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Peter Colclough

Interesting. In the case of ‘file per table’ with Innodb, do you not also get a FS lock on tge physical file, when issuing a select? I have seen this in a few instances, and have got around it by setting a different isolation level.

0
John

Based on my experience, transactions in InnoDB never directly visit files but buffer pool pages. Operations of load/swap/flush are InnoDB’s duty. If a “select” result in a file system lock, I think InnoDB buffer pool size may be too tiny for your case, so that InnoDB frequently call the file handler.

0
james

Thanks a lot

0
vidyadhar

@sveta, It is very good important information mentioned here as well as in the blog written by @Justin Swanhart. If you explain how the search works in case when record which we are looking is part of undo logs and not present in the user snapshot. To be specific, I am talking about below line from the documentation.

” When a secondary index record is delete-marked or the secondary index page is updated by a newer transaction, InnoDB looks up the database record in the clustered index.”

0
Sveta Smirnova

Hi Vidyadhar,

I am sorry for the late reply.

I assume user manual says about a situation like this:

– You have a table t1(id INT NOT NULL PRIMARY KEY, f1 INT, …, KEY(f1)
– You have few records in this table, say, (1,1), (2,2), (3,3), (4,4), (5,5)
– You update one of records, say, (3,3) changed to (3,5)
– InnoDB puts change into redo log file
– InnoDB updates record DB_TRX_ID in the clustered index with transaction number
– Another transaction searches, using index f1: SELECT/UPDATE …. WHERE f1 = 3
– InnoDB, after finds the record, checks value of DB_TRX_ID in the clustered index and, if needed, retrieves new data from the redo log file.

Regarding locks nothing changed if compare to cases when transaction is not in the redo log file.

There is also a sense to put here more full quote from the user manual:

—-—-
When a secondary index column is updated, old secondary index records are delete-marked, new records are inserted, and delete-marked records are eventually purged. When a secondary index record is delete-marked or the secondary index page is updated by a newer transaction, InnoDB looks up the database record in the clustered index. In the clustered index, the record’s DB_TRX_ID is checked, and the correct version of the record is retrieved from the undo log if the record was modified after the reading transaction was initiated.
—-—-

http://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html

0