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.
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.