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

sql_mode=ORACLE: 'SELECT INTO @var FOR UPDATE' does not lock the table

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.4
    • 10.4.6
    • Parser
    • None

    Description

      I run this test:

      --source include/have_innodb.inc
       
      SET sql_mode='';
      CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) engine=innodb;
      INSERT INTO t1 VALUES (1);
      START TRANSACTION;
      SELECT a AS a_con1 FROM t1 INTO @a FOR UPDATE;
       
      --connect(con2,localhost,root,,)
      SET sql_mode='';
      START TRANSACTION;
      --send SELECT a AS a_con2 FROM t1 INTO @a FOR UPDATE;
       
      --connection default
      UPDATE t1 SET a=a+100;
      COMMIT;
       
      --connection con2
      --reap
      SELECT a AS con2 FROM t1;
      COMMIT;
       
      --connection default
      DROP TABLE t1;
      

      It returns this fragment in the end of the output:

      con2
      101
      

      Now I change sql_mode to 'ORACLE' in the test (in two places):

      --source include/have_innodb.inc
       
      SET sql_mode='ORACLE';
      CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) engine=innodb;
      INSERT INTO t1 VALUES (1);
      START TRANSACTION;
      SELECT a AS a_con1 FROM t1 INTO @a FOR UPDATE;
       
      --connect(con2,localhost,root,,)
      SET sql_mode='ORACLE';
      START TRANSACTION;
      --send SELECT a AS a_con2 FROM t1 INTO @a FOR UPDATE;
       
      --connection default
      UPDATE t1 SET a=a+100;
      COMMIT;
       
      --connection con2
      --reap
      SELECT a AS con2 FROM t1;
      COMMIT;
       
      --connection default
      DROP TABLE t1;
      

      and rerun the test. The fragment changes to:

      con2
      1
      

      Notice, it ignores the FOR UPDATE clause.

      The problem resides in this grammar:

      select:
                query_expression_body
                {
                  if (Lex->push_select($1->fake_select_lex ?
                                       $1->fake_select_lex :
                                       $1->first_select()))
                    MYSQL_YYABORT;
                }
                opt_procedure_or_into
                {
                  Lex->pop_select();
                  if ($1->set_lock_to_the_last_select($3))
                    MYSQL_YYABORT;
                  if (Lex->select_finalize($1))
                    MYSQL_YYABORT;
                }
              | with_clause query_expression_body
                {
                  if (Lex->push_select($2->fake_select_lex ?
                                       $2->fake_select_lex :
                                       $2->first_select()))
                    MYSQL_YYABORT;
                }
                opt_procedure_or_into
                {
                  Lex->pop_select();
                  $2->set_with_clause($1);
                  $1->attach_to($2->first_select());
                  if ($2->set_lock_to_the_last_select($4))
                    MYSQL_YYABORT;
                  if (Lex->select_finalize($2))
                    MYSQL_YYABORT;
                }
              ;
      

      The sql_yacc.yy version looks correct. The same grammar in sql_yacc_ora.yy missed these lines:

                  if ($1->set_lock_to_the_last_select($3))
                    MYSQL_YYABORT;
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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