Nachtrag

Mein lieber Kollege Matthias hat mich noch auf eine Folgeidee gebracht: Wie sieht das Ganze aus mit MariaDB Stored Procedures und Stored Functions?

Die beiden Tests hier:

DELIMITER //

CREATE OR REPLACE PROCEDURE locktestsp (INOUT id INT)
BEGIN
  SELECT id INTO id FROM test WHERE id = id LIMIT 1;
END;
//

DELIMITER ;

SET @id = 3;
START TRANSACTION;
CALL locktestsp(@id);
SELECT @id;

SELECT trx_tables_locked, trx_lock_structs, trx_rows_locked FROM information_schema.INNODB_TRX;
+-------------------+------------------+-----------------+
| trx_tables_locked | trx_lock_structs | trx_rows_locked |
+-------------------+------------------+-----------------+
|                 0 |                0 |               0 |
+-------------------+------------------+-----------------+

und hier:

DELIMITER //

CREATE OR REPLACE FUNCTION locktestsf (IN id INT)
RETURNS CHAR(50) DETERMINISTIC
BEGIN
  SELECT id INTO id FROM test WHERE id = id LIMIT 1;
  RETURN id;
END;
//

DELIMITER ;

START TRANSACTION;
SELECT locktestsf(3);

 SELECT trx_tables_locked, trx_lock_structs, trx_rows_locked FROM information_schema.INNODB_TRX;
+-------------------+------------------+-----------------+
| trx_tables_locked | trx_lock_structs | trx_rows_locked |
+-------------------+------------------+-----------------+
|                 0 |                0 |               0 |
+-------------------+------------------+-----------------+