MySQL Bugs: #111598: SELECT FOR SHARE does not acquire lock correctly

Bug #111598	SELECT FOR SHARE does not acquire lock correctly
Submitted:	28 Jun 2023 10:38	Modified:	30 Jun 2023 13:28
Reporter:	Siyang Weng	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	8.0.33	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	lock

Description:
LOCK IN SHARE MODE acts differently when the query runs with/without index.

How to repeat:
Schema:

CREATE TABLE `table0` (
  `pkId` int DEFAULT NULL,
  `pkAttr0` int NOT NULL,
  `coAttr0_0` int DEFAULT NULL,
  PRIMARY KEY (`pkAttr0`),
  KEY `table0index_pk` (`pkAttr0`),
  KEY `table0index_commAttr0` (`coAttr0_0`)
)

insert into table0 values(1,1,1);

Then execute:
session1 > begin;
session1 > update table0 set coAttr0_0=2;
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- is not blocked
session2 > select * from table0 for share; -- blocked

These two queries should act in the same way(be blocked), but the first one is not blocked.

We explain the plans of queries, and find that the first one run with index.

So we check the data lock by 'SELECT * from performance_schema.data_locks;', and it returns:
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+----------------+-----------------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID                           | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME     | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+----------------+-----------------------+-----------+-----------+-------------+------------------------+
| INNODB | 140020761492696:2892:140020765741776     |              10811702 |        50 |       54 | test          | table0      | NULL           | NULL              | NULL           |       140020765741776 | TABLE     | IX        | GRANTED     | NULL                   |
| INNODB | 140020761492696:1826:4:1:140020765738784 |              10811702 |        50 |       54 | test          | table0      | NULL           | NULL              | PRIMARY        |       140020765738784 | RECORD    | X         | GRANTED     | supremum pseudo-record |
| INNODB | 140020761492696:1826:4:2:140020765738784 |              10811702 |        50 |       54 | test          | table0      | NULL           | NULL              | PRIMARY        |       140020765738784 | RECORD    | X         | GRANTED     | 1                      |
| INNODB | 140020761493504:2892:140020765747872     |       421495738204160 |        51 |      104 | test          | table0      | NULL           | NULL              | NULL           |       140020765747872 | TABLE     | IS        | GRANTED     | NULL                   |
| INNODB | 140020761493504:1826:5:1:140020765744960 |       421495738204160 |        51 |      104 | test          | table0      | NULL           | NULL              | table0index_pk |       140020765744960 | RECORD    | S         | GRANTED     | supremum pseudo-record |
| INNODB | 140020761493504:1826:5:2:140020765744960 |       421495738204160 |        51 |      104 | test          | table0      | NULL           | NULL              | table0index_pk |       140020765744960 | RECORD    | S         | GRANTED     | 1                      |
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+----------------+-----------------------+-----------+-----------+-------------+------------------------+
6 rows in set (0.01 sec)

It seems that record(1) has both exclusive lock and share lock on different indexes.

Suggested fix:
 'select * from table0 for share;' and 'select pkAttr0 from table0 for share;' should both be blocked when an update query is uncommitted, as what doc says:

"If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values."

HI Mr. Weng,

Thank you for your bug report.

However, it is not a bug.

To print out your sessions:

session1 > begin;
session1 > update table0 set coAttr0_0=2;
session2 > begin;
session2 > select pkAttr0 from table0 for share; -- is not blocked
session2 > select * from table0 for share; -- blocked

The UPDATE will not block anything since the filtering conditions does not match any rows.

Your last query is blocked due to the previous one in the same session.

This is all described in any MVCC textbook.

Not a bug.

Maybe my description is not clear enough, it seems the reply may misunderstand something. Three more simple cases are provided here.

Both of these cases uses the initial schema as above. Note that there is a record(1,1,1) in the initial database.

Case1:
session1> begin;
session1> update table0 set coAttr0_0=2; -- Here is NO filter, so the query will 
update the only record, i.e. Rows matched: 1
session2> select * from table0 for share; -- blocked

Case2:
session1> begin;
session1> update table0 set coAttr0_0=2; -- Here is NO filter, so the query will 
update the only record, i.e. Rows matched: 1
session2> select pkAttr0 from table0 for share; -- no blocked

Case3:
session1> begin;
session1> update table0 set coAttr0_0=2; -- Here is NO filter, so the query will 
update the only record, i.e. Rows matched: 1
session2> update table0 set coAttr0_0=2 where pkAttr0=1; -- blocked

Case3 shows that the update query in session1 updates the only record and blocks other session's write query after it. Note that here is NO filtering conditions in the update query, the UPDATE will match the only row in the table, instead of "does not match any rows".

Case1 and Case2 show that with the same update query as Case3, 'select pkAttr0 from table0 for share;' will not be blocked, but 'select * from table0 for share;' will be blocked. These two behaviours are independent. 
So 'select * from table0 for share;' is blocked due to the UPDATE, insteal of "last query is blocked due to the previous one in the same session.".

Hi,

In our Manual we have a table with compatibility of the locks.

There are table locks and row locks. Among the row locks there are  S and X locks, with many variants.

So, everything would be clear for your if you analyse the output from the innodb status after each command.

This is all described in our Reference Manual.

Not a bug.

I still have several questions about your response.

Considering you mentioned that "This is all described in our Reference Manual", we search the relevent descriptions on the MySQL Reference Manual.

In Chap. 15.7.1 Innodb locking[1] and Chap 15.7.2.4 Locking Reads[2], Reference Manual describes that:

a. "An exclusive (X) lock permits the transaction that holds the lock to update or delete a row."
b. "SELECT ... FOR SHARE sets an IS lock,"
c. "If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values."
And the lock table shows IS is conflict with X.

So let's look back to Case 2. In session1, here is an uncommitted transaction updated record(1), so it should hold an exclusive lock(as sentence a said). 
Then, session2 start a SELECT ... FOR SHARE, and it should set an IS lock on record(1)(as sentence b said).
In Case 2, these two queries are executed, so record(1) holds both X lock and IS lock, which is conflict with the lock table in Reference Manual.
To ensure record(1) holds both IS and X lock, we check the data lock table in the origin case. The 3rd row shows that record(1) holds an X lock, and the 6th row shows record(1) holds an IS lock. They are both row lock.

In Case2, record(1) was changed by the transaction in session1, and this trx has not committed. Then SELECT ... FOR SHARE should wait until it commits(as sentence c said). But it executes.

These phenomenons lead to three questions as follows:

1. Does the 3rd and 6th row in the data lock table describe different records/objects in table0? If so, what are they? If not, is it conflict for a record to hold both X and IS lock?
2. Should 'select pkAttr0 from table0 for share;' and 'select * from table0 for share;' acquire different locks when they read only one row? If so, which locks should they hold?
3. When other transactions modify that the scanning row does not end, should SELECT ... FOR SHARE keeps waiting? If so, why SELECT ... FOR SHARE can execute in Case 2? If not, why SELECT ... FOR SHARE cannot execute in Case 1?

I try my best to find the answers of these questions in Reference Manual but failed. 
Can you provide the web link that describes the more detail about you mentioned Reference Manual?

[1] https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
[2] https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

Hi,

Our Reference Manual is there to teach the basic features, models and principles of our software It is not there to answer your question.

We already advised you to look at the full InnoDB status in each of these situations to see the locks that are set. There are also table in I_S and P_S to help you with that. But, in the end, it is you  should figure it out how the locking works.

Based on what you have sent us, we can easily conclude that there is no bug.

This forum is not intended for the explanation of how our server functions.

HI ,

One short addition.

Intended Shared locks are table level locks, not row level locks. Gray's book on "Transactions processing" has a table of the compatibility with IS, IX and S and X locks.

A copy of that table is also in our Manual, here:

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

Thanks for your reply!

One last question:

Should MySQL users assume that 'select pkAttr0 from table0 for share;' and 'select * from table0 for share;' have the same lock behaviour in the same situation?

Hi,

The answer is : YES.

But in fact, they are not the same in the cases below:

Let's start with the same schema:

CREATE TABLE `table0` (
  `pkId` int DEFAULT NULL,
  `pkAttr0` int NOT NULL,
  `coAttr0_0` int DEFAULT NULL,
  PRIMARY KEY (`pkAttr0`),
  KEY `table0index_pk` (`pkAttr0`),
  KEY `table0index_commAttr0` (`coAttr0_0`)
)

And the same execution situation:

session1> insert into table0 values(1,1,1);
session1> begin;
session1> update table0 set coAttr0_0=2; 

Then execute two queries:

Case1:
session2> select * from table0 for share; -- blocked

Case2:
session2> select pkAttr0 from table0 for share; -- no blocked

In Case 1, 'select * from table0 for share;' is blocked; in Case 2, 'select pkAttr0 from table0 for share;' is not blocked. Except for this query, the two cases are totally the same. This confuses me and makes me report this issue.

Yes,

You were correct.

In one case the row is locked and in another only index entry is locked.

This is described in our Reference Manual.

This is clearly depicted in the extended InnoDB status .....

Not a bug.