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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4
    • Fix Version/s: 10.4.6
    • Component/s: Parser
    • Labels:
      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

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

                Dates

                • Created:
                  Updated:
                  Resolved: