NO_LOCK_NO_ADMIN admits prepared shared SKIP LOCKED reads and retains InnoDB row locks

With read_only=NO_LOCK_NO_ADMIN, a prepared SELECT ... LOCK IN SHARE MODE SKIP LOCKED succeeds and retains InnoDB row locks. In the same transaction, ordinary shared locking and FOR UPDATE SKIP LOCKED controls are rejected with error 1290.

This occurs when a shared SKIP LOCKED read is issued while read_only is set to NO_LOCK_NO_ADMIN.

Reproducer

Run the following script in the mariadb client using an account permitted to change the global read_only mode and create schemas and routines.

SET @saved_ro=@@global.read_only;

CREATE DATABASE ro_test;

CREATE TABLE ro_test.t1(id INT PRIMARY KEY) ENGINE=InnoDB;

INSERT INTO ro_test.t1 VALUES(1);

DELIMITER //

CREATE PROCEDURE ro_test.check_locks()

BEGIN

 DECLARE rejected INT DEFAULT 0;

 DECLARE seen INT DEFAULT -1;

 DECLARE CONTINUE HANDLER FOR 1290 SET rejected=1;

 SELECT id INTO seen FROM ro_test.t1 LOCK IN SHARE MODE;

 SELECT 'plain',rejected,seen;

 SET rejected=0;

 SELECT id INTO seen FROM ro_test.t1 FOR UPDATE SKIP LOCKED;

 SELECT 'update_skip',rejected,seen;

 SET rejected=0;

 SET @seen=-1;

 EXECUTE IMMEDIATE

   'SELECT id INTO @seen FROM ro_test.t1 LOCK IN SHARE MODE SKIP LOCKED';

 SELECT 'prepared_share_skip',rejected,@seen;

END//

DELIMITER ;

SET GLOBAL read_only=NO_LOCK_NO_ADMIN;

START TRANSACTION;

CALL ro_test.check_locks();

SELECT 'held_row_locks',COALESCE(MAX(trx_rows_locked),0)

  FROM information_schema.innodb_trx

 WHERE trx_mysql_thread_id=CONNECTION_ID();

ROLLBACK;

SET GLOBAL read_only=@saved_ro;

DROP DATABASE ro_test;

For example, after saving it as skip_locked.sql:

mariadb < skip_locked.sql

Decisive observation

The relevant output was:

plain                 1  -1

update_skip           1  -1

prepared_share_skip   0   1

held_row_locks            2

The first two rows show that error 1290 was raised for the ordinary shared-locking read and for FOR UPDATE SKIP LOCKED. The prepared shared SKIP LOCKED statement was instead accepted, read row 1, and left two rows reported as locked for the current InnoDB transaction.

Semantic mismatch

The NO_LOCK modes are intended to prevent shared locks that can block replication. SKIP LOCKED changes waiting behavior when a requested row is already locked; it does not prevent a successfully read row from receiving a shared lock. The prepared shared read should therefore be rejected like the plain shared read, and it should retain no row locks.