MySQL exclusive lock

Question

We have the tables below:

mysql> desc journeys ;
+---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| journey_id    | char(36)   | NO   | PRI | NULL    |       |
| is_completed  | tinyint(1) | NO   |     | 0       |       |
| user_id       | char(36)   | NO   |     | NULL    |       |
| created_at    | datetime   | NO   |     | NULL    |       |
| updated_at    | datetime   | NO   |     | NULL    |       |
| pack_id       | char(36)   | YES  | MUL | NULL    |       |
| family_id     | char(36)   | YES  | MUL | NULL    |       |
+---------------+------------+------+-----+---------+-------+

mysql> desc packs ;
+---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| pack_id       | char(36)   | NO   | PRI | NULL    |       |
| is_published  | tinyint(1) | NO   |     | 0       |       |
| order         | int(11)    | NO   |     | NULL    |       |
| created_at    | datetime   | NO   |     | NULL    |       |
| updated_at    | datetime   | NO   |     | NULL    |       |
| family_id     | char(36)   | NO   | MUL | NULL    |       |
+---------------+------------+------+-----+---------+-------+

The isolation level is REPEATABLE_READ.

According to the glossary here: https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_exclusive_lock

An exclusive lock is kind of lock that prevents any other transaction from locking the same row. Depending on the transaction isolation level, this kind of lock might block other transactions from writing to the same row, or might also block other transactions from reading the same row.

The logic that we have looks like the one below (the user_id takes different values):

START TRANSACTION;
SELECT * FROM journeys WHERE user_id = <user_id> FOR UPDATE ;
# COMMIT;

Below there is a bit of testing. I open one terminal window (terminal #1) and I execute the statements below:

START TRANSACTION;
SELECT * FROM journeys WHERE user_id = user_id_1 FOR UPDATE ;

Then I open a second terminal (terminal #2) window and I execute the statements below:

START TRANSACTION;
SELECT * FROM journeys WHERE user_id = user_id_2 FOR UPDATE ;

The terminal #2 now halts because we never committed the transaction on terminal #1.

My assumption was that because the condition on the first terminal #1 is different from the statement in terminal #2 that the second terminal won't wait for the first one to commit. I'm basing my assumption on the definition of an exclusive lock which says that the exclusive lock prevents any other transaction from locking the same row. Is this a wrong assumption? If yes how can achieve to lock the rows that we have in the first condition?

It seems that it is different when I use the primary key in the condition. In the case below the terminal #2 is not waiting for the terminal #1 to commit.

terminal #1
START TRANSACTION;
SELECT * FROM journeys WHERE journey_id = journey_id_1 FOR UPDATE ;

Statements in terminal #2

terminal #2
START TRANSACTION;
SELECT * FROM journeys WHERE journey_id = journey_id_2 FOR UPDATE ;

What happens exactly with the exclusive locks when we have conditions that do not include primary keys? Are we locking the whole table?

score 2 · Accepted Answer · answered Mar 25 '20 at 22:00

2

Yes, you're locking all the rows in the table when you have a condition on an unindexed column like user_id.

The locks apply to all "examined" rows. Your condition WHERE user_id = <user_id> must examine all the rows in the table, and test them one by one to see if they match the value of <user_id>.

Both queries are examining the whole set of rows, even though they are searching for different specific values of <user_id>, so they conflict.

If you had an index on the user_id column, then MySQL would use that index to find the matching rows first, then only matching rows would become examined rows, and therefore would be locked.

This has nothing to do with the transaction isolation level. These types of locks occur in all transaction isolation levels.

answered Mar 25 '20 at 22:00

Bill Karwin

538,548
86
673
828

1

That's very helpful @Bill Karwin! So by adding an index to the user_id we can solve our use case. – Stavros Zavrakas Mar 26 '20 at 06:44
3

If you add the index, AND MySQL decides to use it. There are cases when the MySQL optimizer decides it's not worth using the index. For example, if the value you're searching for occurs on > 20% of the rows. – Bill Karwin Mar 26 '20 at 15:41
1

hi @Bill Karwin, excellent answer, I have a doubt to finish understanding. So, "by default" the UPDATE/DELETE/SELECT (the WHERE clause itself) will examine LITERALLY ALL ROWS of the tables involved, is that right? – Dec 30 '22 at 17:51
2

@GeorgeMeijer, Yes, if your search is not supported by an index, then it's a table-scan, which means all rows become examined rows. In a locking query, all examined rows become locked, even if eventually they will be filtered out by the condition. It's very important to use an index to reduce the set of examined rows! – Bill Karwin Dec 30 '22 at 18:02
In a `JOIN` (`INNER`/`RIGHT`/`LEFT`) (comparing foreign key with primary key) all records in the left table are compared with all records in the right table, with indexes, would all records still be *scanned*? @Bill Karwin – jwa Jul 04 '23 at 03:35

MySQL exclusive lock

1 Answers1

Linked