Bug #119065 Why does the EXISTS operator not create an S lock in the first case, but create one in the second?

• Submitted: 24 Sep 12:32
• Reporter: Zafar Malik
• Status: Open
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 8.4.4 (Any)
• OS: Any
• CPU Architecture: Any
• Tags: lock, My SQL, query analyzer

[24 Sep 12:32] Zafar Malik

Description:
Why does the EXISTS operator not create an S lock in the first case, but create one in the second?

CASE 1:
`SET bExists = EXISTS(SELECT ...);` requests S-lock while

CASE 2:
`SELECT EXISTS(SELECT...) INTO bExists;` does not request any lock.

Thanks,
Zafar

How to repeat:
Testing script:

CREATE SCHEMA test1;
CREATE TABLE test1.t(i int);
INSERT INTO test1.t VALUES(1),(2),(3),(4),(5);
SELECT * FROM test1.t;
 
DROP PROCEDURE IF EXISTS test1.p;
DELIMITER $$
CREATE PROCEDURE test1.p()
BEGIN
     DECLARE v_i INT;
 
     SELECT EXISTS(SELECT 1 FROM test1.t)
     INTO v_i;
END$$
DELIMITER ;
 
START TRANSACTION;
 
CALL test1.p();
SELECT * FROM performance_schema.data_locks;
 
ROLLBACK;
 
DROP PROCEDURE IF EXISTS test1.p;
DELIMITER $$
CREATE PROCEDURE test1.p()
BEGIN
     DECLARE v_i INT;
 
     SET v_i = EXISTS(SELECT 1 FROM test1.t);
END$$
DELIMITER ;
 
START TRANSACTION;
 
CALL test1.p();
SELECT * FROM performance_schema.data_locks;
 
ROLLBACK;
 
Script output:
> CREATE SCHEMA test1
 
1 row(s) modified.
 
> CREATE TABLE test1.t(i int)
 
0 row(s) modified.
 
> INSERT INTO test1.t VALUES(1),(2),(3),(4),(5)
 
5 row(s) modified.
 
 
> SELECT * FROM test1.t
 
i|
-+
1|
2|
3|
4|
5|
 
5 row(s) fetched.
 
> DROP PROCEDURE IF EXISTS test1.p
 
0 row(s) modified.
 
> CREATE PROCEDURE test1.p()
BEGIN
     DECLARE v_i INT;
 
     SELECT EXISTS(SELECT 1 FROM test1.t)
     INTO v_i;
END
 
0 row(s) modified.
 
> START TRANSACTION
 
0 row(s) modified.
 
> CALL test1.p()
 
1 row(s) modified.
 
 
> SELECT * FROM performance_schema.data_locks
 
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|
------+--------------+---------------------+---------+--------+-------------+-----------+--------------+-----------------+----------+---------------------+---------+---------+-----------+---------+
 
0 row(s) fetched.
 
PROCEDURE test1.p does not exist
> ROLLBACK
 
0 row(s) modified.
 
> DROP PROCEDURE IF EXISTS test1.p
 
0 row(s) modified.
 
> CREATE PROCEDURE test1.p()
BEGIN
     DECLARE v_i INT;
 
     SET v_i = EXISTS(SELECT 1 FROM test1.t);
END
 
0 row(s) modified.
 
> START TRANSACTION
 
0 row(s) modified.
 
> CALL test1.p()
 
0 row(s) modified.
 
 
> SELECT * FROM performance_schema.data_locks
 
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|135977354276864:346:1553:135977255365552   |      417452330987520|       64|   47832|test1        |t          |              |                 |               |      135977255365552|TABLE    |IS       |GRANTED    |              |
INNODB|135977354276864:346:487:4:2:135977255362640|      417452330987520|       64|   47837|test1        |t          |              |                 |GEN_CLUST_INDEX|      135977255362640|RECORD   |S        |GRANTED    |0x00000000020A|
 
2 row(s) fetched.
 
> ROLLBACK
 
0 row(s) modified.