Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6, 10.11, 10.1(EOL), 10.3(EOL), 10.4(EOL), 10.5(EOL)
-
None
Description
The IF operator implicitly sets SHARED LOCK on the rows returned by the query from search_condition.
To demonstrate this behavior, follow the steps below:
1) Preliminary stage
1.1) Create a database and a table. Add several rows to the table.
DROP DATABASE IF EXISTS `test`; |
|
CREATE DATABASE `test`; |
|
USE `test`; |
|
CREATE TABLE `test_table` (id INT PRIMARY KEY, val1 INT); |
|
INSERT INTO `test_table` VALUES (1,1), (2,2), (3,3), (4,4); |
1.2) Create 3 stored procedures
DELIMITER //
|
CREATE PROCEDURE `check_if_exists` ( |
IN `__id` INT, |
INOUT `row_exists` BOOL
|
)
|
BEGIN
|
SET `row_exists` = 0; |
IF (EXISTS (SELECT 1 FROM `test_table` WHERE `id` = `__id`)) THEN |
SET `row_exists` = 1 ; |
END IF; |
END // |
DELIMITER ;
|
|
DROP PROCEDURE `check_if_exists2`; |
DELIMITER //
|
CREATE PROCEDURE `check_if_exists2` ( |
IN `__id` INT, |
INOUT `row_exists` BOOL
|
)
|
BEGIN
|
SET `row_exists` = IF(EXISTS (SELECT 1 FROM `test_table` WHERE `id` = `__id`), 1, 0); |
END // |
DELIMITER ;
|
|
DROP PROCEDURE `check_if_exists3`; |
DELIMITER //
|
CREATE PROCEDURE `check_if_exists3` ( |
IN `__id` INT, |
INOUT `row_exists` BOOL
|
)
|
BEGIN
|
SELECT CASE WHEN EXISTS (SELECT 1 FROM `test_table` WHERE `id` = `__id`) THEN 1 ELSE 0 END INTO `row_exists`; |
END // |
DELIMITER ;
|
2) In session #1, we will start the transaction and execute the following query:
START TRANSACTION; |
|
SELECT * FROM `test_table` FOR UPDATE; |
3) Now, let's do a series of experiments in session #2.
3.1) Let's run the stored procedure check_if_exists:
SET @result = 0; |
CALL `check_if_exists`(1, @result);
|
If the query is not terminated, it will end with an error:
SQL Error (1205): Lock wait timeout exceeded; try restarting transaction
While the transaction in session #1 is active, the stored procedure check_if_exists will wait for the lock to be released in order to set a SHARED LOCK on the row of the test_table table with ID 1.
This can be easily confirmed by executing the following query in any other session:
SELECT |
locked_table,
|
locked_index,
|
locked_type,
|
waiting_query,
|
waiting_lock_mode
|
FROM sys.innodb_lock_waits; |
Query result:
locked_table | locked_index | locked_type | waiting_query | waiting_lock_mode |
---|---|---|---|---|
`test`.`test_table` | PRIMARY | RECORD | CALL `check_if_exists`(1, @result) | S |
3.2) The behavior described in the previous paragraph will also be observed when executing the stored procedure check_if_exists2.
3.3) However, executing the stored procedure check_if_exists3 does not result in any errors and completes successfully, returning the correct result.
SET @result = 0; |
CALL `check_if_exists3`(1, @result);
|
SELECT @result; |
Why does the IF operator implicitly set a SHARED LOCK? Is this expected behavior or a bug?
Which section of the documentation explains this behavior?