1

I have two sessions doing this.

Session 1>start transaction;
Session 1>select * from account for update;

Session 2>start transaction;
Session 2>select * from account for update; //waiting.

Since IX lock is compatible with IX lock shouldn't session two also get IX lock without waiting.

Even the following set of statemnts do not work.

Session 1>start transaction;
Session 1>select * from account where i = 1 for update;

Session 2>start transaction;
Session 2>select * from account where i = 2 for update; //waiting.

The isolation level is Repeatable Read. Are things different in this isolation level?

Source for compatibility this

subject-q
  • 91
  • 3
  • 19

2 Answers2

2

The two queries you show acquire IX locks for the table, which are compatible with each other.

But SELECT...FOR UPDATE also proceeds to acquire X locks on the rows it examines. These are conflicts, and that's why the second SELECT...FOR UPDATE waits.

In your example where one query is for rows WHERE i = 1 and the other is for rows WHERE i = 2, they will not return the same rows, but they will examine an overlapping set of rows if i has no index. Examining rows means InnoDB fetches the rows and then tests them against the conditions in the WHERE clause. Based on the conditions, it may skip some rows it examines, and it only returns examined rows if they test true.

If you have an index on i, InnoDB can exclude non-matching rows without examining the rows. Under these conditions, your example will show no conflict.

Session 1>ALTER TABLE account ADD INDEX (i);

Session 1>start transaction;
Session 1>select * from account where i = 1 for update;

Session 2>start transaction;
Session 2>select * from account where i = 2 for update; // does NOT wait
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you. Coincidentally I saw your stick figures video in percona yesterday. Though it helped me exponentially to grasp concepts, I think I have a long way to travel before I understand these concepts as second nature. Can you please suggest a book or any material (other than dev.mysql.com) for a beginner to master the inno db internals, mainly locking and transaction? Thanks in advance. – subject-q Mar 29 '19 at 12:57
  • 1
    I've never seen a comprehensive book about InnoDB locking. I've been told by the former engineering manager for MySQL that no one person understands everything about InnoDB locking, even among people who have worked on InnoDB internals. I've gathered my knowledge from many years of reading books and blogs, attending conference presentations, chatting with engineers, testing MySQL on my own, and even reading source code. My knowledge is still far from complete. – Bill Karwin Mar 29 '19 at 13:06
  • 1
    My stick figures presentation was fun, I had the idea after I wrote this Stack Overflow answer in 2014: https://stackoverflow.com/questions/25903764/why-is-an-ix-lock-compatible-with-another-ix-lock-in-innodb/26331155#26331155 But the presentation was less than one hour, and of course there's a lot more to InnoDB locking that I couldn't cover. – Bill Karwin Mar 29 '19 at 13:08
  • 1
    i think you are being modest. i found rewatching your video a couple of times, reading dev.mysql.com couple of times useful. And I understand that this is a huge thing to be comprehensively discussed in a book. Thanks. Please, in future you happen to find any link/material that could help, post it here. What I find lacking in the documentation is missing use cases, that could improve understanding. But that is not what documentation is for. :( – subject-q Mar 29 '19 at 13:14
0

When using SELECT ... FOR UPDATE, MySQL will attempt to get an IX (intention exclusive lock) lock on all rows and associated index entries which the query would return. From the documentation:

For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows.

One of your two transactions will execute the FOR UPDATE first, and whichever one succeeds, will then lock the entire table, since the select is a SELECT * without any WHERE clause.

A more realistic real world scenario would be a situation where one transaction locks a few records for update, and another transaction concurrently locks some other rows. In this case, both transactions may be able to proceed at the same time. Here is an example of two selects for update which would not lock the same records (assuming both result sets would not be empty)

SELECT * FROM yourTable WHERE id = 1 FOR UPDATE;
SELECT * FROM yourTable WHERE id = 2 FOR UPDATE;

If the above would still see the second later transaction blocking, it could be because MySQL can sometimes also lock gaps after the actual target records.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • So two transactions can get IX on same table, if the transactions do `select for ... update` on exclusive rows (the rows get x locked). If a filter less `select *` is done with `select ... for update` the table is X locked instead of IX locked? – subject-q Mar 29 '19 at 10:40
  • I don't understand your second sentence. Can you explain it better? – Tim Biegeleisen Mar 29 '19 at 10:44
  • You mentioned the table was locked because the query was `select *`. So I updated the question. I cant seem to get IX locks on a table by 2 transactions. – subject-q Mar 29 '19 at 10:51
  • ...and I explained why, because the first `select * for update` which runs will lock the entire table, during which time it won't be possible for any other transaction to obtain an `IX` lock anywhere in that same table. – Tim Biegeleisen Mar 29 '19 at 10:52
  • May be I am misunderstanding something. Cannot figure out which part. Can you give me an example in which case, two transactions will get IX lock on same table? In `repeatable read` isolation mode? – subject-q Mar 29 '19 at 10:55
  • Here is an example of two tx get IX lock on same table. Execute in two session one after another. The second select query would not be blocked since two IX locks are compatible. create table g_test_deadlock(`id` bigint(20) primary key auto_increment,`name` varchar(100), key `k_name` (`name`)); begin; select * from g_test_deadlock where name='aaa' for update; – Lebecca Sep 22 '19 at 06:33