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

SELECT page in the KB is inaccurate in regard to locking options

    XMLWordPrintable

    Details

      Description

      As of now, the KB page for SELECT statement goes like this:

            [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options]
       
      INTO DUMPFILE 'file_name'	INTO var_name [, var_name] ]
       
            [[FOR UPDATE | LOCK IN SHARE MODE] [WAIT n | NOWAIT] ] [SKIP LOCKED] ]
      
      

      Off-topic: INTO DUMPFILE has lost a square bracket and is shown as mandatory, it's obviously wrong.

      Thus, the locking part

            [[FOR UPDATE | LOCK IN SHARE MODE] [WAIT n | NOWAIT] ] [SKIP LOCKED] ]
      

      expands into these combinations, if I got them right:

      <nothing>
      FOR UPDATE
      LOCK IN SHARE MODE
      WAIT n
      NOWAIT
      FOR UPDATE WAIT n
      FOR UPDATE NOWAIT
      LOCK IN SHARE MODE WAIT n
      LOCK IN SHARE MODE NOWAIT
      SKIP LOCKED
      FOR UPDATE SKIP LOCKED
      LOCK IN SHARE MODE SKIP LOCKED
      WAIT n SKIP LOCKED
      NOWAIT SKIP LOCKED
      FOR UPDATE WAIT n SKIP LOCKED
      FOR UPDATE NOWAIT SKIP LOCKED
      LOCK IN SHARE MODE WAIT n SKIP LOCKED
      LOCK IN SHARE MODE NOWAIT SKIP LOCKED
      

      But this is not true. These ones don't work:

      10.6 88af187db9

      MariaDB [test]> SELECT * FROM t1 WAIT 1;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1' at line 1
      MariaDB [test]> SELECT * FROM t1 SKIP LOCKED;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LOCKED' at line 1
      MariaDB [test]> SELECT * FROM t1 WAIT 1 SKIP LOCKED;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1 SKIP LOCKED' at line 1
      MariaDB [test]> SELECT * FROM t1 NOWAIT SKIP LOCKED;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SKIP LOCKED' at line 1
      

      The first one can actually be a bug, it is strange that this works:

      MariaDB [test]> SELECT * FROM t1 NOWAIT;
      Empty set (0.000 sec)
      

      But this doesn't:

      MariaDB [test]> SELECT * FROM t1 WAIT 1;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1' at line 1
      

      The ones with SKIP LOCKED are most likely rejected by design, so it just needs to be documented more precisely.

        Attachments

          Activity

            People

            Assignee:
            danblack Daniel Black
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration