[MDEV-25383] SELECT page in the KB is inaccurate in regard to locking options Created: 2021-04-10  Updated: 2021-04-11  Resolved: 2021-04-11

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Locking
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: None


 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.

 Comments   
Comment by Elena Stepanova [ 2021-04-10 ]

Assigned to danblack to confirm that SKIP LOCKED is limited by design, and maybe to check SELECT ... WAIT n / SELECT ... NOWAIT – I'd expect that either both should work, or both shouldn't.

Comment by Daniel Black [ 2021-04-11 ]

These look strange, however wait/nowait are table aliases rather than waiting directives.

MariaDB [test]> select wait.i from t1 wait;
 
Empty set (0.000 sec)
 
 
 
MariaDB [test]> select nowait.i from t1 nowait;
 
Empty set (0.001 sec)

So LOCK IN SHARE MODE / FOR UPDATE is part of the required syntax for WAIT / NOWAIT and I've extended those to include SKIP LOCKED.

It seems attempting to remove that constraint is going to get into parser conflicts with wait/nowait being table aliases.

As such I'll correct the KB.

Comment by Daniel Black [ 2021-04-11 ]

Fixed KB for skip locked along with DUMPFILE representation.

Generated at Thu Feb 08 09:37:16 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.