MySQL Bugs: #102531: SET TRANSACTION ISOLATION LEVEL throws error 1568 outside a transaction in func

Bug #102531	SET TRANSACTION ISOLATION LEVEL throws error 1568 outside a transaction in func
Submitted:	8 Feb 2021 15:12	Modified:	8 Feb 2021 15:55
Reporter:	Jon Scott	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Setting SET TRANSACTION ISOLATION LEVEL REPEATABLE READ within a function outside a transaction throws error 1568:

ERROR 1568 (25001): Transaction isolation level can't be changed while a transaction is in progress

How to repeat:
CREATE DEFINER=`sp-user`@`%` FUNCTION `is_in_transaction`()
RETURNS int(11)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE oldIsolation TEXT DEFAULT @@transaction_isolation;
    DECLARE EXIT HANDLER FOR 1568 BEGIN
        -- error 1568 will only be thrown within a transaction
        RETURN 1;
    END;
    
    -- If autocommit is disbaled, then we are in a transaction always until we commit or enable it again
    IF @@AUTOCOMMIT = 0 THEN
	    RETURN 1;
    END IF;
    
    -- will throw an error if we are within a transaction
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    -- no error was thrown - we are not within a transaction
    SET transaction_isolation = oldIsolation;
    RETURN 0;
END

# Test
SET @within_transaction := null;
SET @out_of_transaction := null;

BEGIN;
    set @within_transaction := is_in_transaction();
COMMIT;

SET @out_of_transaction := is_in_transaction();

SELECT @within_transaction, @out_of_transaction;

Hi Mr. Scott,

Thank you for your bug report.

This is not a bug. You can not change isolation level while the transaction is active.

This is all explained in our Reference Manual, the following subchapter:

13.3.7 SET TRANSACTION Statement

Not a bug .....

This is occurring *outside* of a transaction though. I understand that *in* a transaction it should throw the error.

Hi,

Each autocommit SQL statement is a transaction within itself.

Hence, although BEGIN or END are not explicitly written , they are still there.

It is all explained in our Reference Manual.