What 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql1> set transaction isolation level serializable; Query OK, 0 rows affected (0,00 sec) mysql1> begin; Query OK, 0 rows affected (0,00 sec) mysql1> select * from employees join titles using(emp_no); c936e6fc4c6cbaf77679ba5013339dff - 443308 rows in set (29,69 sec) mysql2> begin; Query OK, 0 rows affected (0,00 sec) mysql2> update titles set title='Engineer' where emp_no=15504; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
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.
Resources
RELATED POSTS
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.
Thanks a lot
I also blogged about this awhile ago:
https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/
‘@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.”
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:
—-—-
http://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html