Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-37832

The IF operator implicitly sets SHARED LOCK

    XMLWordPrintable

Details

    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?

      Attachments

        Activity

          People

            marko Marko Mäkelä
            stanly Stan Lyubich
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.